First, a few definitions. A constant is anything that doesn't change -- essentially, an invariant value. The number 1 is a constant, as is the text string "Hello," and the boolean value TRUE. In the VBA code fragment SpecialCells(4), 4 is a constant. While one could use the number 4 as the argument value for the SpecialCells method, it is hard to remember what it represents. It would really aid understanding if we could substitute a more meaningful token, say, xlCellTypeBlanks.
This kind of token -- something that refers to a constant value -- is known by a few different names. Some, like Microsoft, refer to it as a constant. Others refer to it as a constant name. I prefer what I learnt in my introductory Computer Science classes -- mnemonic. The definition of a mnemonic is "something that aids memory," which is what these types of tokens are used for in software development. So, once xlCellTypeBlanks is defined as the same as the invariant 4, it becomes a mnemonic.
Second, let's look at why we have rules for writing programs. Three major reasons come to mind. In order of high to low importance, they are:
1 and 2 have a lot of overlap but, of course, there are differences. 3, on the other hand, often is at odds with the other two.
Effectively, this is what it comes down to. What rules can we develop vis-�-vis constants and mnemonics so that they fit the framework of the rules?
Simply stated, it all collapses into a single cause: When a constant is obvious and used once, it should be used directly. If it is repeated, or if it is not obvious, it should be a mnemonic. The rest of this note illustrates these points.
I) Consider the example, which is something one tends to see in sample code. Unfortunately, it supports neither of the 3 reasons mentioned above and is just plain silly.
sMsg="Hello" sTitle="Just Saying Hello" MsgBox sMsg, sTitle
Unfortunately, many programmers and any number of MS examples do just what the above code does. I just shake my head and move on. The next variant to consider is
MsgBox "Hello", ,"Just saying hello"
This works just fine but leaves the reader with some doubt about the missing argument. Were the two commas intentional or was one a mistake? In the case of the MsgBox function it is -- or at least it should be -- obvious to anyone with any experience with VBA. However, for less common functions there may be nothing obvious about missing arguments.
To be rather strict about rule 2, the next variant makes the intent absolutely obvious.
MsgBox "Hello", Title:="Just saying hello"
OK, so when do we need a mnemonic? Consider the scenario in which a marketing requirement is a consistent start to the caption for every dialog box, say, TM Consulting Services. Now, it would be nothing short of dumb to code
MsgBox "Hello", ,"TM Consulting Services: Just Saying Hello"
I would expect that every project would have a single all-encompassing definition at the top of one non-private module.
Public Const cTUCHeading As String = "TM Consulting Services: "
Then, saying hello would become
II) Use a mnemonic for a constant that is not obvious -- even if that constant value is not repeated anywhere else in the program.MsgBox "Hello", Title:= cTUCHeading & "Just Saying Hello"
I'll illustrate this through the use of values representing various combinations for the Buttons argument of the MsgBox function. As we will see, sadly, math and programmer intent don't always go together. Suppose one reads:
MsgBox "Hello", 36, Title:= TUCHeading & ": Just Saying Hello"
How did the 36 come about? I've been programming with VBA since the first release of Excel that supported it and I have no idea what that number means. It should be obvious to the reader, but it is not. Even if one looks up help, one is still at a loss as to the programmer's intent!
Did the programmer mean
vbQuestion + vbYesNo
or was the programmer's intent, albeit erroneous, to get Excel to display four buttons and one icon with
vbQuestion + vbYesNoCancel + vbOKCancel
How about
MsgBox "Hello", 48, Title:= TUCHeading & ": Just Saying Hello"
Was the intent to get an exclamation icon or did the programmer hope to get two icons for a critical question?
To me, the use of a constant for the Buttons argument in the MsgBox function is foolhardy. A mnemonic is clearly the way to go.
Consider the various methods and properties of the objects in the Excel hierarchy. In each case where a particular argument values are restricted to a predetermined list, Microsoft has defined constant names. For example, in the case of the SpecialCells method of the Range object, the
