Working with enumerated values and the Enum Builder

Why use enumerated values

Enumerated types specify a list of values that can be selected from. Office VBA has hundreds such types ready built-in:

  • VbMsgBoxResult: vbOk, vbCancel, ...
  • XlSheetType: xlWorksheet, xlChart, ...

Obviously, if enums represent the allowed values of so many properties, you will often let your code make decisions on these enums, e.g. if in your code you specified the variable ret As VbMsgBoxResult then, after calling the MsgBox function you will check the result: If ret = vbOk Then.

Defining Enums then is a good practice you should adopt yourself; Enums make explicit the options you support for a property value and allow compile-time verification of your code, thus preventing errors. The Enum Builder is a neat tool to help you work with Enumerated types. I will apply it to a 'Grading' enumeration as an example.

Using the Enum Builder

The below sections axplain the features of the Enum Builder. Note that the Select Case Builder lets you conveniently make Select Case code block which have a Case for each value of the enum.

Enum Builder

Declare an Enumerated type and assign a value

Firstly, in VBA, Enums are declared at the top of a module. 


Enum Grading
grExcellent = 1
grGood = 2
grNormal = 3
grMediocre = 4
grBad = 5
End Enum

Next, you will use it bu declaring variables of the type and assign it a value, e.g.: 


Dim grStudentGrade As Grading: grStudentGrade = grGood

The Enum Builder also is a convenient tool to create several supporting functions.

Translating enums to their string values

By checking the Enum_ToString, the generated code will include a function which presents a readable form for the grading value; it returns 'Good' instead of the mysterious value 2.


Function Grading_ToString(value As Grading) As String
Select Case value
Case grExcellent: Grading_ToString = "Excellent"
Case grGood: Grading_ToString = "Good"
Case grNormal: Grading_ToString = "Normal"
Case grMediocre: Grading_ToString = "Mediocre"
Case grBad: Grading_ToString = "Bad"
End Select
End Function

Translating a string value to their enum value

When the teacher enters values during grading, you may want to be able to interpret a string value as its enumerated counterpart. This function is made available when you tick String_ToEnum:


Function Grading_ToEnum(str As String) As Grading
Select Case str
Case "grExcellent": Grading_ToEnum = grExcellent
Case "grGood": Grading_ToEnum = grGood
Case "grNormal": Grading_ToEnum = grNormal
Case "grMediocre": Grading_ToEnum = grMediocre
Case "grBad": Grading_ToEnum = grBad
End Select
End Function

Calling this function could work like this (here prefixing the prefix gr) 


Dim grStudentGrade As Grading
Dim strGrade As String: strGrade = "Good"
grStudentGrade = Grading_ToEnum("gr" & strGrade)

Checking if a number is a valid Enum value

Let's say you allow the teacher to also input gradings as numbers. You mights say, any value between 1 and 5, which here is ok, but it is possible (with MS this happens regularly) that the numbers are not a neat sequence.


Function Grading_IsEnumValue(lng As Long) As Boolean
Select Case lng
Case grExcellent, grGood, grNormal, grMediocre, grBad
Grading_IsEnumValue = True
End Select
End Function

Looping over the enumerated values

To allow for this, we extend the enum with [_First] and [_Last]. This could be used, amongst others, as an alternative for the above Select Case approach in case of checking if input is valid.


Enum Grading
grExcellent = 1
grGood = 2
grNormal = 3
grMediocre = 4
grBad = 5
[_First] = 1
[_Last] = 5
End Enum


Dim i As Long, booIsValid As Boolean
Dim grStudentGrade As Grading: grStudentGrade = 4
For i = Grading.[_First] To Grading.[_Last]
If grStudentGrade = i Then
booIsValid = True
Exit For
End If
Next N