562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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?
|
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!
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.
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!
Some other pages relevant to the above blog include:
From: | Gian Luca |
When: | 23 Oct 18 at 18:57 |
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 |
From: | Gian Luca |
When: | 25 Oct 18 at 20:56 |
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 |
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?
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.