Getting the Most Out of Subroutines in VBA

Up until this point of the series, the Sub statement–short for subroutine–has been one of the most frequently used statements in tutorials and exercises. In their plain vanilla form, a subroutines serve one purpose — to contain procedural code. However, having a comprehensive understanding of how subroutines work and various options available will help you better organize and compartmentalize your code.


This post within the Getting Started with VBA series begins the focus on Functions and Subroutines in VBA.

While simple procedures can be written without knowing much of the ins-and-outs of subroutines, having a better understanding of how subroutines work and their lesser-known features will open doors to writing much more complex Excel applications. The Subroutine is the core of any VBA script. All VBA commands and procedures must be contained within a Subroutine, and the Subroutine is the core mechanism for calling/initiating all procedures and functions.

A subroutine allows a developer to break up a complex program into specific procedure calls. Doing so provides the following benefits:

  • Easily organize operations into logically named routines.
  • Protect parts of code and its variables from being impacted through other procedures.
  • Easily re-use procedures in many situations.

Sample File

The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening.


There are a few important terms which will be used throughout this post.

Caller vs Callee: While not officially recognized terminology, this post will use the words Caller and Callee to distinguish between the subroutine which calls (caller) another subroutine (callee).

Module: A container element within VBA which contains one or more subroutines. Subroutines can only exist within modules. Instructions on how to create a module can be found here.

Subroutine (also Subroutine Declaration): As opposed to a “called” subroutine, a subroutine declaration contains the procedural elements of the subroutine, without necessarily including any of the actual values. The subroutine declaration includes all instructions that the subroutine should follow once it is called (and variables are assigned values, either through arguments or other inputs). In short, a subroutine declaration is the procedural statements that make up a subroutine.

Parameter: A special kind of variable used in a subroutine to refer to one of the pieces of data provided as input to the subroutine. When a subroutine is called, a variable of literal value can be passed through a parameter to be used in the function. Within this post, a parameter refers to a declared variable within a subroutine declaration that is being set up as a “callee” subroutine.

Argument: A specific variable or literal value being assigned to a parameter within a called subroutine.

Call: The act of invoking a “callee” subroutine from within a “caller” subroutine.

Subroutine Vs. Functions

In some contexts, the word “subroutine” is used interchangeably with “function”. While both subroutines and functions can be called to perform specific actions, generally, the purpose of a function is to return a specific value to the caller subroutine from which it is called. This value can be assigned to another variable, used within a conditional, or displayed for the user. A subroutine, on the other hand, will perform an action but will not return a specific value.

Using Subroutines

In its simplest form, the act of writing a command within a Sub() represents the use of subroutine. With the exception declaring enumerations, constants, or global variables, all VBA commands must exist within a subroutine.

A VBA project can contain any number of subroutines. Basic macros may require one subroutine with few lines of code, while more complex Excel applications can encompass dozens of subroutines, each containing hundreds of lines of code. How you break your code up into separate subroutines is dependent on your preferences and what you feel would be readable and clear to other coders. Always remember that you may need to access the code again, days, weeks, and months down the road — make things so they would be clear to your future self.

A general rule of thumb is to keep subroutines down to specific actions. For example, you may have a procedure collect data from a spreadsheet form, validates the data, then place the data in a table. One good practice might be to keep the data transfer and validation procedures in separate subroutines. While both tasks are required to complete the full procedure, an argument can be made that they are unrelated and deserving of their own subroutine.

Basic Subroutine

Before writing a simple procedure within a subroutine, you must add a module in the VBA module. Here are instructions on how to open the VBA editor and create a new module.

A subroutine can contain as many or as few lines of code as needed. Complex subroutines which deal with complicated calculations, such as financial or statistical analysis, may contain hundreds or even thousands of lines of code. In its most basic form, a subroutine can include as few as three lines of code: a line to start the sub, a line of code (such as a message box), and a line to end the sub. The following is an example of a three line subroutine.

There are a few general rules to go by when naming a subroutine. With the exception of subroutines which are assigned a Public scope (see the Subroutine Scope section below), each subroutine must have a unique name within its host module. Subroutine names cannot contain any spaces. Although not required, it’s useful to establish a consistent naming convention for subroutines, such as words in proper-case, separated by underscores.

Calling a Subroutine

There are many ways to call a subroutine. The method you choose depends on how you intend the user to interact with the application, or if any values need to be passed through parameters. This post provides a summary of some common approaches to calling a subroutine.

