Writing Solid VBA Code

Most of us begin programming in Visual Basic for Applications (VBA) in the same way: we start by
experimenting with macros in Word or Excel and then move on to modifying those macros using the
Visual Basic Editor.

Modifying macro code is a great starting point. If you're not sure how to write your own routine to
open a specific file, for instance, you can record a couple of macros to open files—specifying
different options in each case—and then study the resulting code.

There are some dangers, though, in learning VBA this way. Macro code is not designed to be
efficient nor does it use many of VBA's advanced features. The macro-and-modify approach to
coding is also not conducive to establishing good programming techniques and producing solid code.

Why should you bother about programming techniques? After all, if the code gets the job done, isn't
that sufficient?

If all you want to do is create small routines to solve occasional problems that crop up, then yes, it
is sufficient. If, however, you want to develop your programming skills, make your code more
reliable, and build a library of useful and reusable routines, then no, it's woefully inadequate.

What is solid code?

Solid code is code that:

 is easy to read and easy to maintain;

 is reusable by you and by others;
 performs as expected, each time.

The third point—creating code that performs as expected—requires experience, careful planning,
testing and debugging, things beyond the scope of this article. What we'll focus on is creating code
that is easy to read, easy to maintain and reusable.

A practical example

Rather than talk in the abstract, let's take a practical example which you can use yourself. While the
example is for Microsoft Word 2000, most of what I say is not only applicable to using VBA in the
other Office 2000 products, but also to creating programs in other versions of VBA and in other
languages. I'm going to assume you already know how to create a program using the Visual Basic
Editor.

Take a look at the code in Figure 1. This is a short VBA procedure that deletes the currently active
document. Before we discuss the solidity or otherwise of this piece of code, let's step through it
quickly to see what it does.

Sub DAD()
If Len(ActiveDocument.path) <> 0 Then
ActiveDocument.Close SaveChanges:=False
Kill ActiveDocument.FullName
Else
ActiveDocument.Close SaveChanges:=False
End If
End Sub

Figure 1. An example of poor code which nevertheless does its job.

The DAD() routine first checks to see whether the active document has ever been saved. It does so
by checking the length of the ActiveDocument.Path property. If the document has been saved, it
will have a filename which is accessible using the ActiveDocument.Path property. If, on the other
hand, it has never been saved, the ActiveDocument.Path property will contain a zero-length string.

If the document has been saved, we need to close it without saving any changes and then delete
the saved file from the disk. If the document has never been saved, all we need to do is close it
without saving any changes. That's all there is to it.

If you're game, you can test the DAD procedure by copying it into the Visual Basic Editor in Word
and then running it. Take care! It works without warning and you can't recover the deleted file, so
make sure you try it only on a document that you don't need.

DAD's a dud

DAD has only two things going for it: it's short and it works. Even though it successfully performs its
function, it really is an abysmal piece of code. It's hard to read, its purpose isn't clear and it works
only under ideal conditions.

What happens, for instance, if you try to run DAD when there's no document open? It will fail. What
happens if a user runs DAD and then suddenly realises they don't want to delete the open file after
all? Tough luck! There's no way for the user to back out, and the results are permanent.

DAD is an example of a working program that is bad code.

Cosmetic changes

So what can we do to transform it into good code?

A lot can be done with purely cosmetic changes. We can make DAD readable by indenting our code
structures and adding some white space. This will make it much easier to determine what's going on
in the program. In such a short program, it may not be a big deal, but proper formatting is vital
when you write complex routines with nested control structures.

Here are a few formatting guidelines:

 Indent all code and comments within a procedure.

 Declare only one variable on a line and indent each variable's data-type specifier (such as
Integer, String and so on) so that the data types are aligned.
 Indent control structures—such as If…End If, For…Next, and so on—at least one tab stop,
and indent the code within the code structure an additional tab stop.
 Break long lines of code by using the underscore (_) continuation character, so you don't
have to scroll sideways to see the complete code. Line up second and subsequent continued
lines by indenting them an additional tab stop to create a visual cue that the lines belong
together.
 Separate control structures by adding a blank line before and after.
Sub DAD()

