Public Function VBA #VALUE Error

[7wgh]

Hi,

I have created a simple Public Function (to be expanded) but want to make sure I can get the call in excel working. Here is the code:

Public Function Wind (Direction as integer) as integer

If Direction > 0 & Direction <=10 Then Wind = 1
Else: Wind = 0
End If

End Public Function

Essentially Direction is a numeric value for wind direction and i am seeing if it is between 0 and 10. When i type the following in excel
=Wind (A1) it returns #VALUE
Any help would be appreciated

[si_the_geek]

Thread moved from the 'VB6' forum to the 'Office Development/VBA' forum (while VBA and VB6 have some similarities, they are not the same thing)

[kaliman79912]

You are in the right track. A couple of changes:
* & is concatenation for string, use "And" instead.
* End Function, not End Public Function
* note: This function does not include the "0", if you want it do >=0
* If you put a statement right after the Then keyword, you cant put Else on the next row.

One cosmetic change:
* Structure your code for readability, saving code rows making it compact at the expense of readability is not a good practice.

Public Function Wind(Direction As Integer) As Integer
 
    If Direction > 0 And Direction <= 10 Then
        Wind = 1
    Else
        Wind = 0
    End If
 
End Function

[7wgh]

Thanks for the reply, it worked great. As a follow-up I wanted to see if it would be possible to only use the below code for a wind speed greater than 15km/hr. I added the Speed in the function but do not know how to approach only running the code for Speed > 15

Public Function Wind(Direction As Integer, Speed as integer) As Integer

If Direction > 0 And Direction <= 10 Then
Wind = 1
Else
Wind = 0
End If

End Function

[kaliman79912]

When posting code try to format it as such, with the code tags at the top of the edit window.

Public Function Wind(Direction As Integer, Speed as Integer) As Integer
 
    If Direction > 0 And Direction <= 10 And Speed > 15 Then
        Wind = 1
    Else
        Wind = 0
    End If
 
End Function

[Siddharth Rout]

7wgh, Few suggestions

When you are creating a Public Function you need to take care of many things.

Use a variable name which is not a part of the Excel Object Model. For example "Direction". it is used by

Speech.Direction Property

So how does one know if the variable name is already a part of the EOM. It's simple
The moment you type the variable name and move to the next line, the first letter will automatically get capitalized unless you have previously defined it that way.

Also in your code, you have to take care that if the wrong values are passed in the function than it should not give you unexpected results

I would suggest this code in lieu of your original code. Let me know what do you think

Public Function WindValue(RngDir As Range, SpeedRng As Range) As Long
    On Error GoTo Whoa
    
    '~~> Check if the values passed are numeric
    If IsNumeric(RngDir.Value) And IsNumeric(SpeedRng.Value) Then
        Dim lngDirection As Long, lngSpeed As Long
        
        lngDirection = RngDir.Value: lngSpeed = SpeedRng.Value
        
        If lngDirection > 0 And lngDirection < 11 And lngSpeed > 15 Then
            WindValue = 1
        Else
            WindValue = 0
        End If
    Else
        WindValue = 0
    End If
    
    Exit Function
Whoa:
    WindValue = 0
End Function

Hope this helps.

Sid