6

Executing procedures

This chapter demonstrates how to manage subroutine procedures and how to deal with errors in VBA macros.

Calling subroutines
Modifying scope
Passing arguments
Adding modules
Fixing values
Debugging code
Handling errors
Summary
Calling subroutines
A VBA subroutine can contain any number of statements, which are executed from beginning to
end whenever the macro is run. Execution of these statements is referred to as a “procedure”.
Lengthy subroutines are often better divided into separate, smaller procedures that each perform a
specific task. This simply requires the creation of individual subroutines for each task, and a main
procedure that calls upon the individual subroutines as needed. This modular approach is good
programming design, as it makes the code easier to maintain.

See here for more on accessibility status.

Individual subroutine procedures are created using the Sub keyword followed by a valid
identifier name, as usual. By default, they have global accessibility status. This means they can be
called upon to execute their statements, using the Call keyword followed by their name, from
within any other subroutine in the workbook:

[Link]
Begin a VBA macro module with a subroutine that will later call other subroutine
procedures
Sub Main( )
‘ Statements to be inserted here (Step 5).
End Sub

Next, add a subroutine procedure that will write the current day name into a cell
Sub GetDay( )
Range( “A1” ) = Format( Now , “dddd” )
End Sub

Now, add a subroutine procedure that will write the current date into a cell
Sub GetDate( )
Range( “B1” ) = Format( Now , “mmmm d ,yyyy” )
 End Sub

Then, add a subroutine procedure that will write the current time into a cell
Sub GetTime( )
Range( “C1” ) = Format( Now , “hh:nn am/pm” )
End Sub

Finally, insert statements to execute the procedure in each subroutine when the macro runs
Call GetDay
Call GetDate
Call GetTime

Insert a Button on the worksheet, then choose the Main procedure in the “Assign Macro”
dialog and click OK

Push the Button to have the main subroutine call each other subroutine procedure to
display day, date, and time

Parentheses are not required after the subroutine name in the Call statements.

The dialog displays all subroutine procedures that have Public access, so you could
assign any one of these to the Button control to just execute that procedure.
Modifying scope
Accessibility status determines the availability of procedures and variables across the macro
code. This is known as their “scope”.
Unless their declaration explicitly specifies accessibility status, variables will by default have
“local” scope. This means they are not accessible outside the procedure in which they are
declared. Attempting to reference a local variable declared in another procedure will produce a
“Variable not defined” compile error.

You can create a variable that has wider scope by declaring it with the Dim keyword outside any
procedure, in the declarations section at the beginning of the module. This means that the variable
will be accessible from any procedure within that module:
Dim variable-name As data-type

Alternatively, you can create a variable that has “global” scope by declaring it with the Public
keyword outside any procedure, in the declarations section at the beginning of the module. This
means that the variable will be accessible from any procedure within all other modules in that
workbook, like this:
Public variable-name As data-type

Most declarations are contained within procedures. Only directives, such as Option
Explicit, and non-local variable declarations normally appear in a macro’s declarations
section.
The use of non-local variables is, however, best avoided wherever possible as it can introduce
conflict – which local scope prevents.
Conversely, subroutine procedures will by default have “global” scope so they are accessible
from any other subroutine procedure in any module. You can create a subroutine procedure that
has limited scope by declaring it with the Private keyword, like this:
Private Sub procedure-name ( )

A subroutine procedure that has Private accessibility status is only accessible to other procedures
in the same module. It can be called from other procedures but cannot be assigned to a Button
control. The use of Private procedures is good programming practice and is recommended
wherever possible to safeguard your code:

[Link]
Begin a VBA macro module by declaring two variables at the beginning of the macro
code
Dim ModuleVar As Integer
Public GlobalVar As Integer

Add a subroutine procedure that initializes each variable

Sub Main( )
ModuleVar = 4
GlobalVar = 16
‘ Statement to be inserted here (Step 4).
End Sub

Next, add another subroutine procedure that references both variable values to display a
resulting total
Private Sub ModuleProcedure( )
[Link] = ModuleVar * GlobalVar
End Sub

Finally, insert a statement to execute the procedure above

Call ModuleProcedure

Insert a Button on the worksheet, then choose the Main procedure in the “Assign Macro”
dialog and click OK
 Push the Button to have the main subroutine call the private subroutine procedure to
display a result

Each procedure in this example references non-local variables, but local variables could
be used instead and their values passed as arguments in calls to other procedures –
see here.

