excel - VBA: Usage of parentheses for a method

Question

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.

Answer 1

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.