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

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.

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 listing enum values

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.

This blog has 0 threads Add post