Enums in VBA: A Great 10-Minute Introductory Video

Excel MVP Paul Kelly packs a bunch of great information (including a well-hidden feature) into this ten-minute video introducing Enums in VBA.

Enums in VBA: A Great 10-Minute Introductory Video

Enumerated types--better known as enums–are a great tool in VBA to write clean, self-documenting code.

Microsoft MVP Paul Kelly posted a great introductory video: Enums: The Key to Cleaner, More Efficient VBA Code:

Good for Beginners

The video does a great job of introducing the concept of enums in VBA, so it's a great starting point if you don't know what enums are or why you'd want to use them.

Good for Veterans

Paul also goes into a few more advanced enum-related topics, including the handy [_First] / [_Last] technique.  If you're familiar with enums, but you've never seen that unusual syntax, the video will be well worth your time for that section alone.

A Small Difference of Opinion

One area that I use a slightly different approach than Paul is when it comes to naming.  My preferred enum naming convention is optimized for the cleanest possible IntelliSense.  You can read about that here: Enum Type Naming Convention.

Enum Type Naming Convention
The combination of “IntelliSense overload” and “global identifier case changes” convinced me I needed a different approach.

Of course, naming conventions are very much a personal preference.  You're as likely to prefer Paul's convention as you are mine.  I recommend you watch the video for his take, read my article for my take, and then decide for yourself which one you like best.

Old Dogs Can Learn New Tricks: Excel Columns

While most of the video was a review of concepts and techniques I was already familiar with, the final section on using enums to represent Excel column numbers was a clever use case that I hadn't seen before.  

The autonumbering nature of enums works particularly well in that situation.  You can insert (or remove) a new enum value as columns are added and removed in Excel without having to manually renumber all the subsequent columns in VBA the way you would if you used constants instead.

Top-Notch Production Quality

The icing on the cake is that Paul does a great job with the quality of the video itself.  It's well edited and well scripted.  The end result is a video packed with great content and no wasted time.

I highly recommend it.

Cover image created with Microsoft Designer; Image by Eduardo Saint Jean from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0