CODEWITHSUNDEEP
vbaexcel
Thomas McKay-Smith
Thomas McKay-Smith

Reputation: 31

In VBA, is the syntax different when creating an object vs. not?

Apologies, this was difficult to word as a clear question.

The following line is valid:

objWord.Documents.Add Template:=templatePath

But this line is not:

Set objMergedReq = objWord.Documents.Add Template:=templatePath

I get the following compiler error:

Expected: end of statement

Why are the two interpreted differently? How do I eliminate the error? Do I need extra parentheses in case 2?

Upvotes: 1

Views: 59

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

The crux is this:

objWord.Documents.Add Template:=templatePath

This is a function call, but the returned value (an object reference) is discarded and thus, the function is really used as if it were a procedure; with VBA's implicit procedure call syntax, parentheses are not present. You can use the [deprecated] explicit call syntax to require the parentheses:

Call objWord.Documents.Add(Template:=templatePath)

As you noticed, this is illegal:

Set objMergedReq = objWord.Documents.Add Template:=templatePath

Because the syntax for a function call (you're not discarding the returned value here) requires the parentheses whenever an argument list is specified:

Set objMergedReq = objWord.Documents.Add(Template:=templatePath)

If you're tempted to "just use parentheses everywhere", know that you'll run into other syntax issues, as soon as you need to specify 2 or more arguments:

MsgBox (message, vbOkOnly + vbInformation) 'illegal

That's because when arguments are surrounded by parentheses, you're really telling VBA to evaluate the contents of the parentheses as a value, and pass the result ByVal to the function/procedure, even if that function/procedure is explicitly specifying the parameter as ByRef.

Upvotes: 3

Rich Holton
Rich Holton

Reputation: 682

When there is no return value, like when you use a Sub, or you ignore the return value, you don't put the parameters in parenthesis. Optionally, you can use "Call" and then put the parameters in parenthesis.

In your example, 

objWord.Documents.Add Template:=templatePath

the add method does create a return value, but you are not using it. Therefore, you either don't use parentheses (as you show), or you could use the "Call" statement:

Call objWord.Documents.Add(Template:=templatePath)

In your second example, you are using the return value, so parenthesis are required:

Set objMergedReq = objWord.Documents.Add(Template:=templatePath)

The "Set" is needed because you are assigning an object. The same rules apply if the return value is a non object -- you would just omit the "Set".

Upvotes: 2

Nathan_Sav
Nathan_Sav

Reputation: 8531

you need to use parenthesis when setting, so set x=f(y)

Upvotes: 2

Related Questions