VBA Enum For Each: How to Loop Through Values

VBA & macros · Updated November 22, 2026

You cannot use For Each directly on a VBA Enum: an enum defines named numeric constants, not a collection of items. To loop through selected enum values, put them in an array or collection and use For Each; if the values are consecutive, a numeric For...Next loop is often simpler.

What an Enum Means in VBA

An enumeration, declared with the Enum statement, gives readable names to related numeric values. It is useful when a procedure accepts a small, known set of choices, such as report formats, order statuses, or weekdays. Instead of passing an unexplained number such as 3, a caller can pass a named value such as WednesdayCode.

Enum declarations belong at module level, outside a Sub or Function. In a standard module, use Public Enum when other modules in the project need to use the type; use Private Enum when it should be restricted to that module. Enum members are constants, so they cannot be changed while a macro runs. They do not, however, create a built-in list that VBA can enumerate at runtime.

By default, the first member has the value zero and each following member increases by one. You can assign values explicitly, which makes the intended numbers clear and avoids accidentally changing them if members are later reordered. An enum’s values are numeric, but their names make code easier to read and maintain.

Why For Each Cannot Loop Over an Enum

For Each needs an iterable object or array, such as a Collection, a worksheet range, or an array. An enum type is not one of those things. It defines a set of named constants at compile time; it does not store the names in a collection or expose a list of its members to a loop. Therefore, syntax such as For Each value In WeekdayCode is not a way to visit every member.

This distinction also explains a common misconception: declaring an enum with seven members does not mean VBA knows that it should loop from the first member to the last. VBA does not provide built-in enum reflection that returns all member names and values. If code needs to visit members, it must supply the values or a range of values itself.

Use For Each With an Array of Enum Values

An array is a practical choice when you want For Each syntax or when the values to visit are not consecutive. The following example belongs in a standard module. Its explicit values and separate label function keep the numeric codes and their display names clear.

Option Explicit

Public Enum WeekdayCode
    MondayCode = 1
    TuesdayCode = 2
    WednesdayCode = 3
    ThursdayCode = 4
    FridayCode = 5
    SaturdayCode = 6
    SundayCode = 7
End Enum

Public Sub PrintWeekdays()
    Dim days As Variant
    Dim item As Variant
    Dim dayValue As WeekdayCode

    ' Supply the enum members to visit.
    days = Array(MondayCode, TuesdayCode, WednesdayCode, _
                 ThursdayCode, FridayCode, SaturdayCode, SundayCode)

    For Each item In days
        dayValue = item
        Debug.Print WeekdayLabel(dayValue)
    Next item
End Sub

Private Function WeekdayLabel(ByVal value As WeekdayCode) As String
    Select Case value
        Case MondayCode: WeekdayLabel = "Monday"
        Case TuesdayCode: WeekdayLabel = "Tuesday"
        Case WednesdayCode: WeekdayLabel = "Wednesday"
        Case ThursdayCode: WeekdayLabel = "Thursday"
        Case FridayCode: WeekdayLabel = "Friday"
        Case SaturdayCode: WeekdayLabel = "Saturday"
        Case SundayCode: WeekdayLabel = "Sunday"
        Case Else: WeekdayLabel = "Unknown"
    End Select
End Function

Here, Array creates a Variant array containing the specified numeric values. The For Each control variable, item, is a Variant, as required for iterating values in an array. The assignment to dayValue gives the value an enum-typed variable before it is passed to the label function. In the Visual Basic Editor, the results appear in the Immediate window when the procedure runs.

The array is explicit by design: adding a new enum member does not automatically add it to days. Update both the enum and the array when the set of values changes. That repetition is useful to understand, not a hidden VBA feature.

Use For…Next for Consecutive Values

When all values are consecutive and you want to visit every value in that interval, a For...Next loop is shorter. With the weekday enum above, this procedure visits values 1 through 7:

Public Sub PrintConsecutiveWeekdays()
    Dim dayValue As WeekdayCode

    ' This assumes every value from 1 through 7 is defined.
    For dayValue = MondayCode To SundayCode
        Debug.Print WeekdayLabel(dayValue)
    Next dayValue
End Sub

Use this approach only when the values really form a continuous range and every number in that range represents a member you intend to process. For example, an enum with values 1, 5, and 9 should not be treated as a three-item sequence from 1 to 9: that loop would also pass through numbers that are not named members. An explicit array is safer for sparse or deliberately selected values.

Choosing Between an Array, Collection, and For Loop

