Microsoft Office Development and Consultancy
 Home|Excel|

VBA

|C#|Finance|Tools|Newsletter|Feedback|Contact 
 VBA > Syntax > Enumerations< Previous | Next > 
 

What is an Enumeration ?

 
 

An enumeration is a distinct value which provides an alternative to using a constant.

 
 

Every enumeration has an underlying type which can be any integral type: Integer, Short, Long etc

 
 

Enumeration are typically in groups and provide a way of categorising your symbolic constants into a defined structure.

 
 

A list of built-in enumerations can be seen in the object browser

 
   

 

Built-in Enumerations

 
 

There are a huge number of built-in enumerations that are used to categorise all the built-in constants.

 
 

For a full list of all these enumerations please refer to the dedicated Enumerations > Dedicated Enumerations Section.

 
 

It is often a lot easier to remember the symbolic constant names rather than the actual numeric values.

 
 

Using enumerations has the added benefit of enablying the Auto List Members feature to provide help.

 
 

A good example of a built-in enumeration is related to the MsgBox function.

 
 
1
2
3
4
5
6
7
8
9
Public Enum vbMsgBoxResult
   vbOK = 1
   vbCancel = 2
   vbAbort = 3
   vbRetry = 4
   vbIgnore = 5
   vbYes = 6
   vbNo = 7
End Enum
   

 

Creating Enumerations

 
 

It is possibe to create your own.

 
 

Beware using floating point values with Enum. All constant values are treated as long integers rounded to the largest integer.

 
 

The default type is Integer if omitted.

 
 
10
11
12
13
14
Public Enum OverWrite As Integer
   owYes = 1
   owNo = 2
   owNoToAll = -2
End Enum
   

 

If you don't specify any numerical values the enumeration will start at 0 and each subsequent value will incrememt by 1.

 
 
15
16
17
18
19
20
Public Enum enTemperatue
   Constant1 'this will have the value 0
   Constant2 ' this will have the value 1
   Constant3 = 20
   Constant4 'this will have the value 21
End Enum
   

 
21
22
23
24
25
26
Public Enum enTemperatue As Long
   Constant1 = -20
   Constant2 = 0
   Constant3 = 10
   Constant4 = 35
End Enum
   


 

Displaying the Enumeration Label

 


 

Displaying the Enumeration Value

 
 

When you want to display the value of an enumerated constant rather than its name you must cast the constant to its underlying type

 
 

For example:

 
 
27
  CType(enTemperatire.Constant2, Integer)
   


 
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Public Enum enEnumeration
   MyFirstValue = 1 '0 is the default, can be overwritten to any value
   MySecondValue
   MyNamedRange1
   MyFourthValue
End Enum

Public Sub Testing()
Dim svalue As String
' Debug.Print [MyNamedRange1].Address 'this will not compile with an 'Invalid Qualifier' error
' Debug.Print [MyNamedRange2].Address
    
' svalue = Application.VLookup(20, [MyNamedRange2], 2, False)
' Debug.Print svalue ' = two
    
'this will compile but will generate a 'Type Mismatch' when actually run
 'This is becuase the enumeration is beinf used first
' svalue = Application.VLookup(20, [MyNamedRange1], 1, False)
    
'this can be tested by running the following line
 'if the named range was being used this would generate a 'Type Mismatch' error
    Debug.Print [MyNamedRange1] ' = 3
End Sub
   

 

Case Sensitive

 
 

you can declare enumerations in mixed case but while using them in your subroutines you type them in lowercase it will change the original declaration to lowercase

 


 

Things to Remember

 
 

  • When you are referring to named ranges in your code never use the Range abbreviation [MyNamedRange].

    		 
     

  • Always fully qualify your enumerations.

    		 
     

  • Always prefix your enumeration values with a "en" prefix so they are not accidentally confused with named ranges.

    		 
     

  • These are not available in Office 97 and were only introduced in Office 2000.

    		 

     © 2013 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next > | 02-Jan-2013