This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Using enumerations in VBA
Excel and other MS Office applications make extensive use of enumerations - and you should too! 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

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

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!

Some other pages relevant to the above blog include:

This blog has 1 thread

Add a new post

From:	Gian Luca
When:	23 Oct 18 at 18:57

Reply to Gian Luca

Hi , can an enum set of data be utilized to fill a userform list in VBA?

From:	Andy B
When:	24 Oct 18 at 09:54

Reply to Andy B

A good question! The answer is yes. This blog shows how to loop over enumerations, and this one how to populate a combo box (read the "Hard Way" section). Put these two together and you have the solution! If you get it working feel free to post the code here so others can benefit.

From:	Gian Luca
When:	25 Oct 18 at 20:56

Reply to Gian Luca

Thanks Andy, it worked fine. I am just at the point where I would like to convert Enum into text and, since I am using a class object, I have found useful guidelines in the video "VBA - classes and structures videos | Excel VBA Part 35 - Class Modules". Just not sure how to work the let property to write values. Here below is an example which unfortunately does not work as the listbox linked returns only numbers, could you please advise on the correct use of the Let Property in this case? Also was wondering if you could give examples of how to use class objects to copy data from one worksheet to another.

Private pFruit as Fruit

Public Enum Fruit
        Apple
        Banana
        Kiwi
        Orange
End Enum

Public Property Get FruitText() As String

       Select Case pMCase
Case Fruit.Apple
                FruitText = "Apple"
            Case Fruit.Banana
                FruitText = "Banana"
            Case Fruit.Kiwi
                FruitText = "Kiwi"
            Case Fruit.Orange
                FruitText ="Orange"
           Case Else
                FruitText = "Invalid Case'
        End Select
    End Property

    Public Property Let pFruit (Value As String)
        pFruit = Value
    End Property

From:	Andy B
When:	26 Oct 18 at 19:12

Reply to Andy B

Unfortunately I don't have time to try this out, but I'll leave the post open in case someone else would like to pick up the challenge?

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Creating and Using Enumerations

Built-in Enumerations

Examples of Custom Enumerations

How Enumerations are Stored

Head office

London

Manchester