An argument must be given a name which can be the same as the name of a variable serving as parameter. The name is defined between the brackets in the procedure’s declaration line, for example Sub Proc(Number). Here “Number” is the name of the argument. This example demonstrates Excel’s propensity for omitting defaults. The argument’s complete declaration should be ByRef Number As Variant.
- ByRef means that the value is passed as an address.
This is very useful because you can modify the variable in the procedure and then continue using its new value in the calling procedure. It is also very fast because no large volume of data is copied, just a number specifying an address in the RAM. Most objects, such as Worksheets or Ranges can only be passed ByRef. However, in the case of numbers or text strings you may prefer to pass a copy to a sub where it might be modified without changing the parameter’s original value. For such purposes you would declare the argument as ByVal. - Number isn’t a good name for a Variant because a Variant can be anything, even an array, but not an object. Excel presumes all variables to be Variants unless expressly declared as something else. Variants are relatively large and slow. It is a bit like giving you two Swiss army knives to eat your steak instead of fork and knife.
- Code: Select all
Sub Proc(ByVal Number As Long, ByRef Ws As Worksheet, Optional ByVal HideBlank as Boolean, Optional ByVal MakeBold as Boolean = True)
- Optional ByRef Ws As Worksheet
The code in procedure might include this line:
If Ws Is Nothing Then Set Ws = ActiveSheet
This would enable the procedure to handle any worksheet but default to the active one if none is specified. - Variables other than objects have system-given default values.
As a rule variables are of null value when declared. That translates into a string of zero characters length, a value of zero in case of numbers or a value of Not True in the case of Booleans. You can use these values in your procedure’s code. - You can also assign default values during declaration
Optional ByVal MakeBold as Boolean = True assigns the value of True to the variable if it is not supplied. The supplied value may or may not be the same as the default.
Note, however, that Optional ByRef Ws As Worksheet = ActiveSheet would NOT have the same effect as the code mentioned above because objects can't be declared in this manner.
To call the above procedure you can simply list the paramters in the sequence in which the parameters are declared, for example Proc 100, Worksheets(“Sheet1”), False, True. If, instead of defining the third parameter you want to use its default you can omit it, but you must retain the comma, like this
Proc 100, Worksheets(“Sheet1”), , True.
However, if you have many arguments and you wish to omit some counting the commas can become a bothersome source of errors. In such cases you may prefer to resort to using named parameters, for example
Proc 100, Worksheets(“Sheet1”), MakeBold:=True
In this example the first two parameters are given in sequence of arguments. The last (fourth argument) is named because the third argument isn’t supplied. So, when using named arguments their sequence is of no consequence. This leads to the suggestion that you might use named parameters for the purpose of better readability of your code.
- Code: Select all
Proc Ws:= Worksheets(“Sheet1”), Number:=100, MakeBold:=True
This example demonstrates that the capability of enhancing the code’s readability in this manner depends to a large extent upon the nature and quality of the names.
- Worksheets(“Sheet1”) is much more descriptive than Ws
The only benefit from using the argument’s name is in altering the sequence which could also be achieved by changing the sequence of arguments in the declaration. - Number:=100 demonstrates a bad choice of argument name
This could be greatly improved by calling the argument RowNum. - MakeBold:=True demonstrates the full power of this method.
The True in Proc 100, Worksheets(“Sheet1”), , True begs for definition which is provided by the argument’s name. This is the reason why Excel’s VBA is using this method of passing arguments for many of its own functions such as Open or Find.