VBA: Usage of parentheses for a method

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.

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