Return a select statement result

Question

I have a function that has a select statement. I want this function to return a value which will be assigned to a cell, but I keep getting errors. Here is my code that calls the select statement function:

Sub ChangeSizes()

    For i = 2 To 50
        Range("G" & i) = LookupSize(Range("G" & i).value)
    Next i

End Sub

And here is my function that I want to return a value that will be assigned to the range:

Public Function LookupSize(Size) As String

    Select Case Size
        Case Is = 1
            Return '5/8" or 1/4"'
        Case Is = 43
            Return '3/8"'
    End Select

End Function

However, as it stands, I get an error: Return without Gosub

How do a return the result of the case statement and assign it to a range?

Answer 1

You assign the return value to the function itself. Single quotes create comments, double quotes are used for quoted strings.

Public Function LookupSize(Size) As String

    Select Case Size
        Case 1
            LookupSize = "5/8" or "1/4"
        Case 43
            LookupSize = "3/8"
    End Select

End Function

Answer 2

Functions in VBA do not work using Return, but using the Function name as a variable. If you want to end the function after assigning it a value, you must also use Exit Function. Your code would look like this:

Public Function LookupSize(Size) As String

Select Case Size
    Case 1
        LookupSize = '5/8" or 1/4"' or whatever you want
    Case 43
        LookupSize = '3/8"' or whatever you want
End Select

End Function