VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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
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

Enums can also be used in ‘For Each’ loops to iterate through their values. The following example demonstrates how to loop through the WeekDays enum and display each day in a message box.

Sub DisplayDays()
    Dim Day As Variant
    'Loop through all the values in the WeekDays enum
    For Each Day In WeekDays
        MsgBox Day
    Next Day
End Sub

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
    '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.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA StatementsTags: , Last Updated: September 28, 2023

Leave A Comment