Creating and Using Constants in VBA

A constant is a data structure used to store a specific static value that must be retained through the entire execution of a script. While constants may seem somewhat restrictive, they provide an advantage with memory usage and script readability.

Overview

This post within the Getting Started with VBA series continues the focus on Other VBA Data Structures.

A constant can be thought of as similar to a variable, with one key difference: while a variable’s value can change during the execution of a script, a constant’s value cannot. Constants are useful in situations where neither preceding code, nor user inputs impact the value of a variable.

Intrinsic Constants

Excel comes with many built-in constants that specify many default components of the Excel application — built-in constants are commonly referred to as intrinsic constants. Intrinsic constants typically include default values, such as column widths, or attributes of a border when using Excel’s quick border formatting tool.

There are hundreds of Excel application-level constants available. The Office Dev Center provides a comprehensive list — this list contains constants associated specifically with the Excel application (which can also be used in VBA). VBA also contains a number of VBA-specific constants. The Office Dev Center does not provide a VBA specific list of constants (although many found in VBA’s successor, VB.net are valid), however, the full list can be found within libraries in the object browser.

The Object Browser will display all "vb" intrinsic libraries and their constants.
The Object Browser will display all “vb” intrinsic libraries and their constants.

Common Intrinsic Constants

Using intrinsic constants whenever possible is generally a good practice. They provide pre-loaded values which do not need to be assigned to a variable or written as string/numeric literals. The following is an example of a simple message box prompt with a “yes or no” question:

Depending on the user selection, a message will print in range A1. This script contains three constants:

  1. vbYesNo tells the MsgBox function to provide “Yes” and “No” buttons. It’s actual value is integer 4, but its value has no use in this context.
  2. vbYes tests if “Yes” was selected in the prompt. The actual value is integer 6.
  3. vbNo tests if “No” was selected in the prompt. The actual value is integer 5.

While the MsgBox() function can stand alone without any variable assignment (it will simply display an “OK” button), this example assigns the MsgBox() function return to integer variable prompt, as the vbYesNo Intrinsic Constant tells it to return a value once the user makes a selection. If the user selects “Yes”, 6 is returned, while 5 is returned if “No” is selected. These values are then tested against the specific constants within two conditional statements.

Note that if a function parameter is expecting an intrinsic constant, the VBA editor’s intellisense will provide a list of available values after the typing parameter’s separating comma. It may be worthwhile to test many options and values to see what best suits your program’s needs.

List of available constants with the VBA editors intellisense.
List of available constants with the VBA editors intellisense.

User Defined Constants

Developers have the ability to create user-defined constants in the declaration area of a VBA module. Like their variable counterparts, constants can be declared as both private or public. Unlike variables, however, the constant value must be assigned within the declaration.

A basic syntax is as follows:

Constants must be declared above all “Sub” statements, in the declaration area of the module. The default scope of a constant will be private (within the entire module).

One important thing to note is that a constant can never contain a dynamic or user-inputted value. A constant effectively behaves as if it is a string literal within your script. Given this, you need to be cognizant of the future viability of every constant you define. For example, many tutorials offer sales tax as an example of an appropriate constant. However, sales tax can change. While this is infrequent, if a your state government decides to raise sales tax next year, would you be able and willing to update all copies of your spreadsheet application?

Given the limited functionality with regards to updates, it’s important to evaluate any circumstance when a constant may change. Given this, constant’s are extremely useful for supporting components of your VBA script, such as the name of a worksheet that is frequently referred to (assuming worksheet names never change), or the distance you want a shape to move.

Declaring Scope of User Defined Constants

Constants can be either public or private. Private constants will only be usable within the module they are declared, while public constants can be used in any module. By default, a declared constant is given a private scope if scope is not specified.

Similar to the behavior of variables, a public constant must first be initiated (within the module that hosts it) prior to use within another module. Running any script within the host module will initiate and store a public constant to memory.

Other Notes

  • When using a private constant within a function, that function must exist within the module which it is declared. Alternatively, a constant value–whether private or public–can be passed through a function parameter, allowing the function to exist on any module.
  • A constant beginning with “vb” is specific to VBA, while a constant beginning with “xl” is typically a default value or attribute of the Excel application.
  • While not required, it may be worthwhile to use differing naming conventions for your variable and constant names. This helps improve readability. On this site, variables are named with camelCase while constants are named in UPPER_CASE.

Leave a Reply