Pearson Software Consulting Services

    Using ENUM Type Variables In VBA 

         Enum variables are a special, self-documenting type of variables that you can use in your VBA code. You use Enums to group several values of a variable into a single variable declaration. All Enum variables are of type Long. An example will serve well to illustrate the use of Enums. Imagine that you have 3 types of employees: contract, hourly, and salaried. To assign constant values to these employees, you might be tempted to use Const statements, as follows.

Const Contract As Long = 0
Const Hourly As Long = 1
Const Salaried As Long = 2

You can then use those constants in code to assign a type to a variable. E.g.,

Dim EmployeeType As Long
EmployeeType = Hourly


A better way is to use a Enum to group to logically group these values together:

Enum EmpType
    Contract = 0
    Hourly = 1
    Salaried = 2
End Enum

This groups the three values into a logical group called
EmpType. Now, you can declare a variable as that type:

Dim EmployeeType As EmpType

One advantage of doing so is that the code is self-documenting. You can tell that the EmployeeType variable can take on the values listed in the Enum EmpType.  Moreover, you get VBA"s Intellisense support when you assign a value to the EmployeeType variable, as show below:



This shows you all the allowable values that can be assigned to EmployeeType.

It should be noted that any Long number may be assigned to EmployeeType. Just because only 3 named values exist in the Enum type, you are not limited to those values. The compiler will not warn you if you assign a value not in the list to the variable, and no runtime error is raised.

You do not have to explicitly assign values to the elements of the Enum variable. If you don't assign values, the compiler will give a value of 0 to the first element, and increment by one the value of each subsequent element. For example, the two Enum declarations behave exactly the same:

Enum EmpType
    Contract = 0
    Hourly = 1
    Salaried = 2
End Enum
 

Enum EmpType
    Contract
    Hourly
    Salaried
End Enum

 

You can assign a value to one of the elements, and the compiler will begin renumbering from that element through the last element. For example the following are functionally equivalent

Enum EmpType
    Contract
    Hourly = 4
    Salaried
End Enum
 

Enum EmpType
    Contract = 0
    Hourly = 4
    Salaried = 5
End Enum
 


Note that Enums were introduced in VBA6 (Excel 2000) and are not available in earlier versions.

 

 

 

 

     
     

 

 Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers
chip@cpearson.com

© Copyright 1997-2007  Charles H. Pearson