Enum in VBA: What It Is and How to Use It with Examples

VBA & macros · Updated November 22, 2026

In VBA, Enum is a standard language keyword for declaring an enumeration: a named set of related constants, each associated with a numeric value. You can then declare a variable using the enumeration’s name and refer to meaningful labels such as StatusPaid instead of scattering unexplained numbers like 1 through your code. An enum does not store text labels or create a dropdown in an Excel worksheet; it organizes values in VBA code.

What “Enum” means in VBA

Enum is neither slang nor an informal VBA term. It is a reserved keyword in Visual Basic for Applications, and it is the shortened form of “enumeration.” The English word comes through French from Latin enumerare, meaning “to count out” or “list.” In programming, an enumeration is a type whose named members stand for a set of related values.

In a sentence, a developer might say, “I used an Enum for the order statuses so the procedure uses StatusPaid instead of the number 1.” That is a normal technical use of the word. “Enum” is also commonly used as a noun for the declaration or the resulting group, although the full term is “enumeration.”

How an enumeration works

An enum declaration gives a group a type name and gives its members constant names. VBA associates the members with integral numeric values. If you leave the first member unassigned, it is 0; each following unassigned member is one greater than the previous member. You can assign values explicitly when the numbers have a defined meaning or must match existing data.

Here is a complete example for a standard VBA module:

Public Enum OrderStatus
    StatusPending = 0
    StatusPaid = 1
    StatusShipped = 2
    StatusCancelled = 3
End Enum

Public Sub ReportOrderStatus()
    Dim currentStatus As OrderStatus

    ' Use a descriptive member, not a bare number.
    currentStatus = StatusPaid

    Select Case currentStatus
        Case StatusPending
            MsgBox "The order is pending."
        Case StatusPaid
            MsgBox "The order has been paid."
        Case StatusShipped
            MsgBox "The order has shipped."
        Case StatusCancelled
            MsgBox "The order was cancelled."
    End Select
End Sub

The declaration defines the type and its members; the procedure declares a variable of that type and assigns one of the members. The Select Case statement then makes the code easier to read. Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. This example displays a message and does not change worksheet data.

Where to declare an Enum and how to scope it

An Enum declaration belongs in a module’s declaration section, above its procedures. It cannot be declared inside a Sub or Function. In the Visual Basic Editor, open a standard module or create one with Insert > Module, then place the declaration at the top of that module.

Use Public Enum when code elsewhere in the VBA project needs to refer to the type and its members. Use Private Enum when the declaration should be limited to its module. If you omit the access keyword, the declaration is private by default. A public enum intended for broad project use is commonly placed in a standard module. Scope determines where code can name it; it does not affect the numeric values.

Member names need to be distinct enough to avoid confusion or collisions with other identifiers. Prefixing members with a short form of the enum name, as in StatusPaid, can make their purpose clear and reduce naming conflicts in a large project.

When to use Enum instead of numbers or Const

Enums are useful when a variable should represent one choice from a small, known set. Examples include a processing state, a direction, a report format, or a mode passed to a procedure. The type name communicates what kind of choice is expected, and the named members explain the code’s intent.

For a single fixed value, a Const declaration is usually simpler. For example, a constant holding a worksheet name does not describe a choice among related alternatives. An enum is a better fit when several named options belong together and are used as values of the same conceptual type. In Excel VBA, many built-in constants are also enumeration members supplied by the Excel or Office object libraries, which is why names such as xlUp and vbYesNo are more informative than their underlying numbers.

  • Use an enum for a finite group of related options used in VBA logic.
  • Use a constant for one standalone fixed value.
  • Use worksheet data validation or a form control when the goal is to let a workbook user choose an item in the interface.

Choosing member values and naming them

For purely internal choices, the default sequence beginning at zero is often adequate. Assign explicit values when the numeric representation is part of an interface—for example, when VBA reads or writes codes in an existing file, calls an API, or exchanges values with another program. In those cases, check the relevant specification rather than assuming that the first option should be zero or one.

Choose names that identify the meaning of each member, not just its position. ReportSummary and ReportDetailed explain intent better than Report1 and Report2. Avoid relying on the implicit numbering if inserting a new member in the middle would change the values of later members that are stored or exchanged externally. Explicit assignments can preserve those values.

An enum’s members represent numeric constants, not strings. If a worksheet cell contains the text “Paid,” VBA will not automatically turn that text into the member StatusPaid. Code must map the text to a member, or the program can use a different representation such as strings. Likewise, a worksheet dropdown is a separate Excel feature; declaring an enum does not create one.

Common mistakes and limits

A frequent mistake is putting the declaration inside a procedure. Move it to the module’s declaration section instead. Another is expecting member names to be quoted: write StatusPaid as a VBA identifier, not "StatusPaid", which is a string literal. Also check spelling and scope if VBA reports that a name is not defined; a private enum cannot be referenced from another module.

Enums make code more expressive, but do not treat them as a substitute for validating input. A value read from a cell, file, or external system may not correspond to any member you intended. Check external values before using them in logic, especially if they can be missing or outside the expected range. An enum also does not automatically validate user input or constrain a worksheet cell.

Finally, do not assume that enum members are text or that their numeric values are self-explanatory outside the code. If values are saved in a workbook or passed to another system, document their meaning and keep the mapping stable. That is particularly important when later edits might otherwise renumber implicit members.

Enum compared with alternatives

A set of named Const declarations can also replace magic numbers, but an enum groups the choices under a type name and makes a variable’s purpose more apparent. A string-based approach can be convenient when values must match visible text, but spelling and capitalization differences can introduce errors; strings also do not give the same enum type grouping. The right choice depends on whether the value is an internal code, user-facing text, or part of an external format.

Enums are not automatically bit flags. A flags pattern uses individual powers of two and combines them with bitwise operations such as Or; that is a deliberate convention, not behavior supplied just by writing an enum. Use such a pattern only when the code is designed to test and combine multiple options. For a choice where exactly one state should apply, ordinary sequential enum values are clearer.

Frequently Asked Questions

Is Enum a reserved word in VBA?
Yes. Enum is a standard VBA keyword used to declare an enumeration, so it should not be used as the name of a variable or procedure.

Does VBA Enum start at 0 or 1?
If you do not assign a value to the first member, it starts at 0. Each following member without an explicit assignment increments the preceding value by one. You can specify values directly when needed.

Can I declare an Enum inside a Sub?
No. Put the declaration in the module’s declaration section, outside all procedures. Then declare variables of that enum type inside a procedure as needed.

Does an Enum create a dropdown in Excel?
No. An enum is a VBA code construct. To provide worksheet choices, use an Excel feature such as Data Validation, or create an appropriate form control.

Can Enum members hold text?
No. VBA enum members are named numeric constants, not string values. If your data is textual, write code to map the text to an enum member or use an appropriate string-based design.

Practical takeaway

Use Enum when a VBA variable represents one of several related, named numeric choices. Declare the group at module level, choose clear member names, and assign explicit values if compatibility depends on them. The result is code whose intent is easier to understand than a collection of unexplained numbers, while keeping in mind that worksheet interface choices and input validation are separate tasks.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.