Enumerations

Introduction

While working on the tool for understanding and taking the inventory of Shapes in a Workbook I noted that each Shape  has the property of "Type" (Shapes(<n>).Type) and that each was shown as a mnemonic, e.g "Shapes(1).Type = msoAutoShape". 

Definitions

From experience I knew "msoAutoShape" is a mnemonic within an enumeration.

• Enumeration - Wikipedia's entry begins "An enumeration is a complete, ordered listing of all the items in a collection.."  I will add that each member of the enumeration is uniquely identifiable.  In the case of VBA/Office enumerations, each member is uniquely identifiable by its Value as well as its mnemonic Name.
• Mnemonic - According to Google, a mnemonic is "a device such as a pattern of letters, ideas, or associations that assists in remembering something." For VBA enumerations, this mnemonic is the "Name" for a member of an enumeration.

What is a VBA Enumeration?

In VBA, an enumeration is a list of unique values, aligned with a unique mnemonic and not-necessarily unique description.   The mnemonic is referred to as "Name".   For example, the table below is for the enumeration of XlHAlign, used for aligning text within a Range, 

Name	Value	Description
xlHAlignCenter	-4108	Center.
xlHAlignCenterAcrossSelection	7	Center across selection.
xlHAlignDistributed	-4117	Distribute.
xlHAlignFill	5	Fill.
xlHAlignGeneral	1	Align according to data type.
xlHAlignJustify	-4130	Justify.
xlHAlignLeft	-4131	Left.
xlHAlignRight	-4152	Right.

Table 1 - XlHAlign enumeration

In short, a VBA enumeration is a reference for the valid numeric values, with the associated text for mnemonics (Names) and text descriptions,

How Enumeration is Used

Value Assignment

For a given numeric Property, its value is one of the Values is in the Enumeration.

Synonyms

Because, as stated above, each member of an enumeration is "each member (of an enumeration) is uniquely identifiable by its Value as well as its mnemonic Name" the Value and the Name are linguistically synonymous.   

They are also syntactically synonymous: the interpreter and compiler will recognize the Name as being the equivalent of the Value.  

Enough with the big words: either the Value or the Name is acceptable in a VBA statement.  For example, using the enumeration above where "rng" is a Range, "rng.HorizontalAlignment = -4131" and "rng.HorizontalAlignment = xlHAlignLeft" are equivalent statements.

Validation

If some value other than those in the Enumeration is used, error  '9', Subscript out of range, is raised.  For example, running the following code
    Sub TestAlignmentValue()
         ThisWorkbook.Worksheets(1).Range("A1").HorizontalAlignment = 12
    End Sub
results in the following error.

Figure 1 - Error for invalid enumeration

What enumerations are available?

The enumerations change from release to release, typically adding rather than subtracting due to backwards compatibility.  At this point, I have found 176 different enumerations in respect to Office.

Rather than trying to here to list all of the enumerations in Office, see this link for the Office 2013 enumerations.

Why use enumeration in VBA Code?

Ease of Programming

For most people , it is often easier to the mnemonic name for a member of the enumeration list than it is the Value.  This is especially true when, as often happens, one is working with a few enumerations for a set of code, e.g. the formatting enumerations for cells.

Readability for Maintainability

While code may be a little quicker if using the Value rather than the Name, when someone is trying to decipher what is happening with the code, seeing the names xlHAlignCenter and xlHAlignJustify is more meaning full than -4108 and -4130, respectively.