A Prefix Function to Save You From VBA Magic Numbers, Sometimes

A Prefix Function to Save You From VBA Magic Numbers, Sometimes

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.”

lookup formula with magic number

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.”

=VLOOKUP(LEFT($A2,1),CutleryLookup,2,FALSE) & " " &B2

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.

new quality prefix - same 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:

=VLOOKUP(LEFT($A2,SEARCH("-",$A2)-1),CutleryLookup,2,FALSE) & " " &B2

lookup formula with calculated number

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:

Dim ws As Excel.Worksheet
For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 5) = "Final" Then
        ProcessSheet ws
    End If
Next ws

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:

If Left(ws.Name, 5) = "Really Final" Then

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:

Function HasPrefix(StringToCheck, Prefix, Optional CaseSensitive As Boolean = False) As Boolean
If CaseSensitive Then
    HasPrefix = Left((StringToCheck), Len(Prefix)) = Prefix
Else
    HasPrefix = Left(LCase(StringToCheck), Len(Prefix)) = LCase(Prefix)
End If
End Function

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:

If HasPrefix(ActiveWorkbook.FullName, Environ("LOCALAPPDATA")) then

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

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>