CODEWITHSUNDEEP
ms-accessvba
user1670773
user1670773

Reputation:

ms access - vba: Compile Error: expected: =

I have a subroutine as below

Public Sub updateStagesTable(sName As String, percentageValue As Double)
    stageName = "'" & sName & "'"
    sSQL = "INSERT INTO StagesT ([Stage Name], [Stage Value In Percentage]) VALUES (" & stageName & "," & percentageValue & ");"
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
End Sub

and I call it from another subroutine as below

economy = 3.53
updateStagesTable ("Economy", economy)

But I get this compile error

Compile Error: expected: =

I don't understand what I am doing wrong here. Please help.

Upvotes: 19

Views: 59703

Answers (3)

DIV
DIV

Reputation: 85

Apparently this is an area of confusion!

For one thing, wrapping the input argument in parentheses seems to work for some subroutine calls. And, furthermore, the tooltips in the VBA Editor mirror the statement defining the procedure, which includes parentheses!

So what is behind the confusion? Below is some basic code to explore this.

' Simple subroutine with two input arguments.
Sub twoInputs(in1, in2)
    Debug.Print in1 & " eats "; in2 & "!"
End Sub

' Simple subroutine with one input argument.
Sub oneInput(in1)
    Debug.Print in1 & " eats pizza!"
End Sub

' Routine to test various syntaxes for calling subroutines.
Sub subCallingTest()
    'twoInputs("Cat", "fish")       'FAILS.  Parentheses cannot be ignored here.  
    twoInputs "Cat", "fish"         'Works.
    twoInputs ("Cat"), ("fish")     'Works, but only because parentheses can be ignored here!
    Call twoInputs("Cat", "fish")   'Works.
    'Call twoInputs "Cat", "fish"   'FAILS.  Parentheses are required here.  
    
    oneInput "Daughter"         'Works.
    oneInput ("Daughter")       'Works, but only because parentheses can be ignored here!
    Call oneInput("Daughter")   'Works.
    'Call oneInput "Daughter"   'FAILS.  Parentheses are required here.  
End Sub

It turns out that the parentheses are tolerated for a single input argument if the procedure/code only cares about the value of the inputs (see counter-example at the end), because they can be ignored in the same way as they can be when they are wrapped around individual numbers — but not when they are wrapped around several numbers.

debug.print 1 + 2 * 10      ' Answer is 21.
debug.print (1) + (2) * 10  ' Answer is 21.
debug.print (1 + 2) * 10    ' Answer is 30.

There is one very subtle clue as to when the parentheses are expected, which is in the spacing. Notice that the Editor inserts a space after the procedure's name in oneInput ("Daughter"), but not in Call oneInput("Daughter").

—DIV

P.S. If a procedure has no arguments at all, then parentheses are not used to invoke it (neither with nor without the call statement).

P.P.S. A subroutine can be guaranteed to only care about the values of the input arguments if the inputs are explicitly marked as ByVal in the defining Sub statement. In my examples above there is no explicit designation of the input arguments, so the arguments have defaulted to ByRef; however, it is apparent from the above examples that the performance will nevertheless be unaffected. A simple counter-example would be as follows.

Sub oneInputMod(in1)
    in1 = UCase(in1)
    Debug.Print in1 & " eats pizza!"
End Sub

Sub subCallingTestMod()
    person = "everyone"         ' Set a local variable's value.
    oneInputMod person          ' Works.
    Debug.Print person          ' Output is "EVERYONE", as nominally intended.
    
    person = "everyone"         ' Reset a local variable's value.
    oneInputMod (person)        ' Seems to work, judging by immediately visible output (which was not affected by the parentheses).
    Debug.Print person          ' Output is "everyone", which is nominally unintended.
    
    person = "everyone"         ' Reset a local variable's value.
    Call oneInputMod(person)    ' Works.
    Debug.Print person          ' Output is "EVERYONE", as nominally intended.
End Sub

Upvotes: 2

PeterTaylorXC
PeterTaylorXC

Reputation: 271

I have never liked this peculiarity of VB so I always use the alternate CALL syntax, in your case this would be:

Call updateStagesTable("Economy", economy)

which does allow the parentheses that all other languages expect

Upvotes: 27

Tim Williams
Tim Williams

Reputation: 166885

updateStagesTable ("Economy", economy)

should be

updateStagesTable "Economy", economy

with no parentheses

See related: Unexpected results from typename

Upvotes: 44

Related Questions