MZ-Tools for VBA and VB6

MZ-Tools is a terrific tool for anybody serious in getting some code done in Excel VBA and VB6.

Press here to download this free tool MZ-tools

With this tool is much easier to document your code and speed up some repetivie tasks.
The tools I use most often are

1) Insert and Remove line numbers.
    This is very usefull if you want to add the line number at which an error occurs with the Erl functions

2) Add Module Header. This is the the snipped I use

'----------------------------------------------------------------------
' Module    : {MODULE_NAME}
' Author    : {AUTHOR}
' Date      : {DATE}
' Purpose   : 
'----------------------------------------------------------------------
'

Option Explicit

Private Const cMODULE_NAME As String = "{MODULE_NAME}"

3) Add Procedure Header

'---------------------------------------------------------------------------
' Procedure : {PROCEDURE_NAME}
' Author    : {AUTHOR}
' Date      : {DATE}
' Purpose   : 
'---------------------------------------------------------------------------
'
' Inputs    :
'
' Output    :
'
'---------------------------------------------------------------------------
'

4) Error Handler

    Const cPROC_NAME As String = "{PROCEDURE_NAME}()"

On Error GoTo ErrorHandler

    {PROCEDURE_BODY}

Exit {PROCEDURE_TYPE}

ErrorHandler:
    Call ErrorHandler.Log(cMODULE_NAME, cPROC_NAME, Err.description,)

All those code snippets must be added in the option... dialog box of the MZ-Tools add-in.

Havin a module header and a procedure header for each module and procedure that you write will greatly enhance code maintainance.

In addition I have also and Error Handling snippet. This use my ErrorHandler module.

The ErroHandler module is a piece of code I wrote to manage in a coherent way the error raised by an application.

Example of VBA code formatting using manoli.net

if you go to this website http://www.manoli.net/csharpformat/ you will be able in a breeze to parse your VBA/C# code in HTML format. You can see at the bottom of this blog post an example.
To make it work in blogspot, you just need to go in Design -> Edit HTML and just after


<b:skin><![CDATA[/*

insert the following code. You can find http://www.manoli.net/csharpformat/format.aspx at the bottom of the page the link to the .css style sheet.

/* CSharp VB Formatting */

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: Consolas, "Courier New", Courier, Monospace;
background-color: #ffffff;
/*white-space: pre;*/
}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #006080; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}

.csharpcode .lnum { color: #606060; }

This is how the VBA code looks like formatte using the manoli.net application.

Option Explicit

Dim WithEvents mQry As QueryTable
Dim mOldConnection As String


Private Sub mQry_AfterRefresh(ByVal Success As Boolean)
  mQry.Connection = mOldConnection
End Sub

Private Sub mQry_BeforeRefresh(Cancel As Boolean)
    Dim DBQ As String
    Dim DefaultDir As String
    Dim Connection As String
        
    'Store the original connectin before overwriting
    mOldConnection = mQry.Connection
    
    'Build a DSN connectionless connection using OLEDB
    DBQ = ThisWorkbook.FullName
    DefaultDir = ThisWorkbook.Path
    Connection = "ODBC;DBQ=" & DBQ & ";"
    Connection = Connection & "DefaultDir=" & DefaultDir & ";"
    
    'For Excel 2003
    'Connection = Connection & "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
    
    'For Excel 2007
    Connection = Connection & "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
    
    'For Excel 2003 Just change the Connecton for listed query
    'If mQry.Name = "ReportCustomers" Or mQry.Name = "ReportOrdersAndCustomers" Then
    '   mQry.Connection = Connection
    'End If
    
    'For Excel 2007 Just change the Connecton for listed query
    If mQry.ListObject.Name = "ReportCustomers" Or mQry.ListObject.Name = "ReportOrdersAndCustomers" Then
       mQry.Connection = Connection
    End If
    
    
End Sub