Newton Excel Bach, not (just) an Excel Blog

The use of inappropriate techniques for passing data between sub-routines and modules is probably one of the most frequent causes of error in VBA programs, but many text books provide little guidance on this subject, and what there is, is often too general to be of much use. This post will focus on the basics, and a following post will look at some details affecting passing of arrays and ranges. The best source of more detailed information I know of is: Chip Pearson on Scope and ByVal and ByRef.

By default, when a variable or constant is declared within a VBA subroutine or function it is only available within that routine. The main ways of make the information available to other routines are:

• Declare the variable with a wider “scope”
• Copy the information to a worksheet, or to another file available to other VBA routines and possibly external routines
• Pass the variable to a called subroutine or function at the time of calling

Declaring Module or Project wide scope

For a constant, since the value will not be changed during the execution of a routine, it generally makes sense to give it the widest scope possible. That is declare it as Public at the top of a module. In this way the constant will be available to every module in the project. For instance:

Public Const Pi As Double = 3.14159265358979

For large projects it is a good idea to put all constants in their own module.

For variables it is also possible to declare them at the top of a module so that they will be available anywhere within the module (if declared as Private) or anywhere within the project (if declared as Public). This is quick and easy, but in general it is not a good idea. The problems are:

• Any change to a variable will be affect every procedure where the variable is used, and this may not be what was intended.
• A variable of the same name may be declared within any procedure without any warning being generated. Changes to this variable will only will only be seen within the procedure where it is declared, but it may be assumed (especially at a later date) that the variable with global scope is applicable in every routine.

Transferring variable contents to a worksheet or file

The simplest way to accomplish this is to declare the variable as a range:


Dim RangeVariable as Range
Set RangeVariable = Range("Rangename")


Any changes made to the RangeVariable object will now change the worksheet range (since that is what the RangeVariable refers to), and this changed data will be available from any other routine or module within the project (or indeed from another project). The problem with this approach is that the transfer of data between VBA and the spreadsheet is very slow, and if the data in the range object changes frequently this can result in a significant loss of performance.

Passing Variables between routines

This is of course the main method of transferring data within VBA. Examples are:

1. ReturnVal = FunctionName(Var1, Var2, Var3)
2. SubName Var1, Var2, Var3
3. Call SubName(Var1, Var2, Var3)
4. SubName(Var1), (Var2), (Var3)
5. Call SubName((Var1), (Var2), (Var3))

These are used to call:

Function FunctionName(Var1 as Double, ByRef Var2 as Double, ByVal Var3 as Double) as Double
Sub SubName(Var1 as Double, ByRef Var2 as Double, ByVal Var3 as Double)

Variables may be passed either “by reference” (ByRef) or “by value” (ByVal). Passing by reference passes the memory address of the variable, so that if the value of the variable is changed by the called subroutine, that change is seen by the calling routine, which continues to use the same memory address as the location of this variable. In contrast a variable passed by value may be changed by the called routine without affecting the value of the variable in the calling routine.

Examples can be found in ByRefByVal1.xls.

The response of the different varieties of passing variables is illustrated in the screenshot below:

Output from data passed ByRef and ByVal

The routine used to produce this output performed the following tasks:

1. Read the values in cells B3 to D3 and assign to the variables Var1 to Var3
2. Pass the variables Var1 to Var3 to PassFunc and add 1 to each value
3. Copy the resulting values to cells B4 to D4
4. From the calling routine, copy the value of Var1 to Var3 to cells E4 to G4
5. Copy the function return value to cell H4
6. Repeat steps 2 to 4, but passing the variables to subroutines using the syntax styles of examples 2 to 5 above

The results of this excercise illustrate the following points:

• Passing by reference is the default; the results for Var1 (method of passing not specified) and Var2 (passed by reference) are therefore always the same.
• Var3 is passed by value, so the increment in its value by the called subroutine is not passed back to the calling routine.
• Function return values (in this case Var3) do reflect the increment in value.
• When the variables passed to a subroutine are enclosed in brackets these variables are passed by value, even if by reference is specified in the called routine.
• Thus in Sub3 and Sub4 the increment in Var1 and Var2 is not passed back to the calling routine, even though these variables were passed by reference. This behaviour is particularly confusing if a single variable is passed to a subroutine with the variable enclosed in brackets, since this will be passed by reference by default if the Call statement is used, but always by value if the subroutine is called without the call statement.

When ByRef and when Byval?

In some circumstances the logic of the programme dictates the method of passing variables, so if any changes to the variable are required to be passed back to the calling routine (and it is not a function return value) then it must be passed by reference, and if the variables in the calling routine should not be affected by changes in the called routine, then they must be passed by value. But what is the best practice when the passed variable is not changed in the called routine, or is not used again in the calling routine? There are arguments for different procedures, but my preference is:

• If the program logic is not affected by the method of passing, then leave it as unspecified (and hence by reference is adopted as the default).
• If the program logic demands that the value must be passed by reference, then specify by reference. This then acts as a flag that the method should not be changed unless the program logic changes.
• If the program logic demands that the value must be passed by value, then there is no option, by value must be specified.

In my opinion this procedure optimises performance (by passing by reference whenever possible), aids readability, but also flags those procedures where the correct method of passing is vital to correct performance.