Category Archives: Utilities

Magic Numbers in Formulas

The last post referred to “magic numbers” and the pitfalls of using them in formulas. An example might be this product list, where the quality level is represented by a single digit before the dash in the part number, the “quality prefix.”

The formula generating the name in C2 is a simple one. It does a lookup of the quality prefix – 1, 2, or 3 – in the “Cutlery Lookup” table, yielding a quality of “Cheap,” “Nice” or “Best.” This is added to the product type, resulting in a name such as “Nice Spork.”

One day the proprietors realize these lackluster brand names are a drag on sales. They create new codes and names for the products, adding a 0 to the quality prefix and new quality descriptions to the table. They then print up 2,000 parts lists, failing to notice that the formula is still generating the same lousy names.

This is due to the magic number “1” in the “VLOOKUP(LEFT($A2,1)” part. It still specifies the length of the quality prefix as 1, meaning the lookup is still seeking the prefixes 1, 2 and 3. (Admittedly, their luck was bad in choosing new codes that didn’t result in #NA and in leaving in the old ones, but they were probably forced to by other bad design practices.)

A more robust approach is to have the formula look for the dash separating the quality code from the rest of the product number, like:

This will accommodate different length prefixes.

Magic Numbers and Prefixes in VBA

Prefixes in VBA can also lead to magic numbers. Say you have a workbook with some worksheets identified by the prefix “final” in the name. To process these sheets you might write code like:

Of course, it’s always dangerous to use the word “final” in a name because nothing’s ever finished. So the next week when the “Really Final” worksheets need to be processed, you change your code to:

and the Left function finds no sheet names whose first 5 letters are “Really Final.”

I’ve done something like this more than once, so I wrote a HasPrefix function to end it:

You pass it the string to check, along with the prefix you’re checking for (and whether it’s case-sensitive if you want). It uses the length of the prefix in the Left function, so the length won’t ever be wrong.

I used this recently to check whether a workbook was located in the AppData folder in the user profile, meaning it was most likely opened from an email:

(Environ is a handy Windows function for checking on your computer’s settings and JP has an informative article on it.)