If Len(ActiveDocument.path) <> 0 Then

ActiveDocument.Close SaveChanges:=False
Kill ActiveDocument.FullName

Else

ActiveDocument.Close SaveChanges:=False

End If

End Sub

Figure 2. A little formatting makes DAD more readable.

Commenting your code

Comments are plain English descriptions of what your code is doing. Comments make it easy for
you to debug, maintain and update your code. It's amazing how often you'll return to a program
you wrote a month or a year ago and wonder what on earth you were doing in it.

Comments also make it easier for others to understand what your code is doing, which is vital if you
wish to collaborate on projects or if you write programs for other people.

When writing comments, you should try to give an overview of the process and tasks, rather than
rephrasing each step of your code. After all, the code may change as you revise the program, but
the overall tasks are likely to remain unchanged. If you've done something particularly tricky in
your code, you may wish to write an overall 'task' comment followed by a comment which describes
the technique you've used.

Adding comments to your code may seem like a bore, but it is one of the best ways to ensure your
code is of enduring value. And, if you adopt the right attitude, commenting your code can be
immensely satisfying and can facilitate the actual writing of your programs.

How to comment your code

Many people write their code and then come back when they've finished and add comments. You
should reverse this approach: add your comments as you code. If you leave commenting till later,
chances are you'll never do it or you'll do it half-heartedly, and it will take longer because you'll
have to examine the code first to work out what's going on.

Some programmers write all their comments first and then go back later to add the code. In this
way, the comments provide a blueprint for the program.

