VBA

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?

Mike Wolfe

Jun 25, 2024 • 3 min read

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
    ll_Trace
    ll_Debug
    ll_Info
    ll_Warn
    ll_Error
    ll_Fatal
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"
xx--ll_Error--xx

Doing It By Hand in VBA

Unfortunately (or fortunately...one'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:

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.