I’d like to make an assertion. If you have an open parenthesis preceded by a space, you should remove the parentheses. Here’s an example:
Sub test()
Dim myCollection As Collection
Dim myObject As Object
myCollection.Add (myObject)
myCollection.Add myObject
End Sub
When you put parentheses around an object, VBA evaluates that object and, absent a property, returns the default property. The first Add might error or might not work as expected. Here’s another example:
Sub test2()
Dim dValue As Double
dValue = 1.1
Debug.Print (dValue)
Debug.Print dValue
End Sub
In this case the parentheses don’t do any harm, but they’re still unnecessary. And finally:
Sub test3()
Dim lValue As Long
lValue = CLng(1.1)
End Sub
In this case there is no space before the open parenthesis, so it’s not just preferred, but necessary.
Can you think of a case where an open parenthesis preceded by a space is required or preferred?
An exception is when you use parentheses to control the order of operations:
myvar = x / (y + Z)
I run into this issue when I use MsgBox.
You can do this:
Result = MsgBox(“Prompt”,…)
and it will work
or you can do this:
MxgBox “Prompt”,…
and it will work
but if you do this:
MsgBox(“Prompt”,…)
you will get a syntax error
and I am fairly certain (though not sure) that this is syntactically wrong also:
Result = MsgBox (“Prompt”,…).
(By the way, if not obvious, the “…” is just to shorten the examples. I know it is not good syntax. The emphasis is on the opening parenthesis after the MsgBox. :))
I am sure there is some syntax definition that explains this but I keep running into it.
Personally, I prefer to use the Call syntax instead because it resembles other languages’ function call syntax more closely.
Because I work with different languages in different projects, I try to avoid those small pesky differences :-)
so, I prefer
Call MsgBox(“Prompt”,…)
instead of the two other alternatives. It is a small word extra typing, but a lot of extra consistency with other languages.
I too used to use the Call statement as I thought it was easier to read, and feels more like other languages, but I gave that up for exactly this reason, when reviewing some of my own code as I found it clogged the page with unnecessary words and forced me to use unnecessary parentheses. The only other language that my VBA could possibly be ported to would be VB.Net and, if I am honest, that has only ever realistically/usefully happened once in my life time.
The “Space” before the parentheses just indicates that its a Sub which is “Relaxed” but a Function like Clng will always need the parentheses to tell when it ends.
Try it out using your own Functions and Subs etc.
@Vernon Wankerl… your question is addressed in what follows.
Awhile ago, someone once posted this question to an old newsgroup…
Hi all is there any difference between this..
Shell (“R:\provaclick.exe”)
and this..
X = Shell (“R:\provaclick.exe”)
This is the reply I gave back then…
Shell is a function, which means it returns a value. In VB, however, you have the ability to call any function as if it were a subroutine… the function’s value will still be calculated (it gets assigned to a memory location consistent in size with the function’s declared return value data type), it just that the value won’t be returned anywhere (the memory location eventually gets cleared by VB’s “garbage collection” routines whether it was assigned anywhere or not). So the quick answer is the two statements are the same. HOWEVER, that is only because you used the required argument to the Shell function and omitted its optional second argument. IF you had specified that optional second argument, your first statement would have failed with an error whereas your second statement would still have worked correctly. “Why is that?”, I am sure you are asking. It has to do with how subroutines are called in VB. The proper syntax for calling a subroutine in VB are these…
YourSubroutine Argument1, Argument2, etc.
Call YourSubroutine(Argument1, Argument2, etc.)
Notice that the first calling method’s syntax does not use parentheses whereas the second one’s syntax does. Whenever you use parentheses around something in VB that is NOT required by syntax, VB treats it as an expression to be evaluated. If the expression inside the parentheses is a constant or variable reference, then it evaluates to itself. Now, let’s examine the two possible ways your originally posted first statement could have been written…
Without 2nd argument: Shell (“R:\provaclick.exe”)
or
With 2nd argument: Shell (“R:\provaclick.exe”, 2)
where the 2 in the “with 2nd argument” example is the default value for that argument when omitted (the 2 is assumed in the first example). Okay, as I said, a SINGLE constant (or variable) value inside parentheses evaluates to itself, so (“R:\provaclick.exe”) gets evaluated to “R:\provaclick.exe” and that ends up getting passed to the Shell function. However, in the second example, (“R:\provaclick.exe”, 2) is not an expression that VB can evaluate… there are two terms with a comma instead of an operator between them… that is meaningless to VB, so it doesn’t know what to pass to the Shell function and errors out instead. So, based on this, my advice when would be to only use parentheses where they are required by syntax or for grouping terms in an expression in order to force the order those terms are evaluated in. Your first posted statement does not meet that criteria and, so you don’t end up scratching your head in the future when you end up grouping multiple arguments in a subroutine call, I would recommend against using it.
Rick, thanks for the thorough explanation! I’ve been wondering about this lately.
Wait, in VBA, the following expression:
(x)
can evaluate to something different from this expression:
x
?
That’s terrifying.
@Alex Godofsky,
I am not sure if “evaluate to something different” is the right way to say it. Here is an (off the top of my head) example to show what I mean. Put the letters ABC in cell A1. Then execute this line of code in the VB editor’s Immediate Window…
? WorksheetFunction.CountIf(Range(“A1″),”*B*”)
That line of code will print out 1 because the cell contains the letter B somewhere within it. Now surround the Range call with parentheses…
? WorksheetFunction.CountIf((Range(“A1″)),”*B*”)
Now when you execute this line of code, you get an “Object required” error. The reason? Putting the parentheses around the Range call force VB to evaluate what was inside them… because the default property for Range is Value, that is what was passed in as the first argument and the Value of Range(“A1”) is its contents, ABC, which is a text string, not a Range object.
Yeah, it’s more of a timing thing. COUNTIF wants to evaluate the Range object and it gets mad when you evaluate it first. Job security, I guess.
COUNTIF is highly unionized.