Notice that the dialog does not display subroutine procedures that have Private access,
so you cannot assign any of these to be run by the Button control.
Passing arguments
VBA procedures can be created to accept arguments when called, whose values can then be used
by its statements. The arguments can be any type of variable, constant, expression, or a “literal”
value such as a number or a string of text.
It is important to recognize that arguments can be passed to procedures in two distinctly different
ways:
• By Reference – This is the default way of passing arguments. Passing a variable, for
example, internally passes the memory address of the variable. This means that any changes
made by the procedure are made to the original variable.
• By Value – This is a less common way of passing arguments. Passing a variable, for example,
internally passes only a copy of the original variable. This means that any changes made by
the procedure are not made to the original variable.
To create a subroutine procedure that accepts an argument is simply a matter of specifying an
identifier name for the argument within the parentheses of the declaration, with this syntax:
Sub procedure-name ( argument-name )

The next example demonstrates passing arguments by value – see here.

The argument can then be referenced within the procedure using its specified identifier name.
Unless explicitly specified, the argument will be created as a Variant type that can accept any type
of data. It is good practice to additionally specify an acceptable data type in the declaration, to
prevent inappropriate data being passed by the caller, like this:
Sub procedure-name ( argument-name As data-type )

Multiple arguments can be specified in a procedure declaration as a comma-separated list within

the parentheses, and can be of different data types, like this:
Sub procedure-name ( arg1 As type , arg2 As type , arg3 As type)
In this case, the caller must provide the correct number of arguments, and each must be of the
correct data type. Attempting to pass an argument of an inappropriate data type will produce an
“Argument type mismatch” compile error.

[Link]
Begin a VBA macro module with a subroutine that declares and initializes two variables
Sub Main( )
Dim Number As Integer
Dim Factor As Integer
Number = 4
Factor = 16
‘ Statement to be inserted here (Step 3).
End Sub

Next, declare a Private procedure that must accept two arguments, of the Integer data type
only, for reference in a statement that will display a result
Private Sub Multiply( Number As Integer , Factor As Integer )
[Link] = Number * Factor
End Sub

Now, insert a statement to call the procedure above, passing two arguments of the correct
data type by reference
Call Multiply( Number , Factor )

Insert a Button on the worksheet, then choose the Main procedure in the “Assign Macro”
dialog and click OK
Push the Button to have the main subroutine call the private subroutine procedure to
display the result

The names of arguments need not be the same as the names of the variables being
passed. They can be given any valid name, but it helps to easily keep track of values if
the same names are used for both.

This example is similar to the previous example, but happily avoids the use of non-local
variables.
Adding modules
Many VBA programmers prefer to create procedures in separate modules to distinguish different
types of utility, especially in lengthy complex projects. You can add further modules from the
Visual Basic Editor’s menu by selecting Insert, Module or from its Project window by right-
clicking on the project then choosing Insert, Module from the context menu.

A VBA project can comprise any number of modules.

Subroutine procedures created in added standard VBA modules (e.g. “Module2”) are accessible
throughout that module and from the first standard module (“Module1”), as they have global
scope by default. They do not appear in the “Assign Macro” dialog list, even though they’re not
declared with the Private access keyword.
Subroutine procedures in added modules can therefore be called just like any other procedure –
unless they have been declared with the Private access keyword to restrict accessibility to other
procedures within the same module. Procedures can be created to accept arguments that can be
passed from the caller, by reference (the default) or by value, from any module within the project.
It is important to understand the difference between arguments passed by reference and arguments
passed by value, to avoid errors. A variable argument passed by value can be manipulated in a
procedure to produce a result without affecting the value stored in the original variable.
Sometimes this may be what you need, rather than the default of passing by reference, in which
manipulation in the called procedure changes the value stored in the original variable. To pass by
value, you simply need to add the ByVal keyword before the argument name in the declaration:

[Link]
Begin a VBA macro module with a subroutine that declares and initializes a variable
Sub Main( )
Dim Number As Integer
Number = 4
‘ Statements to be inserted here (Steps 5-7).
 End Sub

Next, select the Insert, Module menu items to add a second module to the project

Begin the second module by declaring a procedure that will display the result of
manipulating an argument passed by value
Sub CubeByVal( ByVal Number As Integer )
Number = (Number * Number * Number )
Range( “D1:E1” ) = Array( “Cubed Value:” , Number )
End Sub

Again in the second module, add a similar procedure that will display the result of
manipulating an argument passed by reference
Sub CubeByRef( Number As Integer )
Number = (Number * Number * Number )
Range( “D3:E3” ) = Array( “Cubed Value:” , Number )
End Sub

Now, return to the first module and insert statements to display the initial variable value
twice
Range( “A1:C1” ) =_
Array( “ByVal” , “Initial Var Value:” , Number )
Range( “A3:C3” ) =_
Array( “ByRef” , “Initial Var Value:” , Number )