From VBA Editor

An action as easy as pressing the VBA editors green “play” button will invoke the subroutine where the text cursor sits.

VBA Toolbar.
VBA Toolbar.



This method of running subroutines is not ideal for the final product, as most user-friendly Excel applications would not expect the user to open the VBA editor to run procedures. This approach is effective for testing procedures as they are being written.

Tip: In the VBA editor, hotkey F5 will run the current subroutine.

From Macro Dialog Box

Macros can be run from the Macro dialog box accessible through the Developer tab.

The macro dialog box lists all available subroutines.
The macro dialog box lists all available subroutines.

By clicking on (1) Developer > (2) Macros, the Macro dialog box will appear showing a list of all available subroutines. (3) Select the subroutine to run, then (4) press the Run button.

While this method is more user friendly than the VBA editor “play button” method, it still is not ideal as the user will need to know that a specific macro needs to be run. Likewise, if there are two or more macros in a workbook, it may not be obvious as to which one should be run.

Tip: Clicking on the Options button will open another dialog box which allows you to assign a hotkey to a specific macro. The Ctrl key must be used in combination with the hotkey. Make sure not to use a hotkey with a common use (such as Ctrl + c), as the macro hotkey will over-ride its native functionality. In this example, Ctrl + c will no longer be the “Copy” hotkey in that workbook.

From Form Control

A common method of activating a subroutine is through a Form Control. This is an easy implementation which requires the addition of a Form Control, along with an assignment of that Form Control to a Macro.

Add a form control to spreadsheet.

To implement this approach, create a form control button by clicking on (1) Developer > (2) Insert, and (3) select the Button Form Control (make sure you select from Form Controls and NOT ActiveX Controls). Pick in area of the spreadsheet for the button to be placed and click — keep in mind that it can be moved afterwards. Upon clicking on its position (5), the Assign Macro dialog box will appear showing a list of available Macros to assign the button to. (6) Select the macro you want activated when clicking the button.

The Assign Macro dialog box presents a list of subroutines available for button assignment.
The Assign Macro dialog box presents a list of subroutines available for button assignment.

While the button will not appear immediately when its location (step 4) is clicked, it will appear after the Macro is assigned (step 6) and OK is clicked. The assigned Macro will now run whenever the button is clicked.

Assigned macros can be changed by right clicking on Form Control buttons.
Assigned macros can be changed by right clicking on Form Control buttons.

Should you need to change the macro assigned to a specific button, you can do so by (1) right clicking the button, then (2) selecting “Assign Macro…” which will then make the Assign Macro dialog box appear.

As a final measure, it’s a good practice to rename the button to something more user-friendly, such as “Submit” or “Send”. This can be done by hovering over the button text, then clicking once the text cursor appears. The button text can then be modified.


Form Control Flaws

While a Form Control button is the easiest implementation of a user-friendly Macro call, it is not without its drawbacks. Form controls have some known issues related to bugs within Excel. Most notable is an issue where Form Controls can inadvertently be resized and moved if they are saved while within a hidden range of cells. While there are ways around this, additional code is required. In short, be very careful when placing Form Control buttons in any ranges that will be hidden during worksheet save events.

From Shape

A less common implementation (yet the one preferred by the author of this post) is using a shape to call a Macro. While a shape is not a button per se, it can be made to look like a button and assigned a Macro. If the shape is made to look like a button, the user will know to click it to activate a Macro. One benefit of this approach is that shapes do not have the hidden/save event flaw that the Form Control button does. As an added benefit, you are also given more flexibility when it comes to the look for the button. However, it is important to make sure the shape looks like a button, as anything else may not be obvious to the user as clickable. Text can be added to the shape (such as “Submit” or “Send”) by clicking on the shape and typing.

Shapes can be assigned a Macro through the same process for reassigning macros on Form Controls. Simply right click the shape and assign a Macro.

From ActiveX Control

Another useful–albeit somewhat less common implementation–is through an ActiveX Button. ActiveX introduces an additional library of controls which provide for more flexibility with formatting and general features. This includes methods of easily altering properties, such as colors and size, as well as button behavior. Generally, ActiveX controls are more flexible than Form Controls.

