Creating and Using Enumerations in VBA

Enumerations are data structures useful for grouping a elements which can be (or need to be) be associated with numeric values. Examples of useful enumerations are products with product IDs, or working with date values, such as months 1 through 12. Many developers also use enumerations to manage events, such as status message selection or triggering actions based on user inputs.

Overview

An Enumeration (sometimes referred to as an enum or enumerated type) is a group of elements associated with numeric values. Their behavior is somewhat similar to constants, whereas they must be declared in the declaration area of a module and their value cannot change. Unlike constants, however, enumerations will always represent the long data type and can be included in groups, typically having a similar theme.

An effective way to explain enumerations is through the list of months. Each month of the year has a name, but in many date formats, months are commonly referred to by their number. January is considered the first month, and can subsequently be referred to as 1. February is 2, March is 3, and so on. In a VBA script, calling the enumeration element March will return long value 3.

Sample File

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

Creating Enumerations

Like constants, enumerations must be declared within a module in the declaration area — at the top before any subroutines or functions.

General Syntax

Going along with the month number example, the general syntax for declaring a enumeration is as follows:

The above approach–although the most basic and easiest way to declare–has a serious flaw in the context of our month number example: enumerations which do not explicitly assign numbers to elements will start with zero. With that, January will be assigned 0, February 1, March 2, and so on. While this is not a fatal issue and can be managed with proper coding, the number assignments are non-standard and non-intuitive.

One way to resolve this is by assigning custom numbers to the enumeration.

Custom Numbers

Custom long values can easily be assigned to enumerations. The following example resolves the issue found in the first example, assigning 1 to the first identifier, January. Each subsequent month is also identified

Customized enumerations do not need to be in sequential order. The following enumeration lists the final month of each quarter:

Scope

Also similar to constants, scope can be specified in the declaration. If no scope is specified, a default scope of Private is assigned.

Clearly specify a private enumeration:

This enumeration will only be accessible within the module where it is declared. To use an enumeration outside of its post module, it must be declared as public, such as follows:

Similar to all other public data structures, a subroutine or function within its host module must run before the enumeration is stored in memory.

Using Enumerations

The basic syntax for using an enumeration is as follows:

Calling an enum value is fairly straightforward, using a period (known as the dot operator) between the enum group name and enum element. This snippet–assuming it’s using the custom numbered enumeration declaration–will print the number 2 in cell A1.

A helpful feature with enumerations is their incorporation of the VBA editor’s intelisense. By declaring an enumeration, all elements will be presented in a floating listbox, immediately after the developer types the dot operator.

Intellisense activated with declared enumerations.
Intellisense activated with declared enumerations.

Conditional Statements

A practical use of enumerations is within if-then-else statements.

Switch Statements

In the month/number example, a select case statement is a slightly cleaner alternative.

For Loops

For loops can be leveraged with enumerations to either find values, or to check if a specific value exists. The following will check to see if a month value exists, then will execute a switch statement (similar to the previous example) if the user inputs a correct value. One important caveat is that the minimum and maximum number assignments must be specified within the enumeration declaration. This can be done with the element [_First] and [_Last]. While VBA will treat these elements as part of the enumeration (they will be included as part of the sequential order if the enumeration is not manually assigned numbers), they can be used as reference points within For loops. Additionally, the brackets and underscore tell the VBA editor not to include the elements in the intellisense list.

One flaw in this approach is that the long values of each enumeration must be in sequential order. If we were to use the “Enum_Quarter_Month” example from above, the for loop would still iterate 12 times between 1 and 12, even though only 4 values exist in that specific enumeration. Ideally, a For Each loop would be a great alternative, but For Each loops are not compatible with enumerations.

Other Notes

  • It is possible to explicitly assign a number to only the first element in an enumeration. Each subsequent element will increase in sequential order. While this may be seen as a shortcut, it’s a good idea to assign numbers to each element in short lists for the sake of readability. This technique may be useful for very long lists.
  • Enumerations can be assigned negative long values.
  • It’s generally a good idea to utilize a unique naming convention for enumeration data types. In the examples above, each word within the enumeration identifier is capitalized and separated by an underscore.
  • Reference points (e.g. [_First] and [_Last]) can have any name, provided it includes the square brackets and underscore.

Leave a Reply