Insert a call to pass the variable by value, then display its current value after manipulation
Call CubeByVal( Number )
Range( “F1:G1” ) = Array( “Current Var Value:” , Number )

Finally, insert a call to pass the variable by reference, then display its current value after
this manipulation
Call CubeByRef( Number )
Range( “F3:G3” ) = Array( “Current Var Value:” , Number )

Insert a Button on the worksheet to run the macro, then hit the button to compare passing by
value and reference

The ByVal keyword applies to a single argument, not the entire argument list. If you
want multiple arguments to be passed by value, you must include the ByVal keyword
before each individual one in the argument list.

Always remember that by default, arguments are passed by reference, so any

manipulation will change the original variable value.
Fixing values
Local variables within a procedure normally exist only while the procedure is executing its
statements – when the procedure ends, the variable does not retain the value it contained. If you
would like a variable to retain its value when the procedure ends, you can declare it using the
Static keyword, rather than the Dim keyword:

Static variable-name As data-type

If you want to create a subroutine procedure in which all its variables retain their value when the
procedure ends, you can declare the procedure using the Static keyword, like this:
Static Sub procedure-name ( arguments-list )

Typically, the type of procedure created to retain its variable values might be passed argument
values when called, to update the stored values while that workbook is open. The variables will,
however, lose their values when the user closes the workbook.
A subroutine procedure declaration can usefully specify arguments with the Optional keyword, to
allow the caller to supply or omit an argument value when calling the procedure. When this is
used, the declaration must also specify a default value that the procedure can use when the caller
omits an argument value:
Static Sub procedure-name ( Optional argument-name = default-value )

Every argument in the argument list that follows an Optional argument must also be
Optional – so it’s a good idea to declare Optional arguments at the end of the list.
Specifying a default argument value of zero allows the caller to optionally access a current
numerical value or to update a stored numerical value:

[Link]
Begin a VBA macro module with a main subroutine
Sub Main( )
‘ Statement to be inserted here (Step 7).
End Sub

Next, add another subroutine that will retain its variable values and optionally accept a
single argument
Static Sub FixValue( Optional Number As Integer = 0 )
‘ Statements to be inserted here (Steps 3-6).
End Sub

In the second subroutine, insert declarations for two variables that will retain their values
Dim Count As Integer
Dim Total As Integer

Now, insert a statement to increment the first variable every time the procedure is called
Count = Count + 1

Then, insert statements to increase the value stored in the second variable when a value is
passed by the caller
If ( Number > 0 ) Then
Total = Total + Number
End If

Insert a statement to display the number of times the procedure has been called and the
current stored total
Range( “B1:F1” ) = _
Array( Count , “Added:” , Number , “Total:” , Total )

Turn your attention to the first subroutine and insert a statement to call the second
subroutine and pass a value
Call FixValue( Range( “A1” ).Value )

Insert a Button on the worksheet to run the macro, then enter values into cell A1 and hit the
button to see the count increment and the total increase
You can declare the procedure with Static and the variables with Dim, or declare the
procedure without Static and the variables with Static instead of Dim.

If you click the Stop button on the Visual Basic Editor menu it will reset the macro and
you will lose these variable values.
Debugging code
It is sometimes useful to closely examine the progression of a program, by watching its execution
line by line to locate any bugs. In the Visual Basic Editor you can set a “breakpoint” to halt
execution on a particular line, then click the Step Into option on the Debug menu to move through
the program one line at a time. When you begin debugging, you can open a Locals window to
monitor the value of particular local variables as execution proceeds – such as those in the nested
loop construct shown here:

[Link]
Begin a VBA macro module with a subroutine that declares three variables and contains
nested loops
Sub Main( )

Dim i As Integer
Dim j As Integer
Dim Pass As Integer
For i = 1 To 3
For j = 1 To 3
Pass = Pass + 1
Next j
Next i
End Sub

In the Code Editor, click in the gray margin against each line containing the Next keyword
to set two breakpoints

Click the Start button and see the application run to the first breakpoint it meets

Click View, Locals Window to launch the Locals window and notice the current value of
each variable
When you set a breakpoint, a dot appears in the margin. You can click the dot to
remove the breakpoint. Yellow arrows and highlights indicate the current position.
Watch the variable values change as you repeatedly click the Start (Continue) button to
move to each Next breakpoint until you reach the third outer Next statement
Use Debug, Step Into to reach the end of the subroutine

