How to use and define VBA enumerate constants

VBA Office enumerates

A VBA enumeration is a special type of constant that automatically assigns predefined values to its members. Usually, the members are related. For instance, there's an enumeration named vbDayOfWeek that contains constants for each day of the week. You can see its members by opening a module in the Visual Basic Editor and entering


Don't forget the period at the end-that wakes up VBA's Intellisence, which displays the members.


You can view enumerates specific to an Office application by opening Help in the VBE and typing, "enumerate." (You must be in the VBE.)

VBA is flexible where enumerates are concerned and allows you to create your own. To do so, declare an enumeration type using the Enum statement in the Declarations section of a standard or public class module using the syntax

Private Enum WorkDays


Public Enum WorkDays

where WorkDays is the enumerate's name (which you provide). Then list the constant names in order of their appropriate Integer values. VBA will assign the value 0 to the first constant and increase by 1 for each subsequent member. You might never use the Integer values, but keep this in mind when creating your list. For Example, VBA would assign the values 0 through 4 to the members Monday through Friday:

Private Enum Workdays
End Enum

To use the enumeration, simply declare a variable as the enumeration's type in the form

Dim DayOff As WorkDays
DayOff = workdayconstant

where workdayconstant is Monday, Tuesday, Wednesday, Thursday or Friday or 0, 1, 2, 3, or 4, respectively.