560 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
|How to loop over enumerations in VBA|
|A short blog explaining how to use Visual Basic for Applications to loop over enumerations.|
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
Now loop over it from beginning to end:
'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
'display the final qualities list
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.
|When:||11 Apr 19 at 18:22|
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.
|When:||12 Apr 19 at 10:21|
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!
25 Aytoun Street