VBA Enums

An Enum (Enumeration) is a list of named integers that are declared together as a common type. Enum types are used to represent a group of items that are bound together by a concept such as a list of colors, keys on a keyboard, characters in the ASCII table, or options for how a function should behave. An Enum is essentially a group of related constants. The value of each Enum member is determined at design-time and cannot be reassigned at run-time. Enums are often used as parameters to procedures or return values from functions. Enums can be used with a Select Case statement to achieve clear and organized branching in a program. Enums always evaluate to type Long.

Declaring And Defining Enums

Enums must be declared at the top of the module they are in otherwise a compile error will occur. Enums can be declared Public or Private. If Public or Private is omitted then Enums are Public by default. Public Enums cannot be declared inside Class Modules. The values of Enum members can be inferred or assigned explicitly. By default, the value of the first Enum member will be 0. If an Enum member is not explicitly assigned a value then its value will be inferred by incrementing by 1 from the previous Enum value.

Public Enum Options
    Option1
    Option2
    Option3
End Enum

Public Sub Example()
    Debug.Print Options.Option1 'Prints 0
    Debug.Print Options.Option2 'Prints 1
    Debug.Print Options.Option3 'Prints 2
End Sub

Public Enum Options
    Option1 = 1
    Option2
    Option3
End Enum

Public Sub Example()
    Debug.Print Options.Option1 'Prints 1
    Debug.Print Options.Option2 'Prints 2
    Debug.Print Options.Option3 'Prints 3
End Sub

Public Enum Options
    Option1
    Option2 = 2
    Option3
End Enum

Public Sub Example()
    Debug.Print Options.Option1 'Prints 0
    Debug.Print Options.Option2 'Prints 2
    Debug.Print Options.Option3 'Prints 3
End Sub

The default value for a Long integer is 0, so if 0 is used as an Enum value, the default value for a variable of the Enum type will be that Enum member. It is often better to avoid defaults and not use 0 in Enums.

Public Enum Options
    Option1
    Option2
    Option3
End Enum

Public Sub Example()

    Dim Opt As Options

    'Opt is not assigned and has a default value of 0 so it is implicitly Option1.

    Select Case Opt

        Case Option1
            Debug.Print "Option1"

        Case Option2
            Debug.Print "Option2"

        Case Option3
            Debug.Print "Option3"

        Case Else
            Debug.Print "Else"

    End Select

End Sub

Enum members are not required by VBA to be different from one another but they should always be different. If more than 1 Enum member has the same integer value there is no way for a program to differentiate between the two members.

'NEVER DO THIS - impossible to distinguish members
Public Enum Options
    Option1 = 0
    Option2 = 0
    Option3 = 0
End Enum

Arbitrary Vs Meaningful Member Values

The values of an Enum can be arbitrary integers or the integer values can have a specific meaning or use. For example, the integer values of an Enum used to define options for a function may not have any special meaning or purpose other than to distinguish each option, but the integer values in an Enum used to define column indexes have a specific purpose to represent the index of each column.

Public Enum FunctionOption
    'The actual integer values of the members here are arbitrary.
    'The values could just as well be 4, 5, 6.
    Option1 = 1
    Option2 = 2
    Option3 = 3
End Enum

Public Enum ColumnIndex
    'The actual integer values represent the column indexes.
    Column1 = 1
    Column2 = 2
    Column3 = 3
End Enum