ms access - vba: Compile Error: expected: =

Question

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.

Dumbarton

Answer 1

updateStagesTable ("Economy", economy)

should be

updateStagesTable "Economy", economy

with no parentheses

See related: Unexpected results from typename

Amerigo

Wow, this is so destabilizing for any developer coming from sane languages! – Slither

It is what it is - it's maybe just different from what you're used to. It's not an issue of sane or not sane. – Amerigo

Answer 2

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

Miele

Answer 3

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

Ko

Careful! It is wrong to say that in the case of calling a sub like this: ExampleSub (Argument) parenthesis can be ignored! This is not at all the case, the parenthesis in this case force passing the argument ByVal. Even if the sub ExampleSub explicitly takes its arguments ByRef, calling it like this ExampleSub (Argument) will force ByVal! See here for a more detailed explanation. – Tat

Thanks, My initial focus was on elucidating why parentheses sometimes 'seem' to work. However, it's a fair point that even parentheses that wrap individual inputs cannot be ignored generally (although it was safe in my simple original examples). I have added a counter-example to illustrate that concretely. – Ko

"Ignore" or "tolerate" parentheses is a wrong approach in my opinion. One should always use parentheses deliberately - use Call if you want to put them around single arguments. Also Global is kinda deprecated and should be Public. – Spano

Andre, please understand that I am not recommending to use redundant parentheses. I am endeavouring to explain why sometimes they seem to work, and why those specific occasions cannot be generalised to all situations. – Ko