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
– STLDev, Commented May 15, 2018 at 16:27
Possible duplicate of Return in VBA, does not do what I expect — user20416
– user20416, Commented May 15, 2018 at 16:36
Possible duplicate of How to Return a result from a VBA Function — Victor K
– Victor K, Commented May 15, 2018 at 16:40
@STLDeveloper the error would be "Return without GoSub" IIRC — Mathieu Guindon
– Mathieu Guindon, Commented May 15, 2018 at 18:58

user20416 · Accepted Answer · 2018-06-25 04:51:24Z

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, 2018 at 4:51

answered May 15, 2018 at 16:31

user20416

15.5k9 gold badges76 silver badges149 bronze badges

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

2 Comments

Kostas K. Over a year ago

No need for an Exit Function in each case. SimplyCase Else: Exit Function and you're covered.

Mathieu Guindon Over a year ago

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 · Accepted Answer · 2018-05-15 17:14:31Z

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

Collectives™ on Stack Overflow

VBA Compile Error: Syntax error

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related