Programming errors are often called “bugs” and the process of tracking them down is
often called “debugging”.
The Locals Window shows all variables in current scope as the macro proceeds.
At the end of the subroutine each counter variable has been incremented beyond the upper limit
set in the For statements, to exit each loop, and there has been a total of nine iterations (3 x 3).
Click the Stop button to finish, then click the Start button to once more run to the first
breakpoint
Click View, Immediate Windows to launch the Immediate window

In the Immediate window, type i = 3 and hit Enter, to immediately move to the start of the
third outer loop
Use the Debug, Step Into option to step through each line of the final complete outer loop
iteration

Any code you type into the Immediate window is dynamically applied to the application
being debugged, but does not change its code. Try typing MsgBox i+j into the
Immediate window, then hit the Enter key.
Handling errors
The Visual Basic Editor helpfully catches syntax errors in your code when you try to run a macro,
and displays an error dialog describing the nature of the error and halts execution of the
procedure. For example, when it encounters an undefined item:

As execution has stopped, the programmer must correct the error before the procedure can
complete. Similarly, when a runtime error occurs, VBA will halt execution and display an error
dialog describing the nature of that error.

Ensure you verify the Visual Basic Editor Error Trapping settings. From the VBE menu,
select Tools, Options then choose the General tab and be sure to check the option to
Break on Unhandled Errors.
The macro programmer must attempt to anticipate how a user might cause runtime errors and plan
how to handle them. If you perhaps anticipate a possible trivial error, of little consequence to the
macro, you can have VBA ignore the error without displaying the standard error dialog, by adding
this statement at the beginning of the procedure:
On Error Resume Next

Alternatively, a custom error handler can be created at the end of the procedure, following an Exit
Sub statement that will otherwise complete the procedure. This will skip execution without
displaying the standard error dialog. To use a custom error handler, simply add this statement at
the beginning of the procedure:
On Error GoTo ErrorHandler
The label does not have to be named “ErrorHandler”, but it is common practice to use
that name.
Your custom error handler can retrieve the standard description of an error from the Description
property of the VBA Err object, and you can extend this to provide a more user-friendly message:

[Link]
Begin a VBA macro module with a subroutine that declares and initializes a variable, then
displays a result
Sub Main( )

‘ Statement to be inserted here (Steps 3 & 5).

Dim Number As Double
Number = Range( “B1” )
Range( “C1:D1” ) = Array( “Tripled:” , ( Number * 3) )
‘ Statement to be inserted here (Step 6).
End Sub

Insert a Button on the worksheet to run the macro, then enter a non-numeric value in cell
B1 and hit the button to see a standard error dialog

Click End to close the standard error dialog, then insert this statement at the beginning of
the procedure
On Error Resume Next

Again, enter a non-numeric value in cell B1 and run the macro to see the procedure
complete – but not as desired

Edit the statement you inserted at the beginning of the procedure in Step 3 to become
On Error GoTo ErrorHandler

Now, add this custom error handler at the end of the procedure, just above the End Sub line
Exit Sub
ErrorHandler: MsgBox [Link] & vbNewLine _
 & “The Number Must Be In Numeric Format Only” _
& vbNewLine & “Please Try Again”

Once more, enter a non-numeric value in cell B1 and run the macro to see a user-friendly
error message

Enter a value in numeric format into cell B1 and run the macro to see the procedure
complete – as desired

Notice that the standard error dialog provides a button to debug the error – not very
user-friendly for other users.

You don’t need to display the standard error description at all, if you prefer just to
display your own description.
Summary
• Subroutine procedures are created using the Sub keyword and are called using the Call
keyword followed by their name.
• By default, variables have local scope and subroutine procedures have global scope.
• The declarations section can be used to create module-level variables with the Dim keyword,
and global variables with the Public keyword.
• The Private keyword can be included in a subroutine declaration to restrict its accessibility to
only that module.
• Arguments passed by reference do affect the original item, but arguments passed by value do
not affect the original item.
• The As keyword can be used to specify the data type of an argument in a procedure
declaration.
• Multiple arguments can be included within the parentheses of a procedure declaration as a
comma-separated list.
• The ByVal keyword can be used in an arguments list to specify that the argument will be passed
by value.
• The value contained in a local variable is lost when the procedure ends, unless it was
declared as a Static variable.
• Procedure declarations can include the Static keyword to ensure that all its variables retain
their value on completion.
• The Optional keyword can be used in a procedure declaration to specify that an argument may
be omitted by the caller.
• Arguments that are optional must specify a default value and appear at the end of a procedure
declaration arguments list.
• Breakpoints allow the code to be examined line by line when debugging errors.
• An On Error Resume Next statement can be included at the start of a procedure to ignore errors.
• An On Error GoTo statement can be included at the start of a procedure to jump to a custom
error handler, added after an Exit Sub statement at the end of a procedure.