Enumerations are often used in various languages to list certain variable variations within a single object to make them easy to find or list. The VBA Enum is a equally valuable construct – allowing you to clean up your mess by grouping a collection of constants into a single object you can easily explore. In this post I will also try to compare an alternative approach with the use of VBA Modules – also allowing you to enumerate constants but in a different fashion.

What is an Enum and why use it?

What is an Enum (enumeration)? It is a type that contains an enumeration of constants. A good example of an enumeration is a list of types of cars e.g. Sedan, Hatchback, SUV, Truck etc. Enumerations make it easy to group these values. Enums can be used as variable, however, an Enum is in fact a numeric variable (Long), which at any time represents one of items in the enumeration.

Why would you want to use enums? In most cases you might as well live without enums, but they are very useful! Imagine your application can throw multiple status message numbers, like errors or warnings. In most programming languages like C# or Java you can keep them in separate resource files. In VBA this is not the case. One way of grouping these messages would be to create constants:

Const msg_Welcome = 1
Const msg_Error = 2
Const msg_Warning = 3
Sub SomeProcedure
  '...
  Debug.Print "Message number: " & msg_Welcome
  '...
End Sub

Seems ok, right? WRONG! When searching through your VBA code this is what you might see (on the right).

Your enumerated message numbers will get mixed up in a mumbo-jumbo of different variables. Well you can group these messages into a single Enumeration type like this:

'Declare the Enum
Enum Messages
  Welcome = 1
  Error = 2
  Warning = 3
End Enum

'Example usage of Enum
Sub SomeProcedure
  '...
  MsgBox "Message number: " & Messages.Welcome
  '...
End Sub

Instead of the above, you can browse just your list of enumerations, without stumbling across other functions and variables.

Syntax of the VBA Enum

Let’s get introduced to the sytax of the VBA enum. First and foremost remember that the Enum is in fact a numeric variable (Long). A Enum variable represents one of its items (possible values).

Enum EnumName
  EnumValue [= LongValue]
  EnumValue [= LongValue]
  [[_EnumValue]] [= LongValue]
  '...
End Enum

The VBA Enum needs to have specified enum values. These can optionally be defined with a certain long value. Each enum value is assigned a certain numeric value. The numbering starts at 0. If an enum item is unassigned with a long value it will be automatically assigned the increment of the previous item.

Enums allow you to hide elements using and underscore and brackets like this [_SomeValue]. These items won’t be visible using VBA “intellisense”.

Valid examples

The below are perfectly valid examples of VBA enumerations.

Assigning values to enums

Enum CarType
  Sedan         'Value = 0
  HatchBack = 2 'Value = 2
  SUV = 10      'Value = 10
  Truck         'Value = 11
End Enum

An enum without any items assigned

Enum CarType
  Sedan       'Value = 0         
  HatchBack   'Value = 1
  SUV         'Value = 2
  Truck       'Value = 3
End Enum

Enumerating a VBA Enum

Funny as it sounds sometimes this is really what we want to do. Imagine you get a number representing one of your enum values (say one of 200 languages on the list). You want to know which item is being referenced. To do this we need to introduce the invisible enum values [_First] and [_Last] and loop through them to find our value.

Enum Language
  [_First]
  Albanian
  Armenian
  '...
  French
  English
  '...
  [_Last]
End Enum

Finding our enum value:

Dim enumVal as Long, unknownEnumValue as Language
For enumVal  = Language.[_First] To Language.[_Last]
  If unknownEnumValue = enumVal Then
      'Found it!
      '...Code here...
       Exit For
  End If
Next enumVal

Type vs Module vs Enum

Most VBA users are often lost when having to distinguish the differences between Types, Modules and Enums. This is totally understandable thus let us clear out these misunderstandings.

Type

A Type in VBA is a custom data type that can be defined. It can only contain variables, not functions nor procedures. Types consist of multiple associated variables (like Classes), while an Enum is simply a numeric value that represents one of it’s possible items.

Type MyType
   SomeString as String
   SomeLong as Long
End Type

Sub Main()
  Dim t as MyType
  t.SomeString = "Hello"
  t.SomeLong = 10
End Sub

Module

A Module in VBA is simply a separate “section” of a VBA Project and can contain variables, constants and procedures. Module names can be used to select variables or procedures, thus making then an alternative to Enums when wanting to group enumerations of strings or other non-numeric data types.