The Best Help for Writing Code

If you want to save time creating custom Access applications, this developer tool can help.

By Doug Silver
Access Advisor, February 1997
Copyright © Advisor Publications. All rights reserved.

FMS, Inc. is a group out of Vienna, Virginia who really understands the needs of Access developers. There's a good chance that you're already familiar with its flagship product - the award winning Total Access Analyzer (which was reviewed in the February 1995 issue). Once again, they've hit the target with Total Access Code Tools, a Swiss Army knife for serious developers using Access 2.0.

Code Tools is designed to assist in the creation, development, maintenance, and delivery of Module objects. It loads as an add-in and when run, a toolbar comes up allowing you to launch the various Code Tools builders and utilities. There are the three tools that make this product a must have for any Access developer:

• Procedure Builder - Create new procedures that contain standard header information and error handling structures.
• Code Cleanup Utility - Formats existing code with indentations and variable naming conventions. In addition, you can specify to add error handling, module/procedure header comments, and Option Explicit statements.
• Code Delivery Utility - Performs pre-delivery modifications to your code by removing white spaces and comments, adding line numbers, and renaming variables.

Code Tools includes the following tools not covered here, but nonetheless can save you loads of time:

• Open All Modules Utility - Opens all form and report modules, so you can perform a global compile and find/replace actions.
• SQL and Long Text Builders - Lets you type or paste long text into a window, and then convert it to a variable assignment which won't run off the end of the screen.
• Message Box Builder - A graphical means to generate code for the MsgBox() function and paste the results in your code.
• Select Case Builder - Using the screen builder form, you can save typing while generating a formatted Select case structure, which you insert into a procedure.
• List Fill Builder - Generates a template for building fill functions for populating list box and combo box controls from arrays.
• Constants Generator Utility - Produces constants for all or selected objects. This allows your code to reference constants resulting in less work should you later rename these objects.

Starting Code Tools

You start CodeTools by selecting it from the File|Add-ins menu. The first time you run Code Tools you're asked to fill out registration information. This is a one time detour. Subsequent launchings of Code Tools open the Code Tools toolbar (figure 1).

The tool bar may seem large, especially in 640x480 resolution. The shrink button can be used to remove the right four buttons from display. FMS has done a fine job of making the Code Tools toolbar as small as possible. To further accommodate your workspace preferences, you can display this toolbar vertically or horizontally (figure 1).

As an alternative, you can open this form on-demand by calling its entry-point function TACTools_Start() from an AutoKeys macro. While this doesn't change the size of the toolbar, it may make it more convenient to open and close as needed.

The Procedure Builder

You can use the Procedure Builder (figure 2) in one of two ways: to create your subprocedure or function, or to generate the body structure and place it into an existing procedure.

After your procedure is generated, you can copy the code to the clipboard and paste it into your module. Recognizing that you may want to use this code in an event procedure, FMS thoughtfully provided a Copy Body to Clipboard option, which copies only the code between the procedure Declaration and End procedure lines. This is useful for adding the code to existing global procedures, although you can accomplish the same task using the Code Cleanup Utility.

The Options button extends the Procedure Builder significantly, by giving you the opportunity to customize the procedure header and error handling text structure. Special characters are used to identify user name, date/time, along with procedure and module name information. A few screen shots can be speak more loudly than a written description. Figure 3 shows how you might customize the Procedure Comment (Header) text.

Figure 4 shows how one might customize the Error Handling options. Since all the text boxes in these options are scrollable, you can write a comprehensive error logging process and have it automatically become part of your procedure. FMS has provided sample code in a text file, which you ca use to log errors to a text file for review at a later date.

The example in figure 4 shows a label for Exit, which I added before snapping the screen shot. The template does not include this label, and defaults to a Resume Next in the actual error handler. There are other numerous possibilities. I talked with an FMS developer, and he was excited about the possibility of benchmarking procedure execution speeds, recording procedure calls, and a host of other actions that could be triggered at the start and end of a procedure which aren't directly related to error handling. The point is, the Error Handling section of the Procedure Builder always writes to the top and bottom of a procedure, making developer intervention as easy as running the Procedure Builder or Code Clean-up on a test version of your application.