The right loop depends on what the code must visit. A simple decision guide is:

  • Use For...Next for a consecutive numeric range when every value in the range is valid and needed.
  • Use an array with For Each for a fixed list, a selected subset, or values that are not consecutive.
  • Use a Collection when the items need to be assembled or changed as the procedure runs. A Collection can hold enum values, but you still need to add each value to it.
  • Use a Select Case when you need to handle particular enum values differently, rather than process a list of all values.

For a short fixed list, an array is often easiest to read and has little setup. A Collection may be useful when code conditionally adds values or when existing procedures already work with collections. Neither method discovers enum members automatically; both require code that supplies the values.

Enum Values, Gaps, and Ordering

Explicit numbering is especially important when enum values are stored in cells, files, or other persistent data, or when different procedures rely on the same numeric codes. Inserting a new member in the middle of an implicitly numbered enum can change the numbers assigned to later members. Assigning values explicitly helps keep those codes stable.

It also means a numeric loop may not match the member list. For example, if an enum assigns PendingCode = 10, ApprovedCode = 20, and RejectedCode = 30, a loop from 10 to 30 visits every number in between, not just those three named choices. Use an array containing the three named constants instead.

Enums are also sometimes used as bit flags, where each member represents a power of two and values can be combined. In that design, the combined number may represent several options at once. It is not a list of individual enum members, so a sequential loop is usually the wrong way to find which flags are set. Test the individual flags using bitwise operations, or maintain an explicit list of the flags you want to examine.

Common Errors and How to Avoid Them

  • Trying to loop over the enum name: VBA does not treat the type name as an array or collection. Supply values through an array or use an appropriate numeric loop.
  • Using a restrictive For Each variable: For an array of values, declare the loop variable as Variant. Assign each item to a variable declared with the enum type when that makes subsequent code clearer.
  • Assuming a numeric range contains only named members: A For...Next loop visits every number between its limits. Use it only for a genuinely consecutive set.
  • Forgetting to update the array: Adding a member to the enum does not update a separate array. Keep the list in sync or use a numeric loop only when its assumptions remain true.
  • Expecting member names from a number: An enum value is numeric at runtime. Use a mapping function such as WeekdayLabel when the code needs a human-readable name.

If VBA reports a type or loop-control error, check the declaration of the control variable and whether the expression after In is actually an array or collection. Also check that enum declarations are outside procedures and that the values supplied to a loop match the intended type and range.

Where Enum-Based Loops Are Useful

Enums are most helpful when code must work through a controlled set of choices. A reporting macro might loop through a short list of output formats; a validation routine might check each supported status; or a helper procedure might create a worksheet section for each configured category. In each case, the enum supplies meaningful names, while the array or loop supplies the iteration mechanism.

Do not create an enum solely to make a loop possible if the values are truly open-ended, such as customer names or worksheet names that users can add. Store changing data in a range, array, or collection instead. Enums suit stable choices defined by the program; they are not a substitute for a data table.

Safety and Workbook Considerations

Enum declarations and loops do not change workbook data by themselves, but the procedure that uses them may. Before running a macro that edits cells, creates sheets, or changes workbook structure, save a copy of the workbook. VBA macro actions generally cannot be undone with Ctrl+Z, so a backup is the simplest way to protect the original file.

The examples use Debug.Print, which writes text to the Visual Basic Editor’s Immediate window rather than changing worksheet cells. If you adapt the code to write results to a sheet, identify the destination range deliberately and avoid assuming that the active sheet is always the intended one. Macro availability and security prompts can differ according to workbook format and trust settings, but they do not change how enum iteration works.

Frequently Asked Questions

Can I use For Each directly on a VBA Enum?
No. An enum defines named numeric constants, not an iterable array or object. Put the values in an array or collection for For Each, or use For...Next for a consecutive numeric range.

What type should the For Each variable be?
For an array containing enum values, declare the control variable as Variant. You can assign each item to a variable declared with the enum type inside the loop before calling code that expects that type.

Does VBA have a function that returns every enum member?
No built-in VBA feature automatically lists an enum’s names and values at runtime. Keep an explicit array or another list if code needs to iterate the members.

Can I loop through enum values that are not consecutive?
Yes. Put the desired constants in an array and use For Each. Avoid a numeric For...Next range when it would also visit undefined numbers or values you do not want.

Can I use an Enum in Excel VBA on Mac?
Yes. Enums and the same basic VBA loop constructs are part of VBA, not a Windows-only Excel feature. The Visual Basic Editor interface differs between Windows and Mac, but the enum iteration approach is the same.

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.