VBA Enum Statement

The VBA Enum statement is a widely-used feature in the Excel VBA programming language that allows for the creation of custom enumerations. Enums are a set of related constants that assign names to integral values, making it easier to refer to these values in code. The term enum is an abbreviation for “enumeration,” and it is a data type that is standard in many programming languages, including VBA.
Enums in VBA are used to create constants for specific values instead of using magic numbers (i.e., random numeric values) throughout the code. This makes the code more readable, easier to maintain, and helps to avoid errors. The Enum statement is a powerful tool that helps VBA programmers to define their own sets of constants, which can be used throughout their macros.

In this blog post, we will explore the purpose, syntax,  examples, important notes, and remarks of the VBA Enum statement.

VBA Enum Statement

The Purpose of the VBA Enum Statement

The main purpose of the VBA Enum statement is to allow programmers to create custom sets of constants that can be used in place of hard coded values in their code. This makes the code more organized and easily readable. Enums are especially useful when working with large amounts of data or when handling a large number of variables in a macro. They also provide an efficient way to store and retrieve values in a macro, improving the overall performance of the code.

The Syntax of the VBA Enum Statement

The syntax for the VBA Enum statement is relatively simple and follows a specific structure. It is written as follows:

Enum [scope] enumname
    [enumeration_list]
End Enum

The ‘scope’ refers to the accessibility of the enumeration, and it can take on the following values:

• Private : The enumeration is only accessible within the module in which it is defined.
• Public : The enumeration is accessible to all modules in the current project.
• Global : The enumeration is accessible to all modules in all open projects.

The ‘enumname’ is the name given to the enumeration, and it must follow VBA variable naming conventions (i.e., it cannot start with a number and cannot contain spaces or special characters).
The ‘enumeration_list’ is a list of enumeration constants and their associated values, written as follows:

[constant_name] [= constant_value], [constant_name] [= constant_value], ...

The constant name must follow VBA variable naming conventions, while the constant value must be an integer value (positive or negative). The values can also be defined as other enums or constants.

Examples of Using the VBA Enum Statement

Here are five examples that demonstrate the use of the VBA Enum statement in Excel macros:

Example 1: Creating a Days of the Week Enum

This example creates an enum for the days of the week, assigning each day a numeric value. This enum can then be used to refer to specific days in a macro.

Enum WeekDays
    Monday = 1
    Tuesday = 2
    Wednesday = 3
    Thursday = 4
    Friday = 5
    Saturday = 6
    Sunday = 7
End Enum

Example 2: Using an Enum in a Case Statement

Enums are commonly used in conjunction with a ‘Select Case’ statement to check and handle different values in a variable. For instance, in the following example, we use the WeekDays enum from the previous example to display a different message based on the day of the week.

Sub DisplayMessage()
    Dim Day As WeekDays
    'Set Day to be Monday
    Day = WeekDays.Monday
    'Check the value of Day using a Select Case statement
    Select Case Day
        Case WeekDays.Monday
            MsgBox "Today is Monday."
        Case WeekDays.Tuesday
            MsgBox "Today is Tuesday."
        Case WeekDays.Wednesday
            MsgBox "Today is Wednesday."
        Case WeekDays.Thursday
            MsgBox "Today is Thursday."
        Case WeekDays.Friday
            MsgBox "Today is Friday."
        Case WeekDays.Saturday
            MsgBox "Today is Saturday."
        Case WeekDays.Sunday
            MsgBox "Today is Sunday."
    End Select
End Sub

Example 3: Using Enums to Define Cell Colors

Enums can be used to define and store cell colors, making it easier to reference them in macros. In the following example, we create a ColorEnum enum and assign each cell color value to one of the enum’s constants.

Enum ColorEnum
    Red = 3
    Green = 4
    Blue = 5
    Yellow = 6
End Enum

Example 4: Looping Through Enum Values

While an Enum is a data type and not a collection, you can’t use a ‘For Each’ loop directly on it. However, you can easily iterate through all its members using a standard For...Next loop by cycling from the first member’s value to the last.

First, let’s define the WeekDays enum we’ll be using:

Enum WeekDays
    wdMonday = 1
    wdTuesday
    wdWednesday
    wdThursday
    wdFriday
    wdSaturday
    wdSunday
End Enum

Sub DisplayDays()
    ' Use a numeric type like Long, as Enums are internally Long integers.
    Dim DayValue As Long
    
    ' Loop from the first defined value to the last defined value.
    For DayValue = WeekDays.Monday To WeekDays.Sunday
        ' This will display the numeric value (1, 2, 3, etc.)
        MsgBox DayValue
    Next DayValue
End Sub

The following example demonstrates how to loop through the WeekDays enum and display the name of each day in a message box.

Sub DisplayDayNames()
    ' Use a Long, as Enums are internally stored as Long integers.
    Dim DayValue As Long
    Dim DayName As String
    
    ' Loop from the first defined member (wdMonday) to the last (wdSunday).
    For DayValue = wdMonday To wdSunday
    
        ' In VBA, you must manually convert the numeric value back to a name.
        ' A Select Case block is a great way to do this.
        Select Case DayValue
            Case wdMonday: DayName = "Monday"
            Case wdTuesday: DayName = "Tuesday"
            Case wdWednesday: DayName = "Wednesday"
            Case wdThursday: DayName = "Thursday"
            Case wdFriday: DayName = "Friday"
            Case wdSaturday: DayName = "Saturday"
            Case wdSunday: DayName = "Sunday"
        End Select
        
        MsgBox DayName
    Next DayValue
End Sub

This code correctly iterates from the numeric value of wdMonday (1) to wdSunday (7). Inside the loop, the Select Case statement checks the current value and assigns the corresponding day’s name to the DayName variable, which is then displayed in the message box.

Example 5: Using Enums to Define Custom Error Codes

Enums can be used to create custom error codes in macros, making it easier to identify and handle different types of errors. This is especially useful when working with multiple macros in a project. In the following example, we create an error enum and use it in an error handler to display a custom error message.

Enum ErrorEnum
    DataNotFound = 1001
    DataInvalid = 1002
    UpdateFailed = 1003
    '...
End Enum
Sub UpdateData()
    On Error GoTo ErrorHandler
    'Code for updating data
    Exit Sub
ErrorHandler:
    'Display custom error message based on error code
    Select Case Err.Number
        Case ErrorEnum.DataNotFound
            MsgBox "Data not found. Please check your input."
        Case ErrorEnum.DataInvalid
            MsgBox "Data is invalid. Please check your input."
        Case ErrorEnum.UpdateFailed
            MsgBox "Update failed. Please try again later."
    End Select
End Sub

Important Notes and Remarks

There are a few important things to keep in mind when using the VBA Enum statement in your macros:

• Enum names must be unique and cannot be redefined within the same scope.
• Enum names cannot be used as variables, objects, or procedure names.
• An enum name can be used as an argument for procedure calls, including other enum names.
• Enum constants can be used in expressions, except when using some mathematical operators (e.g. *, /, %).
• It is considered good programming practice to use the ‘Option Explicit’ statement, which requires the explicit declaration of all variables, including enums.

In conclusion, the VBA Enum statement is a powerful tool that helps to create and organize custom constants in Excel macros. It improves the readability and maintainability of code and provides an efficient way to store and retrieve values. In this blog post, we have explored the purpose, syntax, top 5 examples, and important notes of the Enum statement.

Have you used enums in your VBA macros before? Share your experiences and thoughts in the comments below. Your feedback is valuable and helps us to improve our content.