Excel VBA: Enumerations

Instead of remembering numeric codes – label them with meaningful words. This way your code will flow from your fingers just as you speak, and your program will be clear and readable. Let us understand enumerations.

What are Enumerations?

Simply put, enumeration is about naming sets of related constant numbers.

You probably already worked with enumerations. For example, the Application.Calculation property is numeric. It holds a number representing the calculation mode of Excel. Possible values for this property are:

Assigning the value -4135 to the Application.Calculation property will set Excel on a manual calculation mode.

However, as you start typing the statement, Intellisense identifies the property you are assigning to and pulls up a menu for you to choose one of the three valid values for this property:

That’s nice, isn’t it? Who cares -4135 represents Manual?! The label xlCalculationManual represents the number -4135 in the Application.Calculation enumerator, and that’s all we need to know (even that pops up with the courtesy of Intellisense…).

We say that the Application.Calculation property is enumerated.

Defining our own Enumerators

We can define our own enumerators, to make it easy for us to work with sets of numeric numbers.

Let’s enumerate a set of regions’ codes:

Enum Eregions

   USA = 1

   Europe = 2

   Asia = 3

End Enum

We can now reference the Eregions enumerator for any of its numeric values by their corresponding label: Eregions.Europe will return 2:

MsgBox “Asia Region Code: “ & Eregions.Asia

When defining enumerators, we need not specify the actual numbers if we don’t want to. The VBA engine will automatically assign a numeric value to those labels missing explicit number values:

Enum Eregions

   USA = 11

   Europe

   Asia

End Enum

In the above Eregions definition, USA is explicitly assigned the value 11, and Europe will be assigned the value 12. Asia will get 13. Had I omitted the 11 value for USA, it would have been assigned the value 0 and the rest of the values would have followed accordingly: 1, 2, 3.

Looping over Enumerators

As enumerators are actually numbers in sequence, we can apply a For..Next loop over an enumerator to process all of its members.

However, how would we know what enumerator label represents the first member (that is, having the lowest number in the set) and what label represents the last member? We don’t want to use the actual numbers (we don’t even remember them), only their labels…

For that we have the option to include an indication of the first and last labels of a given enumerator, in the enumerator definition. Consider the following countries enumerator:

Enum ECountries

    Mexico = 11

    Portugal

    France

    Germany

    Great_Britain

    Brazil

    United_States

    Israel

    Egypt

    [_Start] = Mexico

    [_End] = Egypt

End Enum

As you can see, I flagged Mexico to be the first and Egypt to be the last in the numbers set of the Ecountries enumerator. Now, I can loop through the Ecountries enumerator using a For..Next loop.

The following function prints out all countries’ codes and returns the total number of countries in the set:

Function CountAllCountries() As Long

    Dim i As Long

    Dim lngCount As Long

    lngCount = 0

    For i = ECountries.[_Start] To ECountries.[_End]

        Debug.Print i

        lngCount = lngCount + 1

    Next i

    CountAllCountries = lngCount

End Function

The function will return the number 9, as the loop iterated over all 9 members from start (Mexico) to finish (Egypt). By the way, had we defined Brazil to be the [_End] value in the set, the function would have returned 6, as the countries past Brazil would not have been looped through.

As you can see, is it a convention to start the enumerator’s name with a capital E to remind us it is an enumerator, but of course this is not mandatory.

Enumerators are typically defined at the declarations area of the module, just above the first Sub / Function is placed, and you can set its scope to be either Private or Public just as with any other module-level variable.

Practice a little bit with enumerators this week, as we take it further next week when we explore Bit-wise enumerations and their use case.

Be sure to share this Blog post with your colleagues – they’ll love ya!