Defining Enumerated Types in VBA

Like custom data types, enumerated types contain multiple elements; however, enumerated types are derived only from the integer data type. In an enumerated type, each integer is assigned an identifier called an enumerated constant. This allows you to use symbolic names rather than numbers, making your program more readable.

You must define an enumerated type in the general declarations section of a module. Once an enumeration is defined, you can declare variables, parameters, or a procedure's return value with its type.

Enumerated types are defined with their elements listed between the Enum and End Enum statements. The following definition reproduces VBA's VbDayOfWeek enumerated type.

Public Enum Weekdays Sunday = 1 Monday Tuesday Wednesday Thursday Friday Saturday End Enum

The elements of an enumerated type are initialized to constant values within the Enum statement. You can assign the elements of an enumerated type both positive and negative integers. If no specific assignment is made, then VBA assigns the first element 0, the second element 1, and so on. Alternatively, you can assign a value to the first element and VBA will make subsequent assignments to all other elements by incrementing each value by one. In the Weekdays enumerated type, I assigned 1 to Sunday and made no assignments for the remaining elements; however, VBA automatically assigns the value 2 to Monday, 3 to Tuesday, and so on.

The assigned values in an enumerated type are constants and therefore can't be modified at run time.

Variables of an enumerated type are declared in the usual way using the name of enumerated type. They can be assigned any integer value, but it defeats the purpose of using an enumerated type if you assign the variable anything other than one of the enumerated constants.

Dim wkDay As Weekdays wkDay = Tuesday

In effect, you should treat the variable wkDay as a highly constrained integer that can only be assigned values between 1 and 7, even though it can store any value of type integer.

Next, consider the following function called GetDayOfWeek() that is declared public in a standard module. The return type of the GetDayOfWeek() function is that of the previously defined enumerated type Weekdays. When called in a worksheet formula, this function returns an integer value between 1 and 7 depending on the value of the string passed to the function. Certainly, an identical function can be written without using an enumerated type; however, the purpose of an enumerated type is to make your program more readable and the Weekdays enumerated type achieves that goal.

Public Function GetDayOfWeek(wkDay As String) As Weekdays wkDay = LCase(wkDay) Select Case wkDay

Case Is = "sunday"

GetDayOfWeek = Sunday Case Is = "monday"

GetDayOfWeek = Monday Case Is = "tuesday"

GetDayOfWeek = Tuesday

Case Is = "Wednesday"

GetDayOfWeek = Wednesday Case Is = "thursday"

GetDayOfWeek = Thursday Case Is = "friday"

GetDayOfWeek = Friday Case Is = "saturday"

GetDayOfWeek = Saturday End Select End Function

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment