If you spend a lot of your time cutting and pasting code headers and error handlers from procedure to procedure, or if you've inherited code that simply isn't up to standard, then you'll like the first of two add-ins that Peter Strong has written to help you organize your code.
These days, I seem to spend a great deal of my time looking at other people's code. It's an unfortunate fact that while the code I'm reading might be perfectly serviceable, all too often it lacks error handling and is totally devoid of comments. In the real world, the lack of documentation is an understandable phenomenon -- an impending deadline means that the niceties of a well laid-out comment header are set aside in the rush to get an application up and running.
The shame of this situation is that code-level comments are often the most useful, and often only, documentation available to developers who revisit legacy code. In Peter Vogel's review of Total Access Analyzer 8.0 (Smart Access, November 1997), Peter made the point that technical documentation can be generated when it's required by using a documenting tool. While a good documenting tool is an excellent way of revealing the database schema, it's no substitute for module-level documentation that actually tells you what a programmer was planning.
I was recently faced with the unenviable task of bringing several thousand lines of code up to a well-defined project standard. This meant not only including module comment headers but also adding an appropriate level of error handling. The first thing I did was look around to see what tools were available to help me.
Over the years, Smart Access has featured several code builders that can be extremely useful when it comes to creating new procedures with code headers and error handling. The problem with these, though, is that they aren't so useful when it comes to working with existing procedures. What I wanted was a tool that would allow me to make insertions from within my procedures. To get what I wanted, I realized that I was going to have to write my own utility.
My specific need was for a utility to add a standard header block. Since most of the code that I was upgrading also required an error handler, I decided to insert the error code with the header. Once the error code was added, I could then move or delete it as appropriate. The most important design criteria for me were that I wanted the utility to be fast and easily accessible.
Installing the add-in
The result was TOP&TAIL.MDA. The Library is installed using the Add-in Manager from the Tools | Add-ins | Add-in Manager menu. Running it will copy a code module (pas_InsertCode) to your database, which saves you having to go to your Tools | References menu to add a reference to the library. Also added is an Autokeys macro that contains an entry that causes Ctrl-H to run the function that inserts the new material into your code. After installation, you'll be prompted to close the database. When you reopen the database, Access will pick up the Autokeys macro.
The module "pas_InsertCode" contains the routine fncTopAndTail, which, when run, adds a bunch of text to your code. The text is valid VBA code for:
|•||a template for a comment header|
|•||standard code for the error handler|
The pas_PasteHeader routine, which does the bulk of the text insertion, can easily be edited to change the material that's added to your code if you don't like what I've done. I could, in fact, have stored the material that's written to your code in a table, but my intent was to use the minimal number of database objects to make cleaning up as easy as possible.
To use the utility, while editing your VBA program, highlight a procedure name and press Ctrl-H to run the fncTopAndTail function. The macro copies the highlighted procedure name to the clipboard and then calls the function pas_PasteHeader(). pas_PasteHeader uses the contents of the clipboard to build up a sensible header and footer for your code, which it then pastes back into your procedure.
Before and after
So what do the results of this routine look like? Let's say you have a routine that looks like this:
Private Sub Form_Current()
After you run the routine, you'll have this:
Private Sub Form_Current()
'Project : Project Name
'Called By :
'Author : Peter Strong
'Date Created : 21/04/98 15:12:47
'Rev. History :
On Error GoTo Err_Form_Current
Dim strActiveObjectName As String
strActiveObjectName = _
Application.CurrentObjectName & "|Form_Current"
MsgBox "An error has occured in " & _
strActiveObjectName & " . " & Chr$(13) & _
Err.Number & " : " & Err.Description
You'll notice that the routine adds a variable called strActiveObjectName, which is set to the name of the procedure. This variable can be very useful if you want to enhance the very basic error handling to something more sophisticated.
Having gone this far, I should point out the obvious fact that unless you take the trouble to complete the header block properly, you're adding little value to the code -- and I'm afraid that I haven't been able to automate this part of the process so far.
Standardizing header-block categories
I haven't been able to find any universal standards for code headers, but the categories I set up here have been chosen because they're appropriate to most of my projects. More importantly, these categories work as keywords for my Code Library Add-in, which I'll discuss next month. If you use both tools together, you'll see how valuable these categories are.
I hope you'll find this tool to be a useful addition your developers' arsenal. Once you've finished editing your code, you should remove this module and the Autokeys macro before you ship your finished application -- after all, you want to keep things tidy, don't you?
Read about the download TOPTAIL.ZIP on this page
Sidebar: Foundation Concepts
Access allows you to add new developer tools to your Access environment by writing code that manipulates Access itself. These developer tools can be written in Access and are normally kept in databases with the extension .MDA. Access error handling is done through the "On Error Goto" statement, which causes specific lines of code to execute whenever an error occurs. The Autokeys macro is a a macro added to your database to create hot keys. Within a database's Autokeys macro, you can assign a key combination (like Alt-P) to run some function in your database.
If you code for a living, you know that error trapping is a drag. But it’s also an essential part of any serious Access development project. Here Keith Bombard introduces a new tool to automatically insert error-handling code into your Access applications Go to Oct-2002