(Update: To see an illustration of the points I discussed here, see Read a Text File with VBA, And Write the Text to Excel.)
In most companies, it’s common for Excel users to write VBA programs. It’s common for these programs to be…well…less than perfect. And it’s common for these programs to be undocumented.
This lack of documentation often makes it impossible for others — or even the programmer, after a period of time — to figure out what the program is trying to do, why it’s trying to do it, how it’s expected to work, and how one might fix any problems with the code.
Managers probably can’t prevent their Excel users from writing VBA programs. But it is possible for managers to require that Excel users write programs that others can read. Not only is it possible, it’s necessary for the business, and for the programmer.
- “We can’t transfer him,” one manager told me. “Our department relies on the Excel programs he’s written, and no one else understands his code.”
- “You mean you’re still using that stuff?” a new manager asked. Ten years earlier he had been an analyst in the department he now headed. His first challenge was to debug and document his old macros, which the young analysts treated as holy writ.
- “Gimme a minute…” the analyst said, staring at the macro she had written six months earlier. From the look on her face, she was trying to read a foreign language. There was no chance that she would find the bug in her code any time soon.
This article describes four VBA standards that are easy to learn and maintain. If you follow them, your VBA code will be much easier for you to understand in the future, and for others to understand at any time.
Use Option Explicit
As most VBA programmers know, variables can contain many types of data: strings, integers, currency, and so on. By default, VBA does not require that you specify the type of data that each variable is expected to contain. That is, by default, VBA defines all variables using the Variant data type.
This creates several problems. One minor problem is performance. Variants require more memory than other common data types, and they can take slightly longer to process.
A more serious problem is that using only variants in your code makes it difficult to trap errors. For example, suppose you have a variable called MyMonth. You intend for it always to contain a date serial number. But because it’s a variant it also could contain text, like “January”. Depending on how you use MyMonth in your code, this error could go unnoticed.
The worst problem is that by using variants by default, people can’t read your code and easily learn the nature of each variable. Your code starts to read like a foreign novel.
To get around this problem, go to the Visual Basic Editor (VBE). Choose Tools, Options. In the Editor tab, make sure that Require Variable Declaration is checked.
After you check this option, each new VBA module that you launch will begin with “Option Explicit”. This tells Excel that all VBA variables must be explicitly declared. That is, with this option checked, you can’t just start to use a variable, you’ve got to declare each variable, like this:
Dim MyMonth As Date
Dim MyMonthName as String
By formally dimensioning your variables, and by using the Variant data type rarely or never, you reduce the chance for errors in your code and you make your program more readable.
Use Title Blocks for Each Macro
When you write a VBA macro, it’s always a good idea to label it with key information. Here’s a title block from an old VBA program of mine. The figure shows a format that I still use frequently:
| ”======================================================= ” Program: DoMemoData ” Desc: Writes memo data to the memo sheet ” Called by: PrintControl ” Call: DoMemoData wbkReport, oStopRow ” Arguments: wbkReport–Name of the report workbook ” oStopRow–Number of the last row to process ” Comments: (1) RunReport initializes the m_oMemoRowNum ” variable ” (2) wksMemo doesn’t need to be static. And ” it’s over-defined. Fix this at some ” point. ” Changes———————————————- ” Date Programmer Change ” 7/3/96 Charley Kyd Written ” 8/28/97 Charley Kyd Re-set memo object. This is ” needed at times in Excel 8 ” when the report workbook must ” close then re-open. ”======================================================= Sub DoMemoData(wbkReport As Workbook, oStopRow As Long) |
Notice here that the title block is immediately above the first line of the macro.
Depending on your program and your business environment, your title block could contain either greater or fewer categories of information. In a corporate environment, for example, the programmer’s phone number also would be included.
Creating and maintaining a title block takes self discipline. But I know from personal experience that when I don’t use an approach like this I frequently regret it months later when I try to remember what each routine was supposed to be doing, and why.
Use In-Line Comments
Every macro needs in-line comments, as shown here:
| ”If this routine was called by the batch routine… If g_bCalledByBatch Then ”Get the reference of the changing date cell sDateRef = GetNameVal(“ChgDateCell”, 0, g_nReference) ”If the date name is empty, return null sDateFormula If sDateRef = g_sNull Then sDateFormula = g_sNull ”Else, get the beginning formula in the date cell Else sDateFormula = m_wbkReport.Worksheets(1). _ Evaluate(sDateRef).Formula End If Else |
Often, when I write macros, I’ll begin with comments like those in green, and then write the code described by the comments. By following this approach I can think first about what the program is supposed to achieve without getting bogged down in VBA syntax.
On the other hand, if I begin by recording a new macro, I immediately clean up and comment the code that Excel generates. Doing so forces me to understand what the recorded code does. And it saves me from having to re-learn that same lesson in the future after my memory about those lines of code has started to fade.
By the way, the comments shown above certainly aren’t outstanding. I’m sure you could write more informative comments in your own code. But even cryptic comments are better than no comments at all.
Use Hungarian Notation
In the early days of DOS, Microsoft’s Chief Architect at the time, Dr. Charles Simonyi, introduced a very useful way of naming variables. This method came to be known as Hungarian Notation.
The method adds a prefix to each variable, a prefix that identifies the type of variable. Also, the method is used frequently to identify the variable’s scope.
Showing the Scope of a Variable
In VBA, a variable can have one of three scopes.
Local Scope
These variables can be used only within the macro in which they are defined. This is the most common type of variable, and it requires no special designator.
Module Scope
These variables can be used only within the module for which they are defined. That is, one macro in the module can assign a value to one of these variables and another macro in that module can use that value.
These variables are defined using a Dim statement at the top of the module, outside of a macro routine. By convention, their name uses the prefix “m_”.
To illustrate, the first two lines in a module might be:
Option Explicit
Dim m_wbkReport as Workbook
Global Scope
These variables can be used in any module in the workbook. They are defined outside of a macro and use the “Public” key word. They use the “g_” prefix, for “global”.
To illustrate, if the wbkReport variable were global, the first two lines in a module might be:
Option Explicit
Public g_wbkReport as Workbook
Showing the Type of the Variable
The previous examples use “wbk” to let us know that the Report variable contains a workbook object. This illustrates how Hungarian notation specifies the type of the variable. Similarly, if a local variable were to contain the name of a report, its variable name might be sReport. If this were a global variable, its name would be g_sReport.
Notice that both the scope and type information are in lower case, and the normal variable name is in proper (upper-lower) case.
Once you become accustomed to working with variables that use Hungarian notation, it’s hard to imagine using any other naming method. When you read through any VBA code you automatically know the type and scope of each of its variables. This information can tell you a great deal about a program that you’re seeing for the first time.
Different programmers can use different designators to specify the type of variable. Here are some common prefixes:
|
|
|||
| s or str | String | rng | Range |
| b or bln | Boolean | wks | Worksheet |
| n or int | Integer | wbk | Workbook |
| l or lng | Long | cht | Chart |
| v or vnt | Variant | shp | Shape |
|
|
|||
To find other common prefixes and descriptions for Hungarian notation, search Microsoft.com for those two words. If you want to work from a shorter list of results, add “variable” to the search terms. If you want to see even more choices, search the entire web for Hungarian notation. Many programmers have posted their own versions of this naming method.
Within reason, it doesn’t matter which prefix your company uses to specify the type of variable. The prefix merely needs to be consistent, intuitive, and relatively short. That is, after a short introduction to Hungarian notation, even new programmers should be able to read the notation easily.
Conclusion
Let’s be honest. If you’re an Excel user — not a professional developer — you’ll probably document your code only if it’s easy to do and if it’s personally helpful. In my experience, the four techniques shown here all meet that standard.
Give them a try. These methods will save you work in the long run.
