VBA Compile Error: Syntax error

Question

I am trying to write a simple function where, based on the parameter I am passing, I need to return a string value. I am getting error Compile error: Syntax error.

Public Function getServer(env As String) As String
    Dim serverName As String
    Select Case env
        Case "DEV"
            serverName = "abc"
            Return serverName;
         Case "TEST"
            serverName = "def"
            Return serverName;
         Case "Prod"
            serverName = "xyz"
            Return serverName;
    End Select
End Function

What is the syntax error you're receiving? And on what line? — STLDev, May 15 '18 at 16:27
Possible duplicate of [Return in VBA, does not do what I expect](https://stackoverflow.com/questions/11240563/return-in-vba-does-not-do-what-i-expect) — Zev Spitz, May 15 '18 at 16:36
Possible duplicate of [How to Return a result from a VBA Function](https://stackoverflow.com/questions/2781689/how-to-return-a-result-from-a-vba-function) — Victor K, May 15 '18 at 16:40
@STLDeveloper the error would be "Return without GoSub" IIRC — Mathieu Guindon, May 15 '18 at 18:58

Zev Spitz · Accepted Answer · 2018-06-25T04:51:24.960

5

VBA doesn't use Return to exit a function early, or to specify the returned value of the function. In VBA, you specify early exit using Exit Function; and in order to return a value or object from a function, you have to assign / set the name of the function to the value / object you want to return:

Public Function getServer(env As String) As String
    Select Case env
        Case "DEV"
            getServer = "abc"
        Case "TEST"
            getServer = "def"
        Case "Prod"
            getServer = "xyz"
    End Select
End Function

edited Jun 25 '18 at 04:51

answered May 15 '18 at 16:31

Zev Spitz

13,950
6
64
136

1

No need for an `Exit Function` in each case. Simply`Case Else: Exit Function` and you're covered. – Kostas K. May 15 '18 at 16:36
FYI VBA *does* have a `Return` statement. It just isn't used for that purpose (and shouldn't be used at all IMO). – Mathieu Guindon May 15 '18 at 17:08

Mathieu Guindon · Answer 2 · 2018-05-15T17:14:31.037

In VBA the Return statement (which does exist) serves an entirely different purpose; it's used in conjunction with the legacy GoSub statement, to return from a subprocedure jump:

    bar = 42
    GoSub Foo
    Exit Sub
Foo:
    Debug.Print bar
    Return

This type of construct is present in the language to support earlier versions/dialects of BASIC, and shouldn't be seen in modern VBA code.

Functions and Property Get procedures return their return value by assigning to the procedure's identifier:

getServer = "abc"

Note that the procedure's identifier is essentially a local variable, and thus the assignment doesn't return. Use Exit Function statements to bail out.

Also, {NEWLINE} is the end-of-instruction marker in VBA, not ; semicolon ;-)

The semicolon is used in VBA to control the behavior of string-printing, e.g. Debug.Print and Write# statements.

Sub test()
    Debug.Print 1; 2; 3;
    Debug.Print 4; 5; 6; ' prints on the same line as the previous statement
End Sub

Whereas this would output on 2 separate lines:

Sub test()
    Debug.Print 1; 2; 3
    Debug.Print 4; 5; 6 ' prints on the next line
End Sub

VBA Compile Error: Syntax error

2 Answers2