The Procedure Builder is exceptionally functional, and justifies much of the cost of Total Access Code Tools. There are additional option settings for establishing Procedure Naming Conventions and Global Procedure Identifiers.

The Code Cleanup Utility

The Code Cleanup Utility is a superset of the Procedure Builder and more. There are three ways you might use Code Cleanup:

• Make a consistency pass through a work in progress.
• Clean up your application prior to delivery.
• Clean up inherited projects.

After your application is written and tested, you can run Code Cleanup, but first you must perform two key steps:

1. Run the Open all Modules Utility (it comes with Code Tools), and ensure your code can compile.
2. Back up your database (page 59 of the User's Manual uses the work 'backup' no less than eight times).

Once you've accomplished this, you should run Code Cleanup with just the Option Explicit setting on (figure 5). This ensures the Option Explicit is added to all modules, including the private ones belonging to each form and report. If any Option Explicit statements are added, it's possible your code won't compile; therefore, you should (read that as "must") repeat steps 1 and 2 above.

The Error Handling and Procedure Comments Options work as with the Procedure Builder. Once again, you can modify the header text to fit your programming style and conventions. You can also have Code Cleanup apply indentations, module comments, and variable naming conventions to your code. The naming conventions can be edited to whatever you like' however, since they default to the L/R Conventions, you'll probably not make many changes to the defaults. When you press OK, Code Cleanup takes over applying your specifications to all modules in your database.

Once Cleanup is completed, you'll be left with model code suitable for documentation (no doubt using Total Access Analyzer) as in this example:

Function DivByZero (Number as Variant, Denom As Variant, Retval as Variant) As Variant
Dim Result As Variant
Result = RetValue
If Not IsNull (Numer) And Not IsNull(Denom) Then
If IsNumeric(Numer) And IsNumeric(Denom) Then
If Denom <> 0 Then
Result = Numer / Denom
End If
End If
End If
DivByZero = Result
End Function

After running Code Cleanup, this code will look like the following:

Function DivByZero (varNumber as Variant, varDenom As Variant, varRetval as Variant) As Variant
  ' Comments   :
  ' Parameters : varNumer
  '              varDenom
  '              varRetval
  ' Returns    : Variant
  ' Created By : Doug Silver - PDS Consulting
  '            : 05/20/96
  ' Modified   :
  ' -----------------------------------------
  On Error GoTo DivByZero_Err:

  Dim varResult As Variant
  VarResult = RetValue
  If Not IsNull(varNumer) And Not IsNull(varDenom) Then
    If IsNumeric(varNumer) And IsNumeric(varDenom Then
      If var Denom <> Then
        varResult = varNumer / varDenom
      End If
    End If
  End If
  DivByZero = varResult

DivByZero_Exit:
  Exit Function

DivByZero_Err:
  MsgBox "The following error occurred: " & Error$
  Resume DivByZero_Exit
End Function

All that's left is to move the line DivByZero = varResult so that it follows the Exit label, and then to fill out comments as appropriate. Code Cleanup enters the error handling that I specified, module comments, code indentations, and even prefixes variables appropriately. This example is trivial; you should see Code Cleanup do its thing on poorly written code!

The Code Delivery Utility

This is the most ambitious and stunning element of the Code Tools, and alone is worth the cost of the entire package. Code Delivery operates under these assumptions:

• White space (blank lines and indentation spaces) and Comments take up space and reduce performance.
• It's difficult to secure your module code.
• Debugging code is far easier if you can pinpoint the offending line, but while Access supports line numbering, it isn't worth the effort to type them manually.

Code Delivery can remove white space, and indent and outdent code and comments. Code Delivery can also substitute alpha-numeric names for all constants and variables in your code, making it less readable. Finally, Code Delivery can add line numbers in all your procedures, making it easier to track down the offending line when an error is encountered (if you have an error logging process, modify it to record the line number).

This code has been through both Code Cleanup and my own additions of comments and whitespace:

Function DivByZero (varNumer As Variant, varDenom As Variant, varReturn As Variant) As Variant
  ' Comments  : perform division to protect from Divide BY Zero error.
  '             If non-numeric or Null values or if the Denominator is Zero then return the value specified by incoming parameter varReturn.
  ' Parameters : varNumer - Numerator
  '      varDenom - Denominator
  '      varReturn - Return value to supply on
  '      failure or error
  ' Returns : Variant
  ' Created : 05/22/96-Doug Silver-PDS Consulting
  ' Modified  :
  ' ---------------------------------------------
  On Error GoTo DivByZero_Err:
  Dim varResult As Variant

   'Initialize the return value
  varResult = varReturn

  'Test for numeric, non-null values and non-zero denominator
  If Not IsNull(varNumer) And Not IsNull (varDenom) Then
    If IsNumeric (varNumer) And IsNumeric (varDenom) Then
      If varDenom <> 0 Then
        varResult = varNumer / varDenom
      End If
    End If
  End If

DivByZero_Exit:
  DivByZero = varResult
  Exit Function

DivByZero_Err:
  MsgBox "The following error occurred: " & Error$
  Resume DivByZero_Exit
End Function

After running Code Delivery, it looks like this:

Function DivByZero (V2 As Variant, V1 As Variant, V4 As Variant) As Variant
100 On Error GoTo DivByZero_Err:
110 Dim V3 As Variant
120 V3 = V4
130 If Not IsNull(V2) And Not IsNull(V1) Then
140 If IsNumeric(V2) And IsNumeric(V1) Then
150 If V1 <> 0 Then
160 V3 = V2 / V1
170 End If
180 End If
190 End If
DivByZero_Err:
220 MsgBox "The following error occurred: " & Error$
230 Resume DivByZero_Exit
End Function

Notice a significant difference in size and readability. The Divide by Zero function is rather trivial. I ran Code Delivery against a freshly compacted 2.3MB database. This database uses attached tables, so the size is due primarily to all the other database objects. After running Code Delivery with line numbering set to yes, I again compacted my database. The result is a database of just more than 2MB: a reduction in size of more than 200K! This suggests that more than 10 percent of my database size is due to white space, the length of variable names, and comment lines. I have no doubt that performance will be enhanced after Code Delivery.

The User's Manual

The Code Tools Manual is similar to other recent FMS product manuals in that it's written in a clear, concise, and informative manner. You can probably operate this product without reading the manual, but you'd be doing yourself a disservice. The manual comes with a discussion of an enhanced error handler for logging errors to a text file. The code for this is in a text file you can load to a module. Then, all you have to do is make a couple of modifications to the error-handler section of the Procedure Builder or Code Cleanup, and you'll have error logging application wide. A read-me file comes on the product disk and contains corrections and clarifications for some of the topics in the manual.

Conclusion

Total Access Code Tools delivers the tools necessary to produce high quality Access Applications with a maximum of consistence and minimum of tedious labor. Using them makes the less experienced developer produce more professional code, and helps the more experienced developer be more productive. With the addition of a little code, the really creative developer can use Code Cleanup on a pre-delivery version to benchmark procedure call frequency and execution time, and determine what processes should be reviewed for optimization.

There are a few items I'd like to see done differently, but to be honest, it's a matter of taste rather than of importance. One particular option I'd like is an entry-point function to each of the individual builders and utilities. This way, I could bypass the toolbar and call these functions directly from and AutoKeys macro.

I've written builders that do many of the same actions performed by Total Access Code Tools, and I always felt they gave me an edge over other application developers. Without having to take the same route I did, Total Access Code Tools helps to raise programmer productivity and improve the overall quality of your code.

Doug Silver is the presidnet of PDS Consulting, Inc,. a Minneapolis Minnesota, custom application development company specializing in Access, Visual Basic, and Office. Doug is a trainer for Application Developers Training Company and leads the Access Developer User Groups in the Twin Cities.