Executing methods - parentheses or no parentheses?

When you invoke a Sub with Call, you must use parens around the arguments:

Please Login or Register  to view this content.

When you assign the result of a function to a variable, you must use parens around the arguments:

Please Login or Register  to view this content.

If you want to pass arguments by value or by reference, according to the procedure's signature, you don't include parens:

Please Login or Register  to view this content.

If you want to pass arguments ByVal, irrespective of the procedure’s signature (which won't work for a procedure expecting an array (because arrays are always passed by reference) or an object), you enclose the argument in parens, or an extra set of parens. In the examples below, arg1 is passed by value, and arg2 according to the procedure’s signature:

Please Login or Register  to view this content.

Objects are always passed ByRef, so you can never put unnecessary parens around an object variable. Chip Pearson (http://www.cpearson.com/excel/byrefbyval.aspx) explains the meaning of ByRef and ByVal for an object variable:

The ByRef and ByVal modifers indicate how the reference is passed to the called procedure. When you pass an object type variable to a procedure, the reference or address to the object is passed -- you never really pass the object itself. When you pass an object ByRef, the reference is passed by reference and the called procedure can change the object to which that reference refers to. When an object is passed ByVal, a copy of the reference (address) of the object is passed.

Methods are procedures. There are two types of procedures, and therefore two types of methods. The rules below apply

A Sub has 0 or more parameters.
A Function has 0 or more parameters, and also returns a value.

If you are calling a Sub just by its name, then the parentheses around parameters must be omitted. However, if you use the Call statement, then the parentheses must be included.

Please Login or Register  to view this content.

If you wish to call a Function and not capture the return value, you may call it using the same rules as for calling a Sub.

Please Login or Register  to view this content.

I cannot explain why you are able to call the method Add as a Sub without the Call statement and still include the parentheses.

When a Function method returns an object, you may assign that object to a variable (if the object is defined and there is no type conflict in doing so). Objects have a default attribute, which is what will be assigned to the variable. For example, the default attribute of a Range object is the Value attribute.

Please Login or Register  to view this content.

After this code executes, range R2 will have a copy of the value that is in R1. R1 still refers to cell A1, and R2 still refers to cell B2, but now cell B2 has a copy of the value from A1.

But when you use Set, it means that you are causing the object variable to point to the same object as the one you are setting it to. It is much different than using assignment without Set.

Please Login or Register  to view this content.

After this code executes, R2 and R1 both refer to cell A1.

I hope this takes some of the mystery out.

I cannot explain why you are able to call the method Add as a Sub without the Call statement and still include the parentheses.

When you add parens around an argument that doesn't require them, the argument is evaluated, and the result of the evaluation is passed to the procedure. It means the argument is passed by value regardless of how the procedure requested it.

That also explains why you can't use parens around two or more arguments when they are not required; the evaluation of (arg1) may be meaningful, but the evaluation of (arg1, arg2) is not. That's why this generates an error:

Please Login or Register  to view this content.

That also explains why if you call a procedure expecting a range with parens where none are required, you get a type mismatch error; because the evaluation of a range is a Variant, or Variant/Array.

Parens means completely different things according to context. VBA might have used some other bookends (curly braces, square brackets, whatever) to indicate enclosing a list versus a call for evaluation, but it didn't.

You're welcome.