New Excel Forum

This forum has been moved to TeachExcel.

Ask all future questions in the new excel forum.


Arguments and Parameters: Passing Variables to Functions and Other Procedures

Free Excel Tips and Tutorials
Forum rules
This forum is closed.

All new posts should be made at our new Excel Forum at

Arguments and Parameters: Passing Variables to Functions and Other Procedures

Postby Sisyphus » Thu Sep 20, 2012 4:18 am

Arguments and parameters are two sides of the same coin. What you pass to a procedure is a parameter, what the procedure receives is called an argument. In both cases it is a value placed at the procedure’s disposal by the source of the call.

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.
  1. 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.
  2. 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.
Most experts agree that it is better to declare the data type expressly, even when declaring Variants. I suggest also to declare the method of access – ByVal or ByRef – because what you don’t see will be harder to find if it causes an error. Where a parameter is itself a declared variable in the calling procedure the argument that receives it must be of the same data type. You an’t pass a String to a Variant even though a Variant is capable of containing a String.

Code: Select all
Sub Proc(ByVal Number As Long, ByRef Ws As Worksheet, Optional ByVal HideBlank as Boolean, Optional ByVal MakeBold as Boolean = True)
This declaration has four arguments which are separated from each other by commas. There is no particular order to them except that Optional arguments can’t be followed by non-optional ones. “Optional” means that the calling procedure doesn’t need to supply them. This means that an error will occur if a non-optional argument is not furnished. It also means that optional arguments must be given default values. For this there are several ways.
  • 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.
Have a great day! :D

I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Former Moderator
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 1 guest