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.
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.
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.
Total Visual CodeTools updates your code to apply consistent, industry standard formatting:
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.
Of course, if a variable is already named correctly, it is not modified.
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.
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.accdb") 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
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 and data type prefixes: 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.accdb") 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 with Exit and Error Handler: 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
Supports Office/Access 2016, 2013, 2010, 2007, 2003, 2002, 2000, and Visual Basic 6.0!
Also available for
"Total Visual CodeTools is by far my favorite third-party product."
Alison Balter, Author, Conference Speaker, Instructor
Best Visual Basic Add-In