Excel VBA Enum: How It Works and When to Use It

An Excel VBA Enum is a named set of numeric constants: it lets code use readable labels such as ExportAsPDF instead of unexplained numbers such as 2. Declare it at the top level of a VBA module, then use the enum type and its members in variables, procedure arguments, and decision logic.

What an Enum Means in VBA

Enum is short for enumeration. In VBA, it defines a group of related named values, with each member associated with a number. The names make code easier to understand and maintain, particularly when a procedure accepts one choice from a small, known set.

For example, a procedure might need to know whether an export should contain values, formulas, or text. Using a number for each choice makes the caller and the procedure depend on an undocumented convention. An enum gives those numbers descriptive names. A reader can understand ExportAsValues without looking up what the number 1 is supposed to mean.

An enum is a programming-language feature, not an Excel worksheet feature. It does not create a drop-down list, validate cell entries, or add a new worksheet function. You can use enum members in macros and other VBA procedures, but a cell formula cannot directly refer to an enum as though it were a worksheet name or function.

How to Declare an Enum

In the Visual Basic Editor, an enum declaration belongs at module level, outside any Sub or Function. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11, choose Insert > Module to add a standard module, and place the declaration there. The editor shortcut can differ on Mac keyboards; use the Visual Basic Editor from Excel’s macro or Developer tools if the shortcut is unavailable.

Here is the basic structure. The first member defaults to zero when no value is assigned, and each following member increases by one. Explicit values are often clearer when the numbers are part of an established convention.

Option Explicit

' Declare this at module level, outside all procedures.
Public Enum ExportFormat
    ExportAsValues = 1
    ExportAsFormulas = 2
    ExportAsText = 3
End Enum

Public makes the enum available beyond its declaring module within the VBA project; Private limits its visibility to that module. If you omit the access modifier, VBA uses the applicable default visibility. A standard module is a practical place for a public enum intended for use throughout a workbook project.

The member values are numeric constants, and an enum in VBA uses a Long-based numeric representation. You can assign explicit values and leave gaps if that helps preserve existing numbers when adding a member later. For example, values 1, 10, and 20 are still valid enum values; they do not have to be consecutive.

A Complete Excel VBA Example

This example declares a format enum and uses it in a procedure. The procedure shows the selected option rather than changing a workbook, so it demonstrates the type and branching without relying on a particular worksheet layout.

Option Explicit

' These named choices are available throughout the project.
Public Enum ExportFormat
    ExportAsValues = 1
    ExportAsFormulas = 2
    ExportAsText = 3
End Enum

Public Sub ShowExportChoice()
    Dim selectedFormat As ExportFormat

    selectedFormat = ExportAsValues

    Select Case selectedFormat
        Case ExportAsValues
            MsgBox "The export will use values."
        Case ExportAsFormulas
            MsgBox "The export will use formulas."
        Case ExportAsText
            MsgBox "The export will use text."
    End Select
End Sub

To try it, insert the code into a standard module, place the cursor inside ShowExportChoice, and run the procedure from the editor. You can also run a public macro from Excel’s macro dialog. Save a copy of the workbook before running any macro that changes data: VBA actions cannot be undone with Ctrl+Z. The displayed-message example itself does not edit cells.

Here, selectedFormat is declared as ExportFormat, which communicates the intended kind of value. The Select Case statement then uses the member names rather than numeric literals. In a larger macro, each case could call a different export routine, while the caller still passes an understandable choice.

Enum Scope and Where to Put It

VBA requires an Enum declaration to be outside a procedure. Putting it between Sub and End Sub causes a compile error. Keep declarations near the top of the module, before the procedures that use them, so the type and its choices are easy to find.

Choose visibility based on the intended audience. Use Private Enum when the choices are an implementation detail of one module. Use Public Enum in a standard module when procedures in several modules need the same type. This helps avoid copying the same numeric definitions into multiple places, where they can drift apart.

Names in a VBA project share a naming environment, so choose distinctive enum and member names. A member called Open, for instance, may be confusing if the project also uses other symbols with similar names. A prefix related to the enum, such as ExportAsValues, makes the member’s purpose clearer and can reduce naming conflicts.

When an Enum Is a Good Choice