In VBA, all you need to do to create a comment is place a single quotation mark (') at the beginning
of a line. Anything else you write on that line will be regarded as a comment. I like to add a border
line consisting of a quotation mark followed by a string of asterisks (*) to set all my comments off
from the rest of my code. This makes them particularly easy to find in a long program.

Commenting DAD
With any program, it pays to put a comment at the beginning of the routine that explains its
purpose. You may also want to include information such as the date it was created or last revised,
the author, and any routines it calls. Our DAD routine is very brief, so we'll limit our opening
comment to a brief explanation of the routine's function.

We'll also comment the one control structure used (the IF...THEN...ELSE...END IF), to explain the
logic used. You may choose to put all the comments pertaining to the control structure in one
comment block at the beginning of the structure. I've chosen to break the comments up into logical
groups that describe the code immediately following. This is particularly useful in large programs.
So, you'll find a comment before the IF describing the condition we're testing for, a comment
immediately after the THEN describing the course of action to take if the IF condition is matched,
and a comment immediately before the ELSE statement describing the course of action to take if the
IF condition is not met.

Note how I've indented the comments so they align with the code to which they refer.

Sub DAD()

' ************
' This routine deletes the currently open
' document.
' ************

' ************
' Check whether the currently open document
' has been saved.
' ************

If Len(ActiveDocument.path) <> 0 Then

' ************
' If it has been saved, first close the
' open copy and then delete the saved
' file.
' ************

ActiveDocument.Close SaveChanges:=False
Kill ActiveDocument.FullName

Else

' ************
' If it hasn't been saved, simply close
the open document without saving it.
' ************

ActiveDocument.Close SaveChanges:=False

End If

End Sub

Figure 3. Adding comments clarifies the purpose of the code and the logic used.

Choosing your words carefully

Apart from adding comments to your code, you can make your programs at least partially self-
documenting by choosing meaningful names for your procedures and variables, and by adopting a
standard form of naming different objects.
Take our DAD procedure. What on earth does DAD mean? As we've just written it, we can take a
guess that it means 'delete active document'. Why not say so? If we call it DeleteActiveDoc, its
purpose will be clear no matter where we call it from and no matter when we inspect the code. Take
the same approach when naming variables, constants and all other VBA objects.

As well as using descriptive names, stick to a standardised way of naming objects. For instance,
you'll save a lot of hassles if you provide prefixes for your variables indicating their data type. Your
programs will also be easier to read if you use standard methods of capitalising object names.

You can adopt any naming style you choose provided you make it clear and consistent. There are
many standard naming conventions in existence, too, and you may like to use one of them. One of
the most famous is the Leszynski Naming Convention (LNC), which is summarised in Alison Balter's
excellent book, Mastering Access Development, available online.

Breaking your code down

What other things can you do to make your code both more readable and reusable? For starters, try
to write procedures and functions that are no more than a screen or two in length. To do so, you'll
need to break your procedures into smaller, task-oriented fragments. You'll often find these task-
specific chunks have the added benefit of being reusable and callable by other procedures.

Make sure you don't reinvent the wheel. If there's a built-in function or component within VBA itself,
use it. For instance, don't write a function to determine the sine of an angle—use the existing SIN()
function.

Similarly, try to write functions that are reusable in themselves. If you find yourself writing the
same code a couple of times, consider taking that code and turning it into a sub-routine that can be
called from any procedure you create.

Thinking like a user

Apart from DAD's cosmetic flaws, the program makes no attempt to anticipate the actions of
someone using the program. Any program worth it's weight in bytes will perform at least
rudimentary error checking, anticipate problems that might occur, and provide feedback and control
to the user.

DAD's original logic is:

 Check whether the currently open document has been save.

 If it has, close the open document without saving any changes and delete the saved version
on disk.
 If it hasn't, close the open document without saving any changes.

We can improve DAD enormously by making it first check that an open document exists before
trying to delete it, and then checking the user's intentions.

A user-friendly DAD should do this:

 Check that there is an open document to delete. If there isn't, tell the user so and exit the
routine.
 If there is an open document, make sure the user really wants to delete the document, and
didn't accidentally select the DAD command.
 If the user has changed his or her mind, exit the routine.
  If the user really wants to delete the document, check whether the document has previously
been saved. If it has, close the open document without saving the changes and then delete
the saved version. If it hasn't, simply close the open document without saving the changes.

Code to be proud of

Take a look at the code in Figure 4. It's a makeover of DAD that includes:

 A routine to check the documents collection to see if there are any open documents.
 Another routine that gets user confirmation.
 Indention and white space for readability.
 Continuation characters (_) to break long lines.
 Descriptive and consistent procedure and variable naming.
 Comments.

DeleteActiveDocument is the type of code you should be aiming for. Not only does it do its job, but
it does it in style.

Note: Lines in the listing below are wrapped in order to fit into standard Web browsers. You may
find the code more readable if you view it without the line wrapping.

Sub DeleteActiveDocument()

Dim strFileToDelete As String

Dim docOpen As Document
Dim intDocCount As Integer

' ************
' Check that there is, in fact, an open document to
' delete, by checking the documents collection.
' The For...Next loop cycles through the collection
' and increments a counter by 1 for each open
' document it finds.
' ************

intDocCount = 0

For Each docOpen In Documents

intDocCount = intDocCount + 1

Next docOpen

If intDocCount > 0 Then

' ************
' If the counter indicates that there is, indeed, an
' an open document, check that the user really wants
' to delete it.
' ************

If MsgBox("Are you sure you want to delete the " & _

"open document permanently?" & vbCrLf _
& "You won't be able to undo this " & _
"action.", vbYesNo) = vbYes Then

' ************
' If the user wants to delete, check
' whether the document has already been saved.
' ************
 If Len(ActiveDocument.Path) <> 0 Then

' ************
' If it has been saved, close the open document
' without saving any changes and delete the
' saved file.
' ************

strFileToDelete = ActiveDocument.FullName
ActiveDocument.Close SaveChanges:=False
Kill strFileToDelete

Else

' ************
' If it hasn't been saved, simply close the
' open document without saving the changes.
' ************

ActiveDocument.Close SaveChanges:=False

End If

' ************
' If the user has changed her/his mind
' about deleting the document, don't do anything.
' ************

End If

Else

' ************
' If there's no open document, display a
' message for the user.
' ************

MsgBox "There is no open document to delete.", _

vbOKOnly

End If

End Sub

Figure 4. Better logic, better looking and better documented. A piece of solid code.

There are ways you can improve DeleteActiveDocument (such as adding a generic error handling
routine for unforeseen circumstances) but if all your code is at least as good as this routine you'll be
well on the way to writing consistently solid code.