Code Cleanup Formats, Standardizes, and Adds Error Handling to Your VBA/VB6 Source Code

Inheriting Badly Formatted Code

One of the most frustrating parts of supporting someone else's code is if it has a non-standard indentation style or variable naming convention. It's often incredibly difficult to read, which makes fixing problems or adding new features more challenging than it should be.

Procedures without Error Handling

Even worse is if there's no error handling. You know you need to add it to every procedure that lacks it, but the thought of manually doing so is painful.

Discover Total Visual CodeTools' Code Cleanup Feature

Fortunately, Total Visual CodeTools includes a Code Cleanup feature that cleans existing Microsoft Office/Access/VBA and Visual Basic (VB6) source code, adds your error handling to procedures that lack it, and applies your variable naming conventions. Quickly make your application more robust and maintainable.

Improve Quality By Adding Option Explicit and Error Handling

• Add Option Explicit to modules without it to quickly identify compile errors before they become runtime errors
• Add your custom error handling code to every procedure that lacks it, with options to specify procedure specific information.

Reformat Code and Styles

Total Visual CodeTools updates your code to apply consistent, industry standard formatting:

• Standardize line indentations by indenting loops like If..End If, Do..Loop, etc.
• Separate multiple variable declarations (Dim) statements on one line into individual lines
• Convert single line IF statements to multiple lines with End If
• Break up colon separated lines
• Set number of blank lines between procedures
• Eliminate consecutive blank lines
• Sort procedures alphabetically and/or by scope (Public vs. Private), etc.
• Remove line numbers
• Add module level comments with procedure list
• Add procedure comments with parameter listings

Apply Variable Naming Conventions

Total Visual CodeTools understands VBA and VB6 syntax so it detects all the uses of your variables and their scope. This lets it accurately apply the variable naming conventions you specify.

• Prefix variables by their type (e.g. strings start with "str", integers with "int", doubles with "dbl", recordset with "rst", etc.)
• Apply module level tags (e.g. "m")
• Apply global/public level tags (e.g. "g")
• Apply procedure parameter tags (e.g. "p")

Of course, if a variable is already named correctly, it is not modified.

Modify A Portion or Your Entire Project

Code Cleanup can be applied to your current procedure, current module, a subset of modules, or everything in your project. Select the objects, set the options, then launch it. You can even preview the changes before replacing your code.

Total Visual CodeTools supports code in Visual Basic 6 and any VBA host such as Microsoft Access, Excel, Outlook, Office, etc., and is called directly from the IDE menu or toolbar.

After one run you'll immediately realize how productive you can be by applying these code cleanup routines.

Before Code Cleanup

Here's an example of code in module modUtilities before it's cleaned up. You'll notice the lack of consistency with indentations, spacing, variable naming, etc.

Function AddRows(CustId As Long, retval As Integer) As Boolean

Dim Northwind As DAO.Database
Dim Customers As DAO.Recordset
Dim LastName1, LastName2, LastName3 As String

  LastName1 = "Jones"
  LastName2 = "Smith"
   LastName3 = "Gates"

Set Northwind = DBEngine.Workspaces(0).OpenDatabase("c:\nwind.mdb")
  Set Customers = Northwind.OpenRecordset("customers")
  Customers.FindFirst ("CustomerID=" & CustId)

If Customers.Fields(0).Value = LastName1 Then AddRows = True



Select Case Customers.Fields(1).Value
Case 1: retval = 12
                Case 2: retval = 13
                 Case 3: retval = 14
   End Select

 Customers.Close
Northwind.Close
Set Northwind = Nothing

End Function


Function NullConverter(inValue As Variant) As Variant
If IsNull(inValue) Then NullConverter = "" Else NullConverter = inValue

End Function

Reformatted VBA/VB6 Code After Code Cleanup

The text in yellow explains what changed:

Add your module comment structure with a complete procedure list:

' Module     : modUtilities
' Description:
' Procedures : AddRows(plngCustId As Long, pintRetval As Integer) As Boolean
'              NullConverter(pvarInValue As Variant) As Variant
' Modified   :
' 05/23 LC Cleaned with Total Visual CodeTools
' --------------------------------------------------

Add Option Explicit to each module that lacks it:
Option Explicit

Apply naming conventions to the procedure declaration with parameter prefix:
Function AddRows(plngCustId As Long, pintRetval As Integer) As Boolean

Add procedure comments with all parameters, the return value and dates:
  ' Comments:
  ' Params  : plngCustId 
  '           pintRetval 
  ' Returns : Boolean 
  ' Modified: 05/23 LC
  
  Add Error Handling Enabler:
  On Error GoTo PROC_ERR

  Apply naming conventions to variable names based on data type:
  Dim dbsNorthwind As DAO.Database
  Dim rstCustomers As DAO.Recordset
  Split multiple Dims in the same line to separate lines:
  Dim varLastName1
  Dim varLastName2
  Dim strLastName3 As String

  Standardize indentation and line spacing:
  varLastName1 = "Jones"
  varLastName2 = "Smith"
  strLastName3 = "Gates"

  Set dbsNorthwind = DBEngine.Workspaces(0).OpenDatabase("c:\nwind.mb")
  Set rstCustomers = dbsNorthwind.OpenRecordset("customers")
  rstCustomers.FindFirst ("CustomerID=" & plngCustId)

    Fix single-line If statements to add End If:
  If rstCustomers.Fields(0).Value = varLastName1 Then
    AddRows = True
  End If
  Eliminate extra blank lines:

  Fix colon-separated statements and standardized indentation:
  Select Case rstCustomers.Fields(1).Value
    Case 1
      pintRetval = 12
    Case 2
      pintRetval = 13
    Case 3
      pintRetval = 14
    End Select

  rstCustomers.Close
  dbsNorthwind.Close
  Set dbsNorthwind = Nothing

Add Error Handling Exit and Handler Points:

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox Err.Description
  Resume PROC_EXIT

End Function
Standardize to one line between procedures:

Function NullConverter(pvarInValue As Variant) As Variant
  ' Comments:
  ' Params  : pvarInValue
  ' Returns : Variant
  ' Modified: 05/23 LC

  On Error GoTo PROC_ERR
  Split single-line If statements into multiple lines:
  If IsNull(pvarInValue) Then 
    NullConverter = "" 
  Else 
    NullConverter = pvarInValue
  End If
  
PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox Err.Description
  Resume PROC_EXIT

End Function