VBA: Usage of parentheses for a method

by

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

More Related Contents:

  1. How to avoid using Select in Excel VBA
  2. Cycle through sub-folders and files in a user-specified root directory [duplicate]
  3. Convert Text to Date?
  4. Run Excel Macro from Outside Excel Using VBScript From Command Line
  5. Importing text file into excel sheet
  6. Loop through each cell in a range of cells when given a Range object
  7. Checking for #N/A in Excel cell from VBA code
  8. Non-web SQL Injection
  9. Error: “Could Not Find Installable ISAM”
  10. Filling any empty cells with the value above
  11. Is there a way to slow down a Web Scraper so it will pick up the code?
  12. Create a folder and sub folder in Excel VBA
  13. One-dimensional array from Excel Range
  14. Renaming Files with Excel VBA
  15. Excel vba to create every possible combination of a Range
  16. Run a Macro from Another Workbook
  17. Sorting a dictionary by key in VBA
  18. Handle “No cells were found” error when filtered range is empty
  19. VBA paste as values – how to
  20. What are the benefits of using Classes in VBA? [closed]
  21. Writing a VLOOKUP function in vba
  22. Generate Word Documents (in Excel VBA) from a series of Document Templates
  23. Handle cancellation of InputBox to select range
  24. Using Excel VBA Macro To Capture + Save Screenshot of Specific Area In Same File
  25. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  26. VBA: How to combine two ranges on different sheets into one, to loop through
  27. Delete all data rows from an Excel table (apart from the first)
  28. Excel Hide/Show all tabs on Ribbon except custom tab
  29. Macros Not Showing Up in Macro Table
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment