Assigning a value from a sub

Question

I seem unable to write a basic sub function to return a value. I get a "compile error: Expected Function or Variable name", but the VBA seems to recognize the name. It even changes the capitalization when I update the function name.

I've looked at the help and even similiar posts on here. I assume it's a silly error, but can someone help me understand what I'm doing wrong. I've even made a simple function that isn't compiling:

Sub fIVe()
    Dim i As Integer
    i = 5
    fIVe = i
End Sub

sClassification = getClassification("Activities")
ExportSheet "Activities", sFolderName, xWb, True, sClassification

Public Sub getClassification(sTabName As String)
    Dim sClassificationCode As String, sClassification As String
    'Step 1: Find Code for tab name
    sClassificationCode = Application.WorksheetFunction.Index(Sheets("Cover Master").Range("B7:B12"), _
        Application.WorksheetFunction.Match(sTabName, Sheets("Cover Master").Range("A7:A12"), 0), 1)
    'Step 2: Find definition in Type Definitions tab using code from above
    sClassification = Application.WorksheetFunction.Index(Sheets("Type Definitions").Range("F6:F21"), _
        Application.WorksheetFunction.Match(sClassificationCode, Sheets("Type Definitions").Range("E6:E21"), 0), 1)
     getClassification = sClassification
End Sub

`Function`s return a value. There's lots of documentation on this, but here's a [link](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/function-statement) to get you started. — BigBen, Jul 09 '19 at 14:58
Make `fIVe` a `Function`? Otherwise, there are several other ways to make variables "available" (I use that term loosely, because passing arguments `ByRef` is not the same as scoping variables, etc.) across procedures: https://stackoverflow.com/questions/16374253/how-to-make-excel-vba-variables-available-to-multiple-macros/16374516#16374516 — David Zemens, Jul 09 '19 at 14:59
If you're literally hardcoding this function to return 5, then it would be better to use a module-level `Public Const fIVe as Integer = 5`. — David Zemens, Jul 09 '19 at 15:04

score 1 · Accepted Answer · answered Jul 09 '19 at 15:02

Several changes to get it to compile. To return values, instead of

Public Sub foo
End Sub

you use

Public Function foo As SomeReturnTypeOrOther
    foo = SomeValueOrOther
End Function

Changes marked with '<==' in the following.

Function fIVe() As Long    ' <== 'Function' instead of 'Sub'; add 'As <type>'
    Dim i As Long          ' <== Use 'Long' instead of 'Integer'
    i = 5
    fIVe = i
End Function                ' <== End Function

Public Sub DoSomething()    ' <== Wrap the 'sClassification = ...' 
                            ' and 'ExportSheet' lines in a Sub
    Dim sClassification As String   ' <== Since it's not in `getClassification`, 
                                    ' it doesn't share the variables
    sClassification = getClassification("Activities")
    ExportSheet "Activities", sFolderName, xWb, True, sClassification
End Sub

Public Function getClassification(sTabName As String) As String  ' <== Function; As String
    Dim sClassificationCode As String, sClassification As String
    'Step 1: Find Code for tab name
    sClassificationCode = Application.WorksheetFunction.Index(Sheets("Cover Master").Range("B7:B12"), _
        Application.WorksheetFunction.Match(sTabName, Sheets("Cover Master").Range("A7:A12"), 0), 1)
    'Step 2: Find definition in Type Definitions tab using code from above
    sClassification = Application.WorksheetFunction.Index(Sheets("Type Definitions").Range("F6:F21"), _
        Application.WorksheetFunction.Match(sClassificationCode, Sheets("Type Definitions").Range("E6:E21"), 0), 1)
     getClassification = sClassification
End Function

' I also added a stub ExportSheet since it wasn't included in your question.
Public Sub ExportSheet(ParamArray args() As Variant)
    ' Not sure what you want to do here
End Sub

Assigning a value from a sub

1 Answers1