VBA

Improve the Readability of Numeric Settings in VBA

In VBA, numeric property settings (such as ComboBox.TextAlign–where 3 = "Right") can be handled in many ways, but some ways are more right than others.

Mike Wolfe

Dec 7, 2023 • 6 min read

In my clsImExSpecs presentation yesterday, I demonstrated how I use a custom class module to provide developer quality-of-life improvements when importing from–and exporting data to–text files.

Rather than having to switch contexts between code and a focus-hoarding wizard interface, you can easily view and configure your import/export specifications right in VBA. To do this, my class module includes property getters and setters that provide safe access to the specification settings stored in the MSysIMEXSpecs and MSysIMEXColumns system tables.

Some of those settings are stored in integer fieds, such as the Date Order:

DateOrder: a number that represents the date order as shown here:
DMY = 0
DYM = 1
MDY = 2
MYD = 3
YDM = 4
YMD = 5

This presents an interesting question: what is the best way to handle number-based settings values in VBA?

Literal Numbers: The Worst Approach

The first, and most obvious, approach is to use the numeric values themselves.

With this approach, I could have implemented support for this field like so:

Public Property Let DateOrder(Value As Integer)
    mSpecAtt.DateOrder = Value
End Property

Then, in the calling code, users of the class could set a specification's date order to Month/Day/Year with the following code:

Spec.DateOrder = 2

The computer will be perfectly happy with that code, but it's not very readable. Remember, good programmers write code the computer can read; great programmers write code that humans can read. Readability is critically important, especially to the maintainability of a project.

Literal Numbers...But With Good Comments

Of course, a conscientious consumer of the class could document his work with a well-placed code comment, like so:

Spec.DateOrder = 2   'Month/Day/Year order

That's definitely an improvement, but it's far from fool-proof.

It relies on whoever's writing the calling code to add the comment.
It requires the person calling the code to figure out which number goes with which date order sequence.
It could go stale.

That last point is the most important one. Occasionally, good comments go bad:

What happens if the user switches to a different date order but forgets to update the comment?

Spec.DateOrder = 3   'Month/Day/Year order

Now the value and the comment are out of sync.

And if someone is eagle-eyed enough to even notice the mistake, how are they to know which one is correct? Is it the value of 3 (Month/Year/Day)? Or is it the value that the comment "Month/Day/Year order" implies (i.e., a value of 2)?

Constants: A Better Approach

Literal numbers used this way in code are often referred to as "magic numbers."

That's because the programmer appears to have conjured them from thin air, magically bringing them into existence and introducing them to the program. What semantic meaning does the number have? What is it meant to represent? No one really knows. It's magic.

The most common way of avoiding the "magic number" code smell is to assign the value to a constant. For example:

Const MonthDayYearOrder As Integer = 2
Const MonthYearDayOrder As Integer = 3

Spec.DateOrder = MonthDayYearOrder

It's now crystal clear what is happening even without a comment. This is what's meant by "self-documenting" code. And because there's no code comment, there's no code comment to get stale.

While the example above shows a local comment defined by the calling code, I could easily make those comments public constants in the header of my clsImExSpecs class module.

I do not use constants for this, though.

Constants have some downsides in this situation:

Public constants pollute the global namespace. To be useful to the calling code, these class module constants would need to be globally public. But that would just add to the amount of clutter in the global namespace, which is already overwhelming in VBA.
They offer no discoverability. You would have to know where to look for them when writing your code. So, while constants improve the readability of your code, they do nothing to help the writability of your code.
They require excessively verbose identifiers. I mean, sure, you could use a shorter name for your constant, such as MDY. But without the additional context that the constant goes with the DateOrder property, it's too ambiguous. Heck, if I was coming back to my own code in six months and saw a constant named MDY, I would assume it was defining a date format string, like "m/d/yyyy". A number is about the last value I would expect it to hold.

Let's address those shortcomings with my preferred solution: custom enum types.

Enums: The Best Approach

Any time I have a finite set of numeric configuration values, especially in one of my class modules, I like to define a custom enumerated type to document them.

Here's the DateOrder values as defined in my clsImExSpecs class module:

Public Enum imexDateOrder
    ies_DMY = 0
    ies_DYM
    ies_MDY
    ies_MYD
    ies_YDM
    ies_YMD
End Enum

Public Property Get DateOrder() As imexDateOrder
    DateOrder = mSpecAtt.DateOrder
End Property

Public Property Let DateOrder(Value As imexDateOrder)
    mSpecAtt.DateOrder = Value
    UpdateSpecAttributes
End Property

Now, the class module is very old. I first wrote it more than ten years ago, so there are a couple things I would change if I were writing this from scratch now:

Use my Enum Naming Convention
Explicitly assign values to each member of the enum

To be clear, I don't "explicitly assign values to each member" for every enum I define. In fact, I rarely do it when working with custom enums. The rule of thumb I go by is to explicitly include the enum values if the values themselves have meaning, as they do in this case.

Here's an updated version:

Public Enum iedo_ImExDateOrder
    iedo_DMY = 0
    iedo_DYM = 1
    iedo_MDY = 2
    iedo_MYD = 3
    iedo_YDM = 4
    iedo_YMD = 5
End Enum

So, why is the custom enumerated type better than using constants in this situation?

Mainly, because it addresses the three drawbacks of the constant approach:

Enums are highly discoverable. By changing the data type of the class's DateOrder property, IntelliSense will automatically drop down the six date order options when the user is writing code.
They barely pollute the global namespace. Technically, the individual enum values occupy the same scope and extent as public constants. However, the prefix-underscore naming convention that I reserve for enum values makes it almost as if the values were not in the global namespace. In fact, that's a core part of the reason I settled on the enum type naming convention that I did.
We can use shorter names. The names of the enum members only have to be different enough to distinguish themselves from each other–not every other identifier in the global namespace. The surrounding context saves us from having to be overly explicit about what the value represents.

Enums Don't Have to Start at Zero

Enums can be assigned any arbitrary long integer value.

For example, I use an enum type to set the custom specification's Code Page as shown here:

'This enum is a list of commonly used "FileType" field values (i.e., code pages);
'   they correspond to the Code Page drop down in the Specification settings dialog box
'This is not intended to be an exhaustive list; rather, you should add to it
'   as needed over time
'IMPORTANT: Any additions that you make to this Enum require associated changes
'   to the FileTypeToString() function found below in this class module
Public Enum imexFileType
    'CodePage 437 is OEM United States; see http://msdn.microsoft.com/en-us/library/aa752010.aspx
    '  usually shown as "ANSI" encoding in Notepad++ because 437 is usually the active Windows code page; see: http://stackoverflow.com/a/21317382
    ies_EncodeOEM_US = 437          'Appears as "OEM United States" in the Code Page dropdown
    ies_EncodeUTF8 = -535           'Appears as "Unicode (UTF-8)" in the Code Page dropdown
    ies_EncodeUCS2_LE_BOM = 1200    'Appears as "Unicode" in the Code Page dropdown
    ies_EncodeWin1252 = 1252        'Appears as "Western European (Windows)" in the Code Page dropdown
End Enum

A couple notes about the above code:

FileType is the name of the MSysIMEXSpecs column that holds the Code Page value.
The numbers above were discovered/confirmed via testing with the Import/Export Specification wizard.
Rather than document every possible value here, I intentionally limit the list to only those values I actually have used. Keeping the signal-to-noise ratio high is just as important as making the individual values discoverable.
My non-US readers will almost certainly have a different set of commonly used code page values than what I show here.

Acknowledgements

Cover image generated by DALL-E-3