An Excel Tutorial:
Corporate VBA Standards
For Excel Users Who Program
Even if you're an amateur programmer, it makes sense to
your VBA programs. Here are four easy ways to help
others know what your VBA programs are trying to
do, and why.
by Charley Kyd
(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
- "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
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
'' Called by: PrintControl
'' Call: DoMemoData wbkReport,
'' Arguments: wbkReport--Name of the report workbook
oStopRow--Number of the last row to process
'' Comments: (1) RunReport initializes the m_oMemoRowNum
(2) wksMemo doesn't need to be static. And
it's over-defined. Fix this at some
'' Date Programmer
'' 7/3/96 Charley Kyd Written
'' 8/28/97 Charley Kyd Re-set memo object.
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
sDateFormula = m_wbkReport.Worksheets(1).
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
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
Showing the Scope of a Variable
In VBA, a
variable can have one of three scopes.
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
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
To illustrate, the first two lines in a module might be:
Dim m_wbkReport as Workbook
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:
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
|b or bln
|n or int
|l or lng
|v or vnt
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
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.