home

beginners menu


 

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:

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:

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:

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:

Code to be proud of

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

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

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 " & _
             "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.


top home beginners menu