This page has 1 thread | Add post

Blogs

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

How to loop over enumerations in VBA
A short blog explaining how to use Visual Basic for Applications to loop over enumerations.

Posted by Andy Brown on 07 November 2013

Looping over enumerations in VBA

I've blogged previously about enumerations as part of our Excel VBA tutorial, but never knew you could loop over them!

This blog was inspired by someone describing Andrew - a fellow blogger at Wise Owl - as "brilliant, fantastic, amazing, and all enumerations on those terms!".

Here's how to do this. First create your enumeration:

Public Enum AndrewQuality

Brilliant

Fantastic

Amazing

End Enum

Now loop over it from beginning to end:

Sub DescribeAndrew()

'variable to represent each quality

Dim q As AndrewQuality

'variable to hold final message

Dim msg As String

'loop over qualities

msg = "Qualities of Andrew are:" & vbCrLf

For q = Brilliant To Amazing

msg = msg & vbCrLf & q

Next q

'display the final qualities list

MsgBox msg

End Sub

When you run the DescribeAndrew procedure above, you'll get a message displaying his alleged qualities:

The message box lists out the numerical values of Andrew's qualities.

Unfortunately there's no way in VBA of showing the text equivalent for these numerical values, apart from writing your own SELECT CASE statement to return the right text value for each number.

For even more tricks with enumerations, go to (who else?) Chip Pearson.

Some other pages relevant to the above blog include:

This blog has 1 thread

Add a new post

From:	nimrod1313
When:	11 Apr 19 at 18:22

Reply to nimrod1313

Your sample code IS NOT iterating through the values of the enum. All the code does is iterate through all the numbers that exist between Brilliant ( i.e.0) and Amazing (i.e. 2).

To prove this assign values to the enums like brilliant = 1 , fantastic = 5 , Amazing = 10. Now when you run the code you should get a return of 1,5,10 BUT YOU DON'T you get a return of 1,2,3,4,5,6,7,8,9,10. So it's not iterating through the 3 enum values it's just spitting out numbers. between 1 and 10.

From:	Andy B
When:	12 Apr 19 at 10:21

Reply to Andy B

I must admit that's what I had meant, but thanks for clarifying this as I obviously hadn't made myself clear. I must admit in 30+ years of programming I've never had call to do this, but it's nice to know it's possible!

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

Looping over enumerations in VBA

Head office

London

Manchester