• Download addin setup - 160.61 KB
• Download source - 28.69 KB

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

• 20^th July, 2011
• Initial version
• 24^th 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.
• 14^th August, 2011
• Installation Bug fix (after the first run addin not found message appeared)
• 20^th August, 2011
  • Comparison with MZ-Tools 3.0 for VBA