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
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
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”.
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.
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
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.