Subroutine calls with parentheses

Excelbert

New Member
Joined
Mar 4, 2008
Messages
12
I have a subroutine that takes a string and an integer. I was getting an incredibly vague error message (given the number of years and billionaires involved with Microsoft) when I tried to call it. It said: "Syntax error" and highlighted the line in red. Occasionally, if it was in the mood, it would say that it was looking for an "=" sign. A big thanks to the "highlight but don't clarify" coding team. Anyway, it turns out that VBA did not like parentheses around the arguments as in

MySub("hello", 1)

instead I had to do

MySub "hello", 1

I was able to use the parentheses if I defined the subroutine with only a single string argument.

What are the rules for using parentheses with subroutine calls?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi Excelbert

You have 2 ways to call SubRoutines

1 - You use its name

In this case you should not use parentheses with the parameters unless you have only 1 parameter, in which case you can also use parentheses if you want

MySub "hello", 1
MySub "hello"
MySub ("hello")

2 - You use Call

in this case always use parentheses

Call MySub ("hello", 1)
Call MySub ("hello")

HTH
 
Upvote 0
Thanks pgc01!

It appears that the limitation on parentheses does not apply to functions.
 
Last edited:
Upvote 0
It appears that the limitation on parentheses does not apply to functions.

I'm not sure I understand.

You use parentheses with a function when you use the function as a value. If you just want to execute the code of a function and are not interested in its return value you can call it without parentheses (the rule of the 1 parameter also applies).
 
Upvote 0
I'm not sure I understand.

You use parentheses with a function when you use the function as a value. If you just want to execute the code of a function and are not interested in its return value you can call it without parentheses (the rule of the 1 parameter also applies).

I could not do:

MySub(param1, param2, param3)

but I could do:

SomeVar = MyFunction(param1, param2, param3)

They look very similar. It is not clear vba peoplw would say MySub(param1) is valid but not a multiple parameter form. Why didn't they just require Call on any sub call with parentheses?


Are parentheses required with functions? It looks like you can't have a subroutine and function with the same name, as I am getting an error that it is ambiguous, even with an = sign before the call.
 
Upvote 0
Are parentheses required with functions?
Already answered in my last post.

... They look very similar.

Well, with functions you must use the parentheses if you are using it as a value. Notice that if it were not so you'd get very odd expressions, like

SomeVar = MyFunction param1, param2, param3 *4

Instead, the parentheses make the expression understandable

SomeVar = MyFunction(param1, param2, param3) *4

A SubRoutine, on the other hand, can never be used as a value and so you don't have that problem.

It looks like you can't have a subroutine and function with the same name, as I am getting an error that it is ambiguous, even with an = sign before the call.

No, vba is not a OOP, you don't get polymorphism.

Although you can't use polymorphism in your code, vba has native functions/statements that use it like, for instance Mid()

As a function

Dim s as String

s=Mid("Horse",2,3)

As a statement

Dim s as String

s="Horse"

Mid(s,3,1)="u"
 
Upvote 0
For subroutines you should not use parentheses unless you use the Call keyword. For functions, you should use parentheses if you assign the return value, and not otherwise.
In your original example, this:
Code:
MySub ("hello")
is the equivalent of this if you had 2 arguments:
Code:
MySub ("hello"), (1)

it is not the equivalent of this:
Code:
MySub("hello", 1)
which is syntactically wrong.

(You will note that when you use parentheses with the first of those two, a space gets added between the sub name and the argument, which does not happen when you use a function with arguments and assign the return value.)

Although you can often get away with enclosing the single argument to a subroutine within parentheses, you should get out of the habit of it or you will get very confused when you start trying to pass objects as arguments. For example, try this:
Code:
Sub test(rng As Range)
   MsgBox rng.Address
End Sub
Sub calltest()
   ' this works because of the Call keyword
   Call test(Range("A1"))
   ' this works - no parentheses
   test Range("A1")
   ' this fails - the parentheses dereference the object and
   ' you actually pass the value, not the Range object
   test (Range("A1"))
End Sub

HTH
 
Upvote 0
My general rule of thumb is:
For subs, never use Call and never use parentheses.
For functions, always assign the return value (even if you don't use it) and always use parentheses.
Keeps things nice and simple (even if I don't always follow my own advice)! :)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top