4

What's the right way to call method when it comes to using or omitting parentheses? If I understand the results of my google search correctly: you have to use parentheses when assigning the return value of a method (or function) to a variable. Below are some examples:

  1. wbData.Sheets.Add '-> works

  2. Set wsData = wbData.Sheets.Add '-> works

  3. wbData.Sheets.Add(Before:=wbData.Sheets(wbData.Sheets.Count)) '-> syntax error

  4. Set wsData = wbData.Sheets.Add(Before:=wbData.Sheets(wbData.Sheets.Count)) '-> works

  5. wbData.Sheets.Add Before:=wbData.Worksheets(wbData.Worksheets.Count) '-> works

  6. Set wsData = wbData.Sheets.Add Before:=wbData.Worksheets(wbData.Worksheets.Count) '-> syntax error

Just to make sure I get the VBA logic: #3 gives me an error because the parentheses to VBA means the value (= the new worksheet) gets returned, but there's no variable to assign it to? And #6 is the opposite case?

Even if my attempt at an explanation were correct, could someone explain to me why the example on the official help page is not working for me: 

ActiveWorkbook.Sheets.Add(Before:=Worksheets(Worksheets.Count))

This gives me a syntax error, same as #3 in my list above. At this I'm just confused.

NoNameNo123
  • 625
  • 1
  • 8
  • 17
  • In my experience the `()` are needed when you set an object/variable with parameters – Damian Jun 20 '19 at 19:36
  • FYI, the info on the link you provide is for VB, not VBA. They are closely related in that VBA derives *from* VB, but syntactically there are some differences like this. – David Zemens Jun 20 '19 at 20:19
  • 2
    @DavidZemens - that link is to the "Office VBA reference"... – Tim Williams Jun 20 '19 at 20:22
  • @TimWilliams I know what it says in the URL -- maybe they have changed htings as I don't frequent that dox so much anymore, but used to always have C# and VB examples on the same page, they didn't (usually? ever?) have explicit "VBA" examples. My guess is that they just re-used the VB examples. I could be mistaken, but there's probably a reason the snippets are labeled as "VB" and not "VBA", despite what the page purports to present to the reader :) – David Zemens Jun 20 '19 at 20:27
  • 2
    It also wouldn't be the first time that their reference/examples were wrong or contained erroneous information :) – David Zemens Jun 20 '19 at 20:27
  • 2
    @DavidZemens - good point: I hadn't noticed the "VB" labels – Tim Williams Jun 20 '19 at 20:36
  • @DavidZemens Are there any examples on the VBA help site with "VBA"? No matter what topic I look at, the example always says "VB" at the top of the box. – NoNameNo123 Jun 21 '19 at 19:38
  • 1
    I honestly don't know. Probably not. Like I mentioned above, they all used to be either VB or C#, and the VB ones are like, 99% accurate for VBA. I'd just heed Tim's final comment: *One thing to watch out for is when the Vb editor puts a space between the method name and the opening parenthesis - when that happens it's a sign you might not need the parentheses at all*. – David Zemens Jun 21 '19 at 20:00

2 Answers2

8

Does the method return a value you need? Use parentheses (but optional if not passing any arguments to the method unless you're using the return value in the same line).

Eg - below RowRange returns a Range object, but you can't then index directly into it using (2,1), since that is interpreted as passing arguments to RowRange (which doesn't take any) 

s = myPivotTable.RowRange(2, 1).Value   'fails with "too many parameters"

Adding the parentheses clean this up:

s = myPivotTable.RowRange()(2, 1).Value 'OK

Using Call ? Use parentheses. But Call is typically considered as deprecated.

Anything else? Parentheses not required, and may produce unexpected outcomes by causing arguments to be evaluated before being passed.

