Introduction

Similar Application

  1. MZ-Tools 3.0 for VBA

How this Application Differs

Features comparison:

MZ-Tools 3.0 for VBA VBA Error Handler
This Application
Support 32 and 64 bit of Windows No Yes
Open source code No Yes
Very Light Weight even on slow computers No Yes
Can act as an example
to guide you to build your own Addin 		No Yes
Programming Technique VB6 VB2010
Notes: Install VB6 run time;
this may cause slowing in VBA IDE startup		 Required .NET Framework
v2.0 and Office 2010
Power application with many feathers Yes No

What this application does

When you write Visual Basic for application code, this add in will do the following:

  • Format the code correctly
  • Add line numbers
  • Add error handler

Why it's useful

  1. The VBA code is not auto formatting, so it is nice to format it
  2. The line numbers in VBA are a good reference for debugging your code
  3. Error handler will prevent your code form crashing

What problem is solved

Visual Studio does not provide a direct wizard to create add ins for VBA, so we should add the registry Key manually.

Background

Although VB6 went out, VBA is still needed to develop Office macros or Microsoft Access modules so VBA add-in is important.

Using the Code

Requirement

  1. Ensure that Microsoft Office 2010 is installed

How to use this code

  1. Compile the solution and generate the installation setup or use the addin setup
  2. Install the VBA Error Handler add in
  3. Open an Office document that contains macro and edit the macro in VBA Window or open Microsoft Access Module
  4. From Add-Ins menu, choose Add error handler to this file
  5. This will change the format of your code as follows...

Sample of code before applying this add in: 

Public Sub Macro1()
    MsgBox("This is Macro1")
End Sub

Sample of code after applying this add in: 

Public Sub Macro1()
    On Error GoTo EH
11  MsgBox("This is Macro1")
    Exit Sub
EH:
    Debug.Print "Error in: Module1.Macro1." & Erl & vbNewLine & Err.Description
    'This will print the error Module name and the Error Sub Name and
    'line number in the immediate window and this is useful in debugging
    Debug.Assert False
    'This will stop the execution of the code if you are in debug mode and
    'has no effect in run mode
    MsgBox "Error in: Module1.Macro1." & Erl & vbNewLine & Err.Description
    'This will show a message box about the error in run time
End Sub

How to switch off the debug mode when executing Office macro

  1. Open an Office document that contains macro and edit the macro in VBA Window or open Microsoft Access Module
  2. From Tolls menu, choose Your project properties then choose Protection
  3. Check the Lock project for viewing
  4. Type a password and click OK
  5. Close your document and reopen it

Points of Interest

This solution contains the following projects:

  1. The main add in project
  2. Setup project

How to write a VBA Addin

  1. Create new class library project that uses v2 of .NET Framework and ComVisible
  2. Add the following references:
    • System
    • System.Windows.Forms
    • Extensibility = Microsoft Add-In Designer
      1. If you have Visual Studio Tools for Office, add the following references:
        • Microsoft.Vbe.Interop = C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Vbe.Interop.dll
        • Microsoft.Office.Core = C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Office.dll
      2. If you don't have Visual Studio Tools for Office, add the following references:
        • VBIDE = C:\Program Files\Common Files\microsoft shared\VBA\VBA6\VBE6EXT.OLB
        • C:\Program Files\Common Files\microsoft shared\OFFICE14\MSO.dll
  3. Add a class that Implements IDTExtensibility2
  4. Write your code: 
    Private Const AddErrorHandlerCaption As String = "Add error handler to this file"
Private AddErrorHandlerMenuItem As CommandBarControl
Private WithEvents AddErrorHandlerEvent As CommandBarEvents
Private Sub OnConnection(ByVal Application As Object _
, ByVal ConnectMode As ext_ConnectMode, ByVal AddInInst As Object _
, ByRef custom As System.Array) Implements IDTExtensibility2.OnConnection
    Try
        'save the vb instance
        VBInstance = CType(Application, VBE)
        AddErrorHandlerMenuItem = AddToAddInCommandBar(AddErrorHandlerCaption)
        AddErrorHandlerToProjectMenuItem = _
		AddToAddInCommandBar(AddErrorHandlerToProjectCaption)
        'sink the event
        With VBInstance.Events
            Me.AddErrorHandlerEvent = .CommandBarEvents(AddErrorHandlerMenuItem)
            '.....More Code
        End With
    Catch ex As Exception
        ErrMsg(ex)
    End Try
End Sub

Private Sub OnDisconnection(ByVal RemoveMode As ext_DisconnectMode _
, ByRef custom As System.Array) _
Implements IDTExtensibility2.OnDisconnection
    On Error Resume Next
    'delete the command bar entry

    AddErrorHandlerMenuItem.Delete()
    '.....More Code
    'shut down the Add-In
End Sub

Private Sub OnAddErrorClick(ByVal CommandBarControl As Object, _
	ByRef handled As Boolean, _
            ByRef CancelDefault As Boolean) Handles AddErrorHandlerEvent.Click
   '.....Your click event code here
End Sub
  5. Generate reg file to register your add-in for VBA like this: 
    REGEDIT4

[HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE\6.0\Addins\{ClassProgId}]
"CommandLineSafe"=dword:00000000
"Description"="{ClassDescription}"
"FriendlyName"="{ClassDisplayName}"
"LoadBehavior"=dword:00000003

[HKEY_CURRENT_USER\Software\Microsoft\VBA\6.0\Common]
"FontFace"="Courier New"
"FontHeight"="10"

    {ClassProgId}, {ClassDescription} and {ClassDisplayName} will be replaced with their values for this addin. {ClassProgId} is the full class name example: VBAErrorHandler.Connect

  6. Create a setup project that installs and registers the class for com and add the above reg file to the registry.

How to Analyse the VBA Module

Although we could move from one procedure to anther using CodeModule object, the application will be more quick execution if we get all Module code, then analyse it using Regular Expressions and use it to analyse each procedure.

Regular Expressions Learning

Many applications may help you learn Regular Expressions:

  1. Expresso form http://www.ultrapico.com
  2. csharpregexdemo
  3. RegEx Builder form www.SoftPedia.com

History

  • 20th July, 2011
    • Initial version
  • 24th July, 2011
    • Added an addin setup to the download which is the result of compiling of the source code included
    • Removed unused reference from source code
    • Separated Requirement and How to use this code
    • Added How to switch off the debug mode when executing Office macro?
    • Added "quick execution" phrase after more in the How to Analyse the VBA Module section.
  • 14th August, 2011
    • Installation Bug fix (after the first run addin not found message appeared)
  • 20th August, 2011
    • Comparison with MZ-Tools 3.0 for VBA
Tag vba,error,handler
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架