Documentation is one of the most important steps in any development project. In this article, Russell shows you how you can use the properties of the Module object introduced in Access 97 to document procedures in your code separately in order to make your documentation easier to read and understand.
Like many developers, creating system documentation is the one task in any project that I dread. Any product that can help to automate this task is worth its weight in gold. The only problem is that some of the tools available, especially Access's own tools, don't lend themselves very well to customization and don't supply me with all of the information I really want. When I need to customize the way that data is reported, it's often necessary to export reports to Word or Excel and make the changes there. This seems counter-productive to me when the most powerful reporting tools can be found in Access itself. The solution for me has been to create my own documentation functions in Access that store information about the design of a database in a table and then base my own report on that information. The functions I create often look only at the properties of an object -- a field's data type, for example -- and save the value of that property to a table. These functions turned out to be relatively easy to create.
The biggest problem I've had is with documenting modules. Whenever you print a module's definition, you end up with a long printout that shows all of the procedures in that module. You can't tell at a glance where one procedure ends and the next one begins. I figured that the best solution to this problem would be to print each procedure on a separate page with some general information about the procedure as a header. As I had with my other documentation projects, I wanted to save information about each procedure in a table. With the data in a table, I could use an Access report to format the information the way that I wanted it to appear. Until Access 97 was released, there was no way to do this. Access 97 introduced the Module object and added some very useful properties to it. This meant that the information I needed was finally available.
When I started to work on documenting my code, I determined a few basic characteristics of each procedure that I wanted to extract. These are shown in Table 1.
Table 1. Data requirements.
Name of the parent module the procedure belongs to.
Name of the procedure being documented.
Type of procedure: Function, Sub, Property Get, Property Let, or Property Set.
Is the procedure public (scope).
Is the procedure static (lifetime).
What arguments the procedure has.
Data type of the return value, or the Property reference or value parameter.
A complete listing of the procedure code.
Unfortunately, the Module object doesn't have properties that relate to most of these requirements, so I'd have to create functions of my own to extract them. It was these requirements that led to the creation of the EnumerateProcedures function that can be found Add-in database in download file DOCUMENT.ZIP.
Where to start
Modules, like forms and reports, reveal their properties only when they're open. This meant that I'd have to loop through and open each module in the database in order to analyze it. In addition to the modules, I also wanted to document the code in all of my forms and reports. Rather than have separate routines for form, report, and standalone modules, I wanted EnumerateProcedures to handle them all. As a result, I had to account for the differences between the various kinds of modules. Because of this decision, once the EnumerateProcedures was written, I could open each document in the Forms, Reports, and Modules collections and analyze it without worrying if I was calling the right routine.
To open a form or report module, my procedure had to open the parent form or report in design view. Once the parent object was open, I determined whether the object had a module by using the HasModule property of the parent and saved this value to the variable boolHasModule. Later, I'd document any object where boolHadModule was true. This might seem a little roundabout -- why not just open the module and document it? However, if you attempt to open a form or report module that doesn't have a module, Access will create a module for it and eliminate the benefits of having a lightweight form. When working with forms and reports, I also had to supply EnumerateProcedures with the object type of the parent and the leader on the name of the module (that is, Form_ or Report_). Regular modules were simpler to handle: I just set boolHasModule to True.
With this information in hand, I could begin the analysis. The first thing I wanted to document was the module-level declarations. Access 97 modules now include the Lines and CountOfDeclarationLines properties. The Lines property returns the text for a set of lines in the module (you specify which line to start at and how many lines to return). The CountOfDeclarationLines property gives the number of lines in a module's declaration section. In order to retrieve the declarations, all I had to do was use the Lines property to retrieve the lines between the first line of the module and the value returned by the CountOfDeclarationLines property. I also saved the module's Type property, which indicates whether a module is a standard module or a class module in my table. The next step was to save the information for each procedure in the module.
Finding the procedures
Obtaining the list of procedures in a module was the biggest hurdle I had to overcome in my program. Most of the new properties that the Module object has provide information on a particular procedure. To retrieve that information, you must provide the procedure name and its type. There's no Procedures collection.
Because Access doesn't make a list of procedures and their types available, I was forced to turn to my experience with analyzing strings. What I needed to do was read through each line of the module and determine whether the line being analyzed was a procedure declaration. My solution can be seen in the function ProcedureHeader in Listing 1.
Listing 1. A routine to determine whether a line in a module is a procedure declaration.
Private Function ProcedureHeader _
(ByVal strLine As String, _
ByRef rstrName As String, _
ByRef rstrType As String, _
ByRef rboolPublic As Boolean, _
ByRef rboolStatic As Boolean) As Boolean
On Error GoTo Err_ProcedureHeader
Dim strStartOfLine As String
Dim astrLeaders(1 To 6) As String
Dim arstrTypes(1 To 5) As String
Dim lngI As Long, lngJ As Long
Dim boolDeclaration As Boolean
'Define possible leaders
astrLeaders(1) = ""
astrLeaders(2) = "Static "
astrLeaders(3) = "Public "
astrLeaders(4) = "Public Static "
astrLeaders(5) = "Private "
astrLeaders(6) = "Private Static "
'Define possible procedure types
arstrTypes(1) = "Function "
arstrTypes(2) = "Sub "
arstrTypes(3) = "Property Let "
arstrTypes(4) = "Property Set "
arstrTypes(5) = "Property Get "
For lngI = LBound(astrLeaders) To UBound(astrLeaders)
For lngJ = LBound(arstrTypes) To UBound(arstrTypes)
strStartOfLine = _
astrLeaders(lngI) & arstrTypes(lngJ)
If Left(strLine, Len(strStartOfLine)) = _
boolDeclaration = True
boolDeclaration = False
If boolDeclaration Then Exit For
If boolDeclaration Then
rboolPublic = True
rboolStatic = False
If InStr(strStartOfLine, "Private") Then
rboolPublic = False
If InStr(strStartOfLine, "Static") Then
rboolStatic = True
rstrName = Trim$(Mid(strLine, _
Len(strStartOfLine) + 1, InStr(strLine, "(") - _
Len(strStartOfLine) - 1))
rstrType = Trim$(arstrTypes(lngJ))
ProcedureHeader = True
MsgBox "Error " & Err.Number & " " & _
There are a number of ways a procedure can be declared. Each declaration tells what type of procedure or property the routine is and what scope and lifetime the procedure has. In order to match all of the possible declaration methods, I created two arrays. The first array, astrLeaders, contains a list of the different scope and lifetime declarations for a procedure. Because it's possible to declare a procedure without explicitly declaring the scope or lifetime (shame on those of us who do this), I also included a zero-length string as a possibility in the list of leaders. The second array, astrTypes, contains a list of the different types of procedures. Looping through each possible combination of the two arrays, I was able to generate all of the different ways of declaring a procedure.
With this information in hand, all the ProcedureHeader function had to do was determine whether the beginning of the line of code being analyzed matched one of my generated procedure declarations. If the line is a declaration line, the function sets the variable boolDeclaraton to True and exits the ForNext loop.
The first time I created the ProcedureHeader function, the only thing I had happen at this point was that the calling procedure was told that the line of code being analyzed was a procedure declaration line. Later on in the development, however, I decided that this was the easiest point at which to obtain more information about the procedure.
My generated procedure declaration contained some important information about the procedure. After all, that declaration told me what type of procedure had been found and what that procedure's scope and lifetime were. Also, it was easier to get the name of the procedure if I already knew how the procedure was declared; the name of the procedure was whatever was between the procedure declaration and the first occurrence of an open bracket. In the latest version of ProcedureHeader, all of this information is returned to the calling function, EnumerateProcedures, by reference. EnumerateProcedures then uses Access's Switch function to save the VBA constant for the procedure type to the variable lngType.
Obtaining the procedure information
Once I had the declaration information for a procedure, I could make use of some of the new properties of the Module Object. The next piece of information I wanted to be able to save to the documentation table was the arguments used in the procedure. The easiest way to do this is to use the module's ProcBodyLine property. This property returns the line in the module that the procedure is declared on. It requires the procedure name and type as its arguments. Here's a sample call:
lngDeclLine = mdl.ProcBodyLine("MyProc", lngType)
The only problem with this function is that it only returns the beginning line number and doesn't take line continuation characters into account. Once again, I was back to my experience in working with strings. I created the FullDeclaration function (which, ironically, uses line continuation characters in its declaration line). This function, which is found in Listing 2, reads the declaration line and all the following lines until it finds a line without a continuation character at the end of it. The function then loads all of the lines of the declaration into a string variable and deletes all continuation and carriage return/linefeed characters from the line. Finally, it trims the line and deletes all of the double spaces in it. The complete declaration statement is then passed back to EnumerateProcedures and that function stores the procedure information to the documentation table.
Listing 2. A function to extract a procedure declaration.
Private Function FullDeclaration _
(ByVal strModuleName As String, _
ByVal strProcedureName As String, _
ByVal lngType As Long) As String
On Error GoTo Err_FullDeclaration
Dim mdl As Module
Dim strLine As String
Dim lngDeclLine As Long
Dim lngI As Long
Set mdl = Modules(strModuleName)
lngDeclLine = mdl.ProcBodyLine(strProcedureName, _
lngI = 1
strLine = mdl.Lines(lngDeclLine, lngI)
lngI = lngI + 1
Loop Until Trim$(Right$(strLine, 1)) <> "_"
Do Until InStr(strLine, "_" & vbCrLf) = 0
strLine = ReplaceString(strLine, "_" & vbCrLf, " ", _
strLine = TrimString(strLine)
FullDeclaration = strLine
MsgBox "Error " & Err.Number & " " & _
I also wanted to save the procedure arguments and return type to my documentation table. Obtaining the return type for a procedure was reasonably simple. All I had to do was find the last closing bracket in the declaration, check to see if the word "As" appeared after it, and then get the word after that. The procedure's arguments would be any characters between the first and last brackets in the declaration. I've included the ProcedureArguments routine in Listing 3 as a sample of how the extracted procedure declaration is used.
Listing 3. A function to extract procedure arguments.
Private Function ProcedureArguments _
(ByVal strDeclaration As String) As Variant
On Error GoTo Err_ProcedureArguments
Dim strWorking As String
Dim lngStart As Long
Dim lngLength As Long
Dim lngLastPos As Long
Dim lngCurrentPos As Long
lngStart = InStr(strDeclaration, "(") + 1
lngCurrentPos = InStr(strDeclaration, ")")
Do Until lngCurrentPos = 0
lngLastPos = lngCurrentPos
lngCurrentPos = InStr(lngLastPos + 1, _
lngLength = lngLastPos - lngStart
strWorking = Mid$(strDeclaration, lngStart, lngLength)
ProcedureArguments = TrimString(strWorking)
MsgBox "Error " & Err.Number & " " & Err.Description
However, these functions don't work for Property Let and Property Set procedures. Property Let procedures contain the property value as their last or only argument. Similarly, Property Set procedures contain their object type as their last or only argument. This meant that I had to take the last argument out of the argument list and store it somewhere else. For the sake of simplicity, EnumerateProcedures saves the value or reference in the Return field in the documentation table.
The final piece of data about the procedure that I stored is the full code for the procedure, including the declaration, comments, and any error handling or exit statements. I could then use this information in my report.
Using the add-in
To install the add-in, extract it to the directory where Access resides and use the add-in manager to install it. You can then select Module Documentor from Tools | Add-Ins to start the documentor. The screen form that appears prompts you for the collections that you'd like to document and the table to document them to. Figure 1 shows the form that the Module Documentor uses.
If you choose a table name that already exists, the add-in will ask you if you want to overwrite it. The add-in does not append information to the table. Once the documentation is complete, the add-in will ask you if you want the preview the default report. This is a simple report that that uses the report's GroupBy property to start a new page after each record in the data. You can find the report in the add-in database and save it in your own database to modify it if the layout doesn't suit your needs.
Where do you go from here?
There are a number of possible improvements that could be added to the add-in. It currently documents all of the objects in a collection. This could be changed so that the user has the ability to select only those objects that he or she would like to document. You could change it so that the data is stored in a separate documentation database so as not to clutter your current project. You can also change the function so that it offers the ability to append the information to the destination table, rather than overwriting it.
In every project, documentation is a necessary evil. However, with a little time and know-how, I discovered that you can use some of Access's native functionality to make this task a great deal less onerous.
Read about the Download DOCUMENT.ZIP on this page