Use an enum when a value represents one of a small, meaningful set of alternatives and those alternatives are part of the code’s design. Typical Excel macro uses include selecting a report mode, choosing a chart style handled by your own procedure, describing a processing status, or specifying which kind of output a routine should produce.

Prefer an enum when a procedure accepts a fixed set of named options and you want calls to be self-explanatory.
Prefer a Boolean when there are exactly two simple states and names such as includeHeaders express the choice clearly.
Prefer a variable or configuration table when users need to add, edit, or manage choices without changing and recompiling VBA code.
Prefer a worksheet validation list when the choice is entered by a workbook user in a cell and should be visible in the sheet.

Enums work especially well at the boundary between procedures. A caller can pass ExportAsFormulas to a routine, and the routine can respond to that named option. They are less useful for a list that changes frequently or is driven by workbook data, since changing the enum requires editing VBA rather than updating a table.

What Enums Do Not Do

A VBA enum is not a text-based category system. Its members correspond to numeric values; it does not store labels such as “Values” as the underlying enum value. If a worksheet needs to show a friendly label, your code must convert between the enum member and the text, or use a table that maps numeric choices to labels.

Enums also do not automatically create a graphical selection control or enforce a worksheet data rule. They make VBA code more readable, but they are not a substitute for validating user input. If a macro reads a value from a cell, it should still check that the value is allowed before acting on it.

Declaring a variable as an enum improves clarity, but do not treat it as a complete validation barrier against every unexpected numeric value. Code can encounter values from cells, files, or other procedures, and robust procedures should include a default or error-handling path. A Select Case can include Case Else to deal with an unsupported value rather than silently assuming it is valid.

VBA enums are also different from enums in some other languages. They do not provide string members or the richer type features found in languages such as VB.NET. If you are following a tutorial written for another language, check its syntax and assumptions before copying its enum example into Excel VBA.

Common Enum Problems and Fixes

Compile error after putting the declaration inside a macro: move the entire Enum block to module level, outside every procedure.
Type not defined: check that the enum declaration is in the same VBA project, that its spelling matches, and that its visibility permits the code to use it. A public enum shared by modules is commonly placed in a standard module.
Duplicate or confusing member names: choose unique, descriptive names, especially when several enums exist in the same project.
Unexpected numeric value: check the source of the value and add validation or a Case Else branch. An enum declaration does not validate untrusted cell or file input for you.
New member changes old behavior: assign explicit values when the numbers have external significance, and avoid renumbering members that are stored or exchanged elsewhere.

Another frequent misconception is that enum values are always numbered from one. By default, the first member is zero, not one, and the rest increase from there. Set values explicitly when zero would be misleading, when a particular number has meaning, or when consistency with an existing interface matters.

Enums Compared with Constants and Worksheet Lists

A group of individual constants can also give names to numbers, but an enum packages related choices under one declared type. That communicates that the options belong together and lets a procedure declare an argument or variable using the enum name. For a single fixed value, a constant may be simpler; for a coordinated set of alternatives, an enum is usually easier to maintain.

A worksheet list solves a different problem. Data Validation can present user-selectable entries in cells, and a table can hold options that workbook users maintain. Those are appropriate when the choices are part of the workbook’s interface or data. An enum is appropriate when the alternatives belong to the VBA program’s logic and are expected to change only when the code is edited.

These approaches can work together. A macro might read a selected label from a cell, validate it against a known set of options, and then translate it to an enum value before calling internal procedures. Keep the conversion explicit, because the worksheet label and the enum’s numeric value are not automatically linked.

Frequently Asked Questions

Can I declare an Enum inside a VBA Sub?
No. Declare it at module level, outside any Sub or Function. The procedures that need it can then refer to the enum according to its visibility.

What is the default first value in a VBA Enum?
If you do not assign a value, the first member is zero. Each following member is one greater than the previous member unless you assign a different value explicitly.

Can an Excel cell formula use a VBA enum?
Not directly. A VBA enum is available to VBA code, not as a worksheet function or named cell value. A macro can translate an enum to text or write a corresponding value into a cell.

Should I use Public or Private for an Enum?
Use Private when only one module needs the type. Use Public when procedures elsewhere in the VBA project need it; a standard module is a clear home for a shared public enum.

Does an Enum create a drop-down menu in Excel?
No. An enum defines named numeric constants for VBA. To give worksheet users a cell drop-down, use Excel’s Data Validation feature instead.

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.