Adding an ActiveX Control button to your sheet is nearly identical to the process of adding a Form Control button. However, assigning a subroutine to an ActiveX button is completely different. First, to add the button, click on (1) Developer > (2) Insert, and (3) select the button from the ActiveX set of controls, the form set below the Form Controls.

Make sure to select the ActiveX Control, not the Form Control.
Make sure to select the ActiveX Control, not the Form Control.

To force an ActiveX button to call a Macro, a worksheet click event (subroutine) must be created to call the main subroutine. Fortunately, this can easily be done by double-clicking on the newly created ActiveX button, after it has been placed on the spreadsheet, while Excel is in Design Mode. Remember: Excel must be in Design Mode when working with ActiveX Controls.

Design mode, in the Developer tab, is highlighted when it is active.
The Design Mode toggle button, in the Developer tab, is highlighted when it is active.
Double-clicking an ActiveX Control will auto-generated a worksheet click event.
Double-clicking an ActiveX Control will auto-generated a worksheet click event.

After double clicking the ActiveX button, the VBA editor will automatically appear with the click event subroutine auto-generated. Upon close inspection of the Project window (left pane), you will notice a slight highlight around the “Sheet1” object. This is because the Sheet1 object–not a specific module–is open. ActiveX Control events are assigned to worksheet objects, not modules.

It is possible to include your procedural statements within this click event subroutine, but a more effective approach is to place a call to a subroutine located within a module. This is discussed in detail in the next section.

As a final cleanup measure, it’s a good idea to modify the button caption, as it defaults to CommandButton1. This can be done by (while still in Design Mode) right clicking on the button, clicking properties, then modifying the Caption within the Properties dialog box. Make sure not to modify the (Name) field as this may inadvertently impact the button’s click event. Once complete, you can turn Design Mode off through its toggle button and test the button.

The ActiveX Button's Properties dialog box.
The ActiveX Button’s Properties dialog box.
ActiveX Control Flaws

ActiveX Controls are susceptible to many flaws common in their Form Control counterpart. Specifically for ActiveX controls, they will not work for some older versions of Excel (pre-2003 or pre-1997), and they come with many of the hidden/save/sizing issues that are also present in Form Controls.

From Within Scripts

Given that the purpose of a subroutine is to contain a set of statements to perform an operations, more complicated programs containing multiple operations may require subroutines to be called within a subroutine.

The following is an example of a simple subroutine that takes data from two Excel cells and inputs it into a new row. There’s nothing particularly complex about this operation, and it can be considered a single procedure if the action were to be categorized.

However, let’s say that you also want to calculate the age of a person and include that in a third column. While this could easily be included within the main subroutine, it may be worthwhile to include it in a different subroutine. Some reasons of doing so may be as follows:

  • The operation is not fully related to the operation to add the data to the spreadsheet.
  • Including the calculation on a separate subroutine will provide for easy scalibility if the script needs to be expanded and built upon at some point.
  • The separated subroutine can be called within other operations without invoking the script to add a new line of data.
  • Separating out and compartmentalizing scripts in separate subroutines improves readability.
  • Makes it easy to comment out (deactivate) large swaths of code when debugging.

The following script calls a 2nd subroutine (within the main subroutine) to present the user with the age of the person entered.

Line 7 initiates the call to the Calculate_Person_Age subroutine (callee). Simply adding “Call” before the subroutine name will cause it to be invoked within the Add_Person_Birth_Date subroutine (caller). The Calculate_Person_Age subroutine is another basic subroutine which may be hosted in the same module, or in a different module within the project. Its code is as follows:

Using Parameters

When calling a subroutine within an existing script, parameters can be used to pass values from the caller subroutine down to the callee subroutine. This requires two additional steps:

  1. Parameters must be defined within the subroutine declaration.
  2. Values must be passed through the subroutine call statement as arguments.

Passing variables

In the following subroutine, the previously used Add_Person_Birth_Date subroutine (caller) is used again with one minor difference. In the previous example, the age calculation subroutine referred back to the spreadsheet to calculate the person’s age (see line 6 of subroutine Calculate_Person_Age). In this example, that particular line has been removed out, and the birth date is now passed through the call statement as an argument.

First, the Add_Person_Birth_Date_Param subroutine (caller):

There are three main differences between this subroutine and the main subroutine in the previous Add_Person_Birth_Date example. First, variable transferDate has been declared as a Date (line 3). Second, Date variable transferDate is assigned the value typed into cell B2 (line 8). Finally, variable transferDate is included as an argument (within the parenthesis) of the subroutine Call statement for Calculate_Person_Age_Param (line 10). This will pass the value of Date variable transferDate to the Calculate_Person_Age_Param subroutine (callee).

