Developer

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

vbDayOfWeek.

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

march2008blog3fig1r.jpg

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

or

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
Monday
Tuesday
Wednesday
Thursday
Friday
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. 

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks