BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog is part of our Excel VBA tutorial blog. Alternatively, why not book onto one of our introductory or advanced training courses in VBA?
Posted by Andy Brown on 24 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Creating and Using Enumerations
Built-in Enumerations
If you've been programming for any length of time, you've already used enumerations hundreds of times. For example, to go to the bottom of a block of cells in Excel:
You can go in 4 possible directions: down, left, right or up.
Enumerations in Microsoft Office have the following prefixes:
| Application | Prefix |
|---|---|
| Access | ac |
| Excel | xl |
| FrontPage | fp |
| Outlook | ol |
| PowerPoint | pp |
| Word | wd |
| VBA general | vb |
In the example above, the values xlDown, xlToLeft, xlToRight and xlUp correspond to internally stored numbers, but it's much easier to use the names. However, to prove the point, the following routine:
Sub ListEnum()
'show the values of the xlDirection enumeration
Debug.Print "xlDown = " & XlDirection.xlDown
Debug.Print "xlUp = " & XlDirection.xlUp
Debug.Print "xlToLeft = " & XlDirection.xlToLeft
Debug.Print "xlToRight = " & XlDirection.xlToRight
End Sub
would show the value of each enumeration element:
So instead of using xlDown, you could use -4121 instead!
Microsoft obviously think it's a good idea to group related numbers like this - and so should you!
Examples of Custom Enumerations
As shown above, an enumeration is a way of grouping integer variables together under a single umbrella. Here are some examples of enumerations you could create:
'enumeration for sex of child
Enum Gender
Boy = 0
Girl = 1
End Enum
'enumeration for days of week
Enum DayOfWeek
Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7
End Enum
'enumeration for types of mortgage
Enum MortgageType
Repayment = 1
Endowment = 2
Pension = 3
Other = 4
End Enum
The beauty of enumerations is that they make it harder to make mistakes in code:
You could refer to the wrong mortgage type in this example, but it would be harder.
How Enumerations are Stored
An enumeration in VBA is always of data type Long. If you don't specify any numbers, they'll start from 0. So:
'enumeration for sex of child
Enum Gender
Boy = 0
Girl = 1
End Enum
is exactly the same as:
'enumeration for sex of child
Enum Gender
Boy
Girl
End Enum
'the tax rates in Ruritania
Enum TaxRate
LowestBand = 20
MiddleBand = 40
UpperBand = 55
End Enum
Use enumerations whenever you have a finite site of possible integer values - they make bugs less likely!