How to Get the String Values of an Enum in VBA

Getting the string values for enums in VB.NET is easy using the built-in ToString method. What's the VBA equivalent?

How to Get the String Values of an Enum in VBA

Let's say you have a custom enumerated type for logging debugging messages in your application.  

Using my preferred enum naming convention, it might look like this:

Enum ll__LogLevel
End Enum

Now, let's say you want to include that information when logging events.  Here's the signature for a sample function that builds a string to be logged in a text file:

Function BuildLogText(Level As ll__LogLevel, Msg As String) As String

Here's what we would want a function like that to return:

?BuildLogText(ll_Error, "Out of memory")
2024-06-25 22:32 ERROR: Out of memory

Reflection: VB.NET's Secret Weapon

In VB.NET, we could use the built-in .ToString method of an enumerated value to get its string equivalent.  This feature relies on "reflection," which allows an executing application to inspect its own source code at runtime.  The .NET framework supports reflection; VBA does not.

So, in VB.NET, we could write code like the following:

? "xx--" & ll__LogLevel.ll_Error.ToString & "--xx"

Doing It By Hand in VBA

Unfortunately (or's perspective changes with age and wisdom on these things), reflection is not a tool that's available to us in VBA.

So I like to solve this puzzle with:

  • A good naming convention
  • A Select Case statement
  • Judicious use of the statement separator (:) for enhanced readability
  • A Case Else catch-all to throw errors on unrecognized enum values

Naming Convention

I covered my naming convention for custom enum types in a previous article:

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

To save you from reading the above article, my custom enum types follow this naming convention:

  • starts with a 2- or 3-letter prefix
  • followed by two underscores
  • then a brief description of the purpose of the enum type

I have good reasons for doing it this way (read the article already!), but those are not important here.  What is important is that the convention we adopt for these functions should integrate well with our existing convention.

With that in mind, here's the naming convention for our function:

  • Start with a prefix of Enum
  • Followed by the description (e.g., LogLevel)
  • End with a suffix of ToString

Putting it all together, our function will be named: EnumLogLevelToString.

Using this naming convention makes it really easy to find the function that will be used to convert enum values to strings: just type Enum in the code, press Ctrl+Space, and a list of all the EnumXxxToString() functions will be right there.

Sample Code

Here's the full sample code for this particular function:

Function LogLevelAsString(LogLevel As ll__LogLevel) As String
    Select Case LogLevel
    Case ll_Trace: LogLevelAsString = "TRACE"
    Case ll_Debug: LogLevelAsString = "DEBUG"
    Case ll_Info: LogLevelAsString = "INFO"
    Case ll_Warn: LogLevelAsString = "WARN"
    Case ll_Error: LogLevelAsString = "ERROR"
    Case ll_Fatal: LogLevelAsString = "FATAL"
    Case Else: Throw "Unexpected LogLevel: {0}", LogLevel
    End Select
End Function

Obviously, the literal string values will vary from one enum function to another, but the formatting and structure should remain the same.

Additional Reading

Throwing Errors in VBA
Introducing a frictionless alternative to Err.Raise.
When–And When Not–To Use the Statement Separator Character in VBA
Did you know you can combine lines of code in VBA with the colon character? The key is to understand when--and when not--to use it.
Defensive Programming
Don’t build digital Maginot Lines. Program your defenses in depth.

Cover image generated by StableDiffusionXL.

UPDATE [2024-07-05]: Fix typo in immediate window output of VB.NET example (h/t Tom van Stiphout).

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