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.
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.
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.
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 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.
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:
If Len(ActiveDocument.path) <> 0 Then
Figure 2. A little formatting makes DAD more readable.
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.
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.
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.
If Len(ActiveDocument.path) <> 0 Then
Figure 3. Adding comments clarifies the purpose of the code and the logic used.
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.
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.
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:
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.
Dim strFileToDelete As String
intDocCount = 0
For Each docOpen In Documents
strFileToDelete = ActiveDocument.FullName
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.