However, there are a few changes required within the called subroutine Calulate_Person_Age_Param for this to work:

The most obvious change from the previous Calculate_Person_Age example is that variable declaration birthDate has been moved between the brackets of the Subroutine statement. This is known as a parameter tells the subroutine to expect a value of a Date data type to be passed through as an argument to the function call. Given that the value of birthDate will be assigned immediately once the subroutine is called, we can omit the line assigning value to the variable birthDate.

ByRef Vs. ByVal

Subroutine parameters can be declared as ByRef (by reference) or ByVal (by value). While including this with the parameter is optional, the default behavior will be ByRef if none is specified.

An argument passed as ByRef will refer back to (point back to) the original variable (reference) in the calling statement. In contrast, an argument passed as ByVal will no longer refer back to the variable in the calling statement and be treated as a separate entity.

This is confusing. Here’s an example to articulate this concept. Imagine if you have a two variables, each with a value of 10. In the example below, integer variables refFactor and valFactor have both been assigned 10. Each variable will then be passed to a subroutine to be multiplied by 2. After passing through subroutines, the main (callee) subroutine will display the values in a message box.

  • Variable refFactor will be passed through subroutine Mult_Ref, where the argument is defined as ByRef.
  • Variable valFactor will be passed through subroutine Mult_Val, where the argument is defined as ByVal.

Although the called subroutines are two different Subs, they both contain the same procedures and simply multiply the passed value by 2.

After the two subroutines are called from the caller subroutine, the caller subroutines continues by displaying both the refFactor and valFactor variables on two separate message boxes. Variable refFactor, which was passed byRef through the Mult_Ref subroutine will return 20. However, variable valFactor, which was passed ByVal through the Mult_Val subroutine will return its original assigned value of 10, as if nothing happened to it. The following are the three subroutines:

To summarize: even though both variables went through nearly the same script, two different values are returned in the message box.

The variable refFactor is passed to the called Subroutine Mult_Ref as ByRef–its full variable–taking its location in memory, data type, and value with it. Any changes that occur to it within the called subroutine will stay with it, even when it is called again within the main subroutine. Meanwhile, the variable valFactor is passed to the called Subroutine Mult_Val as ByVal–simply its value. Upon entering the called subroutine, a new variable is created in memory (seperate from valFactor), and any impact the Mult_Val subroutine will not impact the valFactor variable in the main subroutine.

Additional Note on ByVal:

While ByVal is useful for preserving values of variables within caller subroutines, there is one more feature available by using ByVal. One limitation (for good reason) with ByRef is that the data type of the argument passed from a caller subroutine through a callee subroutine parameter must match the value assigned to that parameter. With that, for a ByRef parameter declared as an integer, if a variable with a data type of Double is passed as an argument, a “ByRef argument type mismatch” error will occur.

For Example, the following script will not work:

This would return the following error:

VBA error ByRef argument type mismatch.

However, when identifying a parameter as ByVal, the called subroutine only cares about the variables value, not its location in memory or data type. Given this, you would be able to pass a variable of a Double data type as an argument, even if the callee subroutine’s parameter is declared as an Integer. However, if it is a floating point number, the called subroutine will convert the argument to an integer, simply dropping the digits beyond the decimal point.

Simply changing the ByRef to ByVal within the Integer_Sub subroutine will make the script work:

This approach is useful with numeric data types and other special data types, such as date values.

Other Notes:
  • Attempting to pass a numeric value outside the constraints of the called subroutine’s parameter data type (such as a number beyond 32,767 for an Integer) will trigger an overflow error.
  • Passing a numeric string value–numbers only–into a numeric data type will work as long as it’s within that data type’s constraints. However, passing any string alpha characters to a numeric data type parameter will return an error.
  • An alternate means of avoiding data type errors with ByRef parameters is to declare the parameter as a Variant. The Variant data type will accept any data type passed through the called argument.

Passing arrays

Arrays can be used as arguments when calling a subroutine. Within the call statement argument, simply include the array name without any brackets or subscript. The entire array will be passed to the called subroutine.

Within the subroutine declaration, include the array as a Parameter as you normally would, but include open and closed brackets immediately after the array name. This tells the subroutine to expect an array to be passed. The following Array_Test  subroutine (caller) builds an array containing 11 values, the numbers 0 to 100 in increments of 10. A second Display_Array subroutine (callee) is called which prints the array in a message box with each value separated with a vertical bar.

Note that array size and dimensions do not need to be specified with the subroutine declaration parameter. It will accept any size as long as the data type matches.

Passing Objects

Worksheet, workbook, and VBA objects can also be passed through subroutines. All object properties and attributes will be passed along. This is a useful technique if you need to replicate properties of a specific object, or apply a specific algorithm (such as a cleanup) to multiple objects.

In the following example, a subroutine sets a shape object to identifier submitButton, which is the submit (“Add Value”) button on the example sheet. Variables shapeWidth and shapeHeight can be modified in lines 8 and 9 of the caller subroutine to change the height and width of the button.

Also note that within the called procedure definition, intellisense will work as long as the parameter is declared as an object.

Optional Parameters

It’s also possible to include optional parameters within the subroutine declaration. Within the parameter declaration, there are two approaches to doing so:

varOne is declared as optional without a default value. If no argument is passed to the subroutine for this parameter, it will be assigned numeric value 0 (zero). If the varOne parameter were given a String data type, it would be assigned an empty (“”) value.

varTwo is declared as an optional value with a default value. By including the assignment operator after the data type in the parameter declaration, a default value of 25 will be assigned if no arguments are passed. Any passed argument will override the default value.

The following procedure takes the previous shape resize subroutine, but makes the width and height arguments optional. Parameter shapeWidth does not contain any default value, and the script will maintain its current width if no argument is passed. Parameter shapeHeight contains a default value of 25, so it will slightly increase the size of the button (from 20) if no argument is passed.

Order of Optional Parameters

Optional parameters must be the last parameters declared in a subroutine declaration. Attempting to add mandatory parameters after an optional parameter within a declaration will result in a syntax error.

When passing arguments to optional parameters, typical comma separated arguments will be appropriate when moving through all parameters from left to right. For example, the following call will pass an argument (literal value of 40) for optional parameter shapeWidth.

Given that the shapeWidth parameter is the second parameter in the declaration, no further action is needed. However, what if we actually wanted to assign 40 to ShapeHeight (the third parameter) while leaving the shapeWidth parameter alone. One extra step is required when skipping an optional parameter:

As can be seen in this example, the parameter name shapeHeight is included, along with the operator “:=” just before the value assignment. This will tell the subroutine call to ignore the optional shapeWidth parameter and skip right to shapeHeight.

Subroutine Scope

VBA does permit two scope assignment settings for subroutines. Unlike global assignments and procedural level scope for variables, which determine if variables are accessible for calls within a (1) specific subroutine, (2) a specific module, or (3) in the entire project, the subroutine’s scope settings determine whether or not it’s available within a specific module or throughout the entire project.

Omitting the specification of scope (a common practice by the author of this post) will yield a default scope of public. This makes the subroutine accessible for calls throughout the entire VBA project.

The following is a simple example of each subroutine scope assignment.

It’s important to note that subroutines given a Private scope will not be accessible to developers (or users) through the Developer tab’s Macros dialog box and will not be available for assignment to form controls or shape. Private subroutines can only be accessed with a call through a caller subroutine. Given that, it is possible to access a Private subroutine through an ActiveX control click event.

Other Notes

  • Subroutines cannot be assigned identical names within the same module. This will lead to an ambiguous name error. Subroutine names can be duplicated if they are within different modules, although this may lead to some issues, both in readability and ambiguity issues.
    • Identical named subroutines between modules will contain the module name followed by the dot operator and subroutine name.
    • Identically named subroutines in different modules with Private scopes will not cause any ambiguity conflicts.
    • Subroutines specified as Public (or their default scope) can exist in different modules with some caveats related to subroutine calls. If the caller subroutine exists in Module1, and the two callee subroutines of the same name exist in Module2 and Module 3, an ambiguity error will occur. However, if the caller subroutine exists in Module1, and one copy of the callee subroutine exists in Module1 as well (while the second copy is in Module2), the caller subroutine will by default call the copy within the same module (Module1).
  • All ActiveX Control types (radio buttons, checkboxes, listboxes, etc) can be assigned a click event by double clicking. This can be very useful in advanced applications.

Leave a Reply