One thing to watch out for is when the Vb editor puts a space between the method name and the opening parenthesis - when that happens it's a sign you might not need the parentheses at all.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 1
    Much more succinct & better explained than my rambling, upvoted =) – Mathieu Guindon Jun 20 '19 at 20:21
  • No I think it's worth spending more time than I did - particularly around what happens when you use unneeded parentheses – Tim Williams Jun 20 '19 at 20:23
  • 2
    @TimWilliams There is already https://stackoverflow.com/a/10262247/11683 for that. – GSerg Jun 20 '19 at 20:29
  • 1
    @GSerg would it be inappropriate to retag that one with [tag:vba] (and [tag:vbscript] while we're at it)? I'm bookmarking it as a useful dupe target (should *this* question be marked as a dupe?) – Mathieu Guindon Jun 20 '19 at 20:35
  • @MathieuGuindon I would say no to **re**tag. As for adding tags, it certainly benefits future readers, even though adding them might seem wrong from the OP's perspective. This question, I considered closed as a duplicate, then I did not because the byval behaviour is only a corner case that is worth mentioning in an answer, which is exactly what happened with this answer and my initial comment. – GSerg Jun 20 '19 at 20:44
  • Side note: there's an implicit default member call against the `Range` interface here: `myPivotTable.RowRange()(2, 1).Value` - the explicit equivalent would be `myPivotTable.RowRange.Cells(2, 1).Value`, which reads better IMO: `()` is always suspicious in VBA =) – Mathieu Guindon Jun 21 '19 at 11:49
  • @MathieuGuindon - yes probably worth adding. I did have it here which is where I got that example. https://stackoverflow.com/questions/56642693/excel-vba-run-time-error-450-from-referencing-a-range-directly-rather-than-throu/56661734#56661734 – Tim Williams Jun 21 '19 at 15:01
  • Thanks a lot, if I could mark two answer as correct I would (Mathieu Guindon's walked me through the concepts even more). As for not using `Call`: Is there anything bad about it if I still do? I find it makes the code clearer, but that may just be due to my inexperience. – NoNameNo123 Jun 21 '19 at 19:41
  • Nothing really "bad" about using Call - it's just not really in common use. Some discussion here also: https://stackoverflow.com/questions/479891/what-does-the-call-keyword-do-in-vb6 – Tim Williams Jun 21 '19 at 19:48
8

The "official help page" is on GitHub, it's actively maintained, and multiple changes are being merged every day. If there's an error in an example, open an issue for it, or better, submit a fix yourself!

The example is wrong, the parentheses either shouldn't be there, or the expression should be on the right-hand side of a Set assignment to some object variable.

you have to use parentheses when assigning the return value of a method (or function) to a variable

Correct.

When you don't capture the return value, you don't put the parentheses. If you do, the VBE gives you a hint. If you copied the example from the docs, it would look like this in the editor:

ActiveWorkbook.Sheets.Add (Before:=Worksheets(Worksheets.Count))

Note the space. If you captured the return value:

Set newSheet = ActiveWorkbook.Sheets.Add(Before:=Worksheets(Worksheets.Count))

No space.

Just to make sure I get the VBA logic: #3 gives me an error because the parentheses to VBA means the value (= the new worksheet) gets returned, but there's no variable to assign it to? And #6 is the opposite case?

There's more to it than that. Consider a simpler example:

MsgBox "hi", vbOkCancel

If we wanted to capture or otherwise use the return value, we would need the parentheses:

If MsgBox("hi", vbOkCancel) = vbOk Then

If we added parentheses without capturing/using the return value, we would have this:

MsgBox ("hi", vbOkCancel)

So what does this space mean?

To the VBA compiler, it means "this isn't the argument list, it's the first argument, and this is a value expression: evaluate it, and send the result ByVal to the invoked procedure". The problem, of course, is that ("hi", vbOkCancel) isn't an expression, and can't be evaluated, and we have a compile error.

So back to the docs example: Before:=Worksheets(Worksheets.Count) isn't a legal expression either - it's an argument list consisting of one named argument... but syntactically it's not the argument list: parenthesized, it's an expression that, if it could be evaluated, would be passed to the first argument of the parameter list, ByVal - like so:

ActiveWorkbook.Sheets.Add Argument1:=(the result of the expression)

The ByVal nature of the parenthesized argument is basically an accident: when VBA evaluates the expression, it gets a value... but that value is up in the air, there's no local reference to it - so even though the invoked procedure is accepting a ByRef argument, since the caller isn't holding a reference to that argument, it's discarded - effectively producing the exacty same result as if the function took the parameter ByVal.

Confusing? This should help:

Public Sub Test()
    Dim foo As Long

    DoSomething (foo) ' evaluates the expression, passes the result of that expression
    Debug.Print foo ' prints 0

    DoSomething foo ' passes a reference to the local variable
    Debug.Print foo ' prints 42
End Sub

Private Sub DoSomething(ByRef value As Long)
    value = 42
End Sub
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235