What are the rules governing usage of parenthesis in VBA function calls?
There is perfect logic to the Parentheses Rule in VB(A), and it goes like this.
If a procedure (function or sub) is called with arguments, and the call is on a line with other statements or keywords, the arguments must be enclosed in parentheses. This to distinguish the arguments belonging to the procedure call from the rest of the line. So:
1: If CheckConditions(A, B, C) = DONT_PROCEED Then Exit Sub
is a valid line; the call to CheckConditions needs the parentheses to indicate what other bits of the line are its arguments. Conversely, this would produce a syntax error:
2: If CheckConditions A, B, C = DONT_PROCEED Then Exit Sub
Because it is impossible to parse.
With a procedure call as the only statement on the line, parentheses aren't needed because it is clear that the arguments belong to the procedure call:
3: SaveNewValues Value1, Value2, Value3
While this results in a syntax error (for sound reasons discussed below):
4: SaveNewValues(Value1, Value2, Value3)
To avoid confusion about parentheses or no parentheses (in fact, to avoid the Parentheses Rule entirely), it is always a good idea to use the Call keyword for calls like these; that ensures that the procedure call is not the only statement on the line, thus requiring parentheses:
5: Call SaveNewValues(Value1, Value2, Value3)
So if you get in the habit of preceding self-contained procedure calls with the Call keyword, you can forget the Parentheses Rule, because you can then always enclose your arguments in parentheses.
The matter is confused by the additional role parentheses play in VB(A) (and many other languages): they also indicate evaluation precedence for expressions. If you use parentheses in any other context but to enclose procedure call arguments, VB(A) will attempt to evaluate the expression in the parentheses to a resulting simple value.
Thus, in example 4, where parentheses are illegal for enclosing the arguments, VB(A) will instead attempt to evaluate the expression in the parentheses. Since (Value1, Value 2, Value3) is not an expression that can be evaluated, a syntax error ensues.
This also explains why calls with a variable passed ByRef act as if called ByVal if the argument is enclosed in parentheses. In the example above, where function p is called with ByRef parameter a, there is a big difference between these two calls to p:
6: p a
And
7: p(a)
As discussed above, 6 is the correct syntax: the call is alone on its line, so parentheses should not be used to enclose the arguments.
In 7, the argument is enclosed in parentheses anyway, prompting VB(A) to evaluate the enclosed expression to a simple value. Which of course is the very definition of passing ByVal. The parentheses ensure that instead of a pointer to a, the value of a is passed, and a is left unmodified.
This also explains why the parentheses rule doesn't always seem to hold sway. Clearest example is a MsgBox call:
8: MsgBox "Hello World!"
And
9: MsgBox ("Hello World!")
Are both correct, even though the parentheses rule dictates that 9 should be wrong. It is, of course, but all that happens is that VB(A) evaluates the expression in the parentheses. And the string literal evaluates to the exact same string literal, so that the actual call made is 8. In other words: calls to single-argument procedures with constant or string literal arguments have the identical result with or without parentheses. (This is why even my MsgBox calls are preceded by the Call keyword.)
Finally, this explains odd Type Mismatch errors and weird behavior when passing Object arguments. Let's say your application has a HighlightContent procedure that takes a TextBox as argument (and, you'll never guess, highlights it contents). You call this to select all text in the textbox. You can call this procedure in three syntactically correct ways:
10: HighlightContent txtName
11: HighlightContent (txtName)
12: Call HighlightContent(txtName)
Let's say your user has entered "John" in the textbox and your application calls HighlightContent. What will happen, which call will work?
10 and 12 are correct; the name John will be highlighted in the textbox. But 11 is syntactically correct, but will result in a compile or runtime error. Why? Because the parentheses are out of place. That will prompt VB(A) to attempt an evaluation of the expression in the parentheses. And the result of the evaluation of an object will most often be the value of its default property; .Text, in this case. So calling the procedure like 11 will not pass the TextBox object to the procedure, but a string value "John". Resulting in a Type Mismatch.
From Here:
Using the VBScript Call Statement to Call a Subroutine The use of Call statement is optional when you wish to call a subroutine. The purpose of the Call statement when used with a Sub is to allow you to enclose the argument list in parentheses. However, if a subroutine does not pass any arguments, then you still should not use parentheses when calling a Sub using the Call statement.
Call MySubroutine
If a subroutine has arguments, you must use parentheses when using the Call statement. If there is more than one argument, you must separate the arguments with commas.
Call MySubroutine(intUsageFee, intTimeInHours, "DevGuru")
Calling the Function There are two possible ways to call a function. You may either call the function directly, by name only, or you may call it by using the VBScript Call statement.
Calling a Function by Name When calling a function directly by name and when there is no assignment to a returned value, all of the following are legal syntax:
MyFunction
MyFunction()
MyFunction intUsageFee, intTimeInHours, "DevGuru"
If you want a returned value, you can assign the function to a variable. Note that if there is one or more arguments, you must use the parentheses.
returnval = MyFunction
returnval = MyFunction()
returnval = MyFunction(intUsageFee, intTimeInHours, "DevGuru")