VBA. Nesting if elseif within Select Case procedure

Question

I have the following function written within VBA module as a test case. At this moment the function includes only one "Case" because I want to test it and if successful to fill with more "Case"s:

Function Insurn(indicator as String, value as Double)
    Select Case indicator
        Case string_indicator1
            If value >= 11 And value <= 99 Then
                asset_val = 5
            ElseIf value >= 6 And value <= 10 Or value >= 100 Then
                asset_val = 4
            ElseIf value >= 0 And value <= 5 Then
                asset_val = 3
            ElseIf value >= -5 And value < 0 Then
                asset_val = 2
            ElseIf value >= -14 And value < -5 Then
                asset_val = 1
            ElseIf value >= -24 And value < -14 Then
                asset_val = 0
            Else val < -24 Then
            End If
    End Select
End Function

I apply this function inputting the following data Insurn("string_indicator1", 12) and it returns 0 value. However, I expect the function to return 5.

And much more, no matter what number is assigned to the value parameter the function invariably returns 0 value.

Please help clear out how to make this function work properly.

Answer 1

I think first you need to understand how UDF (User Defined Functions) work. When you create an UDF, you must assign a value to be returned by the UDF.

Something like this:

Function MYSUM(a As Integer, b As Integer) As Integer
result = a + b
End Function

This will return zero always because I've not assigned any value to be returned. Now check this:

Function MYSUM(a As Integer, b As Integer) As Integer
result = a + b

MYSUM = result
End Function

This will work as expected. second thing is comparing direct strings/numbers or variables. You got this line:

Case string_indicator1

Here string_indicator1 is being called as a variable, not as a direct string, and the variable is empty so actually your code is checking vs an empty variable.

So if you call your function like Insurn("string_indicator1", 12) probably you want your code something like this:

Function Insurn(indicator As String, value As Double)
'always declare your variables
Dim asset_val As Integer 'or whatever tipe you need

Select Case indicator
    Case "string_indicator1"
        If value >= 11 And value <= 99 Then
            asset_val = 5
        ElseIf value >= 6 And value <= 10 Or value >= 100 Then
            asset_val = 4
        ElseIf value >= 0 And value <= 5 Then
            asset_val = 3
        ElseIf value >= -5 And value < 0 Then
            asset_val = 2
        ElseIf value >= -14 And value < -5 Then
            asset_val = 1
        ElseIf value >= -24 And value < -14 Then
            asset_val = 0
End Select

'assign result to function!

Insurn = asset_val

End Function

Notice the modification on Case and finnaly how the code assigns the output to be returned.

Hope this can help you out