This page has 0 threads | Add post

Creating and Using Enumerations

Written by Andy Brown

In this tutorial

Built-in Enumerations
Examples of Custom Enumerations
How Enumerations are Stored

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

Nor do enumeration values need to be consecutive. For example:

'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!

Previous
tutorial

Passing arguments and creating functions

Previous
part

Arrays

Top of page

Next
part

Next
tutorial

Working with forms

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on VBA

Other data structures

Creating and Using Enumerations

Built-in Enumerations

Examples of Custom Enumerations

How Enumerations are Stored

Head office

London

Manchester