Variables in VBA: An Overview

While the series has so far focused on VBA’s interaction with spreadsheets, there are endless opportunities for dynamic features and calculations which can occur off the spreadsheet.

Overview

This post within the Getting Started with VBA series begins the focus on Variables in VBA.

A variable is a component of a programming script that stores a value. Just to name a few possibilities, the stored value can be used to test conditions, take part in mathematical operations, or manage loops. Even more valuable is the fact that variables can change as a script is executed. This becomes important when working with scripting elements such as loops.

Declaring Variables

While not required in VBA, it’s a good practice to declare variables. First, this can help better manage memory usage and ensure that your script runs efficiently. Second, it can help with script debugging and ensure that no stray values are assigned to variables.

By declaring a variable, you specify a data type which will allocate a certain amount of memory to that variable. Each data type has certain limitations in range and length. The stricter the limitation, the smaller the variable size in bytes. Generally, it’s a good practice to keep variable size as small as possible under the circumstances. This will limit the memory usage in the stack and ensure that your program runs efficiently.

The most basic way of declaring a variable is through a Dim (dimension) statement, as follows:

Variable quantity was declared as an Integer data type. Excel comes loaded with 11 different data types, as follows.

Name Value Suggested Use Size
Byte 0 to 255 Use only when referring to the ASCII table or in an array of bytes. 1 byte
Boolean True or False Useful for variables that provide results to conditional statements. 2 bytes
Integer -32,768 to 32,767 Situations where certainty exists where whole numbers will be constrained to the ~-32K to ~+32K range. 2 bytes
Long -2,147,483,648 to 2,147,483,647 Best use for large whole numbers, specifically for iterating through spreadsheets with lots of data 4 bytes
Currency -922,337,203,685,477.5808 to 922,337,203,685,477.5807 Useful for defining currency values as decimal point is fixed. 8 bytes
Single -3.4e38 to +3.4e38 Floating point decimal with up to 39 positions. 4 bytes
Double -1.8e308 to +1.8e308 Floating point decimal with up to 308 positions. Try to avoid the use of this when long or single data types may be more suitable. 8 Bytes
Date Stores dates and times as a real number Useful for storing dates. 8 bytes
String Letters Use for storing arrays of characters. Varies, but generally 2 bytes per character
Object Refers to an object Can be used to specify shapes or controls as objects for later reference. 4 bytes
Variant Can contain any combination or letters, numbers, or a null value Try to avoid. 8 bytes or more

As you can see, each data type has a specific limitation, along with byte usage. As previously stated, it’s good to try and get the data type size as small as possible. In the example above where quantity was declared, I could have used any of the following data types: Integer, Long, Double, or Variant. I choose to use Integer as it’s unlikely that anyone would order more than 32,767 (let’s say, I’m selling arts and crafts online), and it only uses 2 bytes. That means the variable quantity will use less space on stack, than if it were declared a Long, Double, or Variant.

Variable types do not result in any noticeable difference in speed, but it is possible to use all the space in the stack (returning a Stack Overflow debug error) or to compromise the overall performance and stability of your program. Use your best judgement when assigning a data type and understand the limitations of the variables you’re working with. Keep in mind that properly managing data types becomes important with very large arrays.

A Note About Variant

The variant data type is the most flexible data type, with the ability to contain any number or string. In fact, if a variable is not declared in a script, VBA automatically assigns it the variant data type. It’s worth noting, however, the a variant takes up more memory than any of the other data types (excluding long strings) and will take longer to process. With that, if you are certain that a variables values will fit within a certain numeric range, pick a more specific data type.

Initializing Variables

The second step in managing a variable is initialization.  This is a fairly easy step where you assign a value to a variable through the assignment (=) operator. In the following example, the variable season is initialized and assigned the string “Fall”.

Now that the variable season has been initiated with the string “Fall” in memory, and call made to the season variable later in the script will return “Fall”. The following code will assign the season variable to cell A3—through the range object and value property—within the active worksheet.

You can also use a range object to assign a value to a variable, as such:

A variable can be assigned the value of another variable:

You may have noticed quotation marks around the variable values above. Quotation marks are only required for text values, referred to as strings in VBA. Integers, other variables, and objects do not require quotation marks.

Variables can be initialized through mathematical operations.

Variable Scope

Procedure (Local)

Local scope permits variables to work within an individual procedure (subroutine or function) with no impact to procedures outside of the routine. These variables are eliminated once the procedure is complete and the memory allocated to those variables is released.

Local variables are specified with a Dim statement and must follow immediately after the Sub or Function definition.

The variable is released from memory at the End Sub statement.

Static

Static variables remain in memory within their base procedure for as long as Visual Basic is running, or if a subroutine is called from a different module. At that point, the variable will be eliminated and the allocated memory will be released. This approach is useful when you want to maintain the value of a variable—say, keep it constant for multiple runs or change previous value with mathematical operations—within the same procedure.

In this example, the subroutine remains in existence after the End Sub statement. If the same static variable with the same name is called in a separate procedure, another instance of the variable will be created with its own count value.

Public

A public variable stays in memory until VBA is stopped or Excel is closed. A public variable can be recalled from memory in any procedure within a workbook, regardless of which module it exists in.

In the above module, variable quantity is declared as a public variable above the subroutine. Now insert the following code into a different module:

As long as the first module is run first, the variable quantity will be initiated, and will work in this module even though it is not declared anywhere within. When Public_Test_1 is initiated, the quantity variable will store a value of 5 will print the value 5 in cell A1. When Public_Test_2 is initiated, the subroutine will refer to the stored value in the Public quantity variable (5) and add 5 to that, changing the value of quantity to 10, and printing 10 in cell A1.

Note that a public variable must be declared at the top of a module, before any subroutines.

Private

A private variable can be accessed by all subroutines and functions within a specific module. A subroutine or function in a module where the Private variable is not declared will not work.

Public_Test_2 is declared in a different module.

In the above example, when Public_Test_1 is initiated, the quantity variable will store a value of 5 will print the value 5 in cell A1. When Public_Test_2 is initiated, the subroutine will not be able to call the quantity variable from memory as it is declared private in a different module. Given that the quantity variable is not declared in Public_Test_2, VBA will declare it as a variant and still print it on the spreadsheet.

Option Explicit

A good practice is to begin each of your VBA modules with the Option Explicit statement. While it is not required, it will force you to properly declare all module variables for a script to run. As previously stated, declaring variables ensures that VBA scripts run efficiently and are easy to manage.

The following is an example of a module with an Option Explicit statement. The statement goes at the top of the module, prior to any subroutines or public/private variable declarations.

When an Option Explicit statement is provided and a variable is not declared in one or more of the modules subroutines or functions, the following error is shown upon script initiation.

2016-11-19-1
A compile error appears if a variable is not declared with Option Explicit is on.

Good Practices

While variable names can vary in general format and length, there are some generally agreed upon practices to make code easy to read, ensuring that a coding team will work. The following are a few suggestions:

  • Keep variable names succinct, but clear. Include a few works to make it clear to yourself later on (and any other team members) what a variable is meant to do. Variable names like populationCount or itemName provide simplistic clarity. Variable names such as usPopulationDensityThisYear are long and lack clarity. Keep in mind that some large projects may utilize hundreds of variables.
  • It’s generally a good idea to keep a consistent case format for all variables. This will make it clear to you, and other coders that the item is a variable. The most commonly agreed upon approach is a format known as camel-back. This involves beginning a variable in lowercase, then making the first letter of each following word uppercase. For example, populationCount or itemName
  • Loop counters can be a single letter, typically starting with i, then j, then k, and so on. Skip over letters “o” (to avoid confusion with zero) and “l” (to avoid confusion with one). It’s generally a good practice to begin each loop with a new variable (even if they’re not nested), unless the loop relies on the continuation of a prior loop.

This is a quick summary of suggestions that will be specifically valuable in VBA. While VBA allows flexibility and forgiveness many traditional programming languages would not allow, following standard programming practices will ensure that you provide yourself and others with good reminders on the purpose of specific variable. Additional good practices–not specific to VBA, but still relevant– can be found in this post at the CodeBuilt blog.

Other Resources

Leave a Reply