Excel VBA how to return variable from function

Question

Example of the code:

Sub

   Dim NOSSPrefix As String
   NOSSPrefix = Cells(1, 6).Value
   NOSSSpecific(NOSSPrefixSpec)

End Sub

Function NOSSSpecific(NOSSPrefixSpec)

   If NOSSPrefixSpec = "6" Or NOSSPrefixSpec = "17" Or NOSSPrefixSpec = "19" Then
       NOSSPrefix = NOSSPrefixSpec        
   Else
       NOSSPrefix = "999"
   End If

End Function

Tried alreaddy return, set, ... Just want to return the NOSSPrefix back from the function to the routine above.

Add NOSSSpecific = NOSSPrefix as the last line in the Function — Scott Craner
– Scott Craner, Commented Jan 5, 2018 at 18:29
Replacing NOSSPrefix with the function name NOSSSpecific would make it return either of the 2 values. — Alex K.
– Alex K., Commented Jan 5, 2018 at 18:29
AS a suggestion; I would add Option Explicit at the top of your module. Not declaring your variables is a bad habit. — Sorceri
– Sorceri, Commented Jan 5, 2018 at 18:30
^^ Option Explicit would pick up the fact that your main subroutine is passing a variable (NOSSPrefixSpec) to the function even though that variable had never been assigned a value previously - the previous line is setting a variable called NOSSPrefix. — YowE3K
– YowE3K, Commented Jan 5, 2018 at 18:32

Alex P · Accepted Answer · 2018-01-05 18:33:59Z

4

Something like this:

Sub Main()
    Dim NOSSPrefixSpec As String
    NOSSPrefixSpec = "12"
    Debug.Print NOSSSpecific(NOSSPrefixSpec) //Returns "999"
End Sub

Function NOSSSpecific(NOSSPrefixSpec As String) As String
    If NOSSPrefixSpec = "6" Or NOSSPrefixSpec = "17" Or NOSSPrefixSpec = "19" Then
        NOSSSpecific = NOSSPrefixSpec
    Else
        NOSSSpecific = "999"
    End If
End Function

Note:

You have to define NOSSPrefix. I changed it to NOSSPrefixSpec
I've explicitly added String as the function argument type and return value

answered Jan 5, 2018 at 18:33

Alex P

12.5k7 gold badges55 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA how to return variable from function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related