VBA Extend

Downloads:

Download setup file that will install complied application and source code VBAExtendSetup.zip 660KB
Arabic Version of this article External link

Introduction:

To whom this application is written:

If you are interested of one or more from the following then I hope that you will find this application helpful:

MS Access forms designer.
Microsoft Office macros writers.
VBA (Visual Basic for application) Developers
Add in builder

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
Expose some dot Net library to extend VBA programing
Expose some dot Net Controls to be used in office or other com application.
Works With VBA6 and VBA7 for both 32 and 64 bit version of Microsoft office applications

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.
Support VBA7 for 64 bit version of Microsoft office applications

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

Microsoft Office 2010 (32 bit or 64 bit)
Microsoft dot net framework version 2
Visual Studio Tools for Office

How to use this code as VBA/VBE Addin:

Download the setup file and run it as administrator.
If registration fail please Run register.exe file from the application path as administrator.
Now you can you some dot net control in your MS Access form.
You can use this application to format you VBA code
Open an Office document that contains macro and edit the macro in VBA Window it or open Microsoft Access Module
Write your VBA code
From Add-Ins menu, choose Add error handler to this file
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 use this code to expose some dot Net library to VBA programing:

Open an Office document that contains macro and edit the macro in VBA Window it or open Microsoft Access Module
In VBE Window Select Tools, References, Brows then select the file VBAExtend.tlb form the folder VBAExtendBin in the application installing folder.
Now you could start writing the code using this library.
Please note that the Class NP is the main class that could be used to access most of other classes.
Before using this class please insert the following code in a VBA Module
```
Public NP As New VBAExtend.NP
```
After this you could use the NP object in any code in your project

Using NP object in your code:

NP.Clipboard: It is used to access computer clipboard. ex: put or get text from the clipboard with specific format, clear the clipboard or converting the text in it form or to Unicode.
You could put or get text with the following formats: Text, Unicode text, RTF, HTML.
```
Sub Test()
    NP.Clipboard.Clear
    NP.Clipboard.SetText "Some Text"
    NP.Clipboard.Ascii2Unicode
    NP.Clipboard.Unicode2Ascii
    Debug.Print NP.Clipboard.GetText
End Sub
```

NP.Directory: CreateDirectory, Delete, Exists, Move

Sub Test()
    If Not NP.Directory.Exists("C:\Temp") Then NP.Directory.CreateDirectory "C:\Temp"
    NP.Directory.Move "C:\Temp", "C:\Temp2"
    NP.Directory.Delete "C:\Temp2"
End Sub

NP.File: Copy, Create, Delete, Exists, Move, Shell.

Sub Test()
    If Not NP.File.Exists("C:\Temp.txt") Then NP.File.Create "C:\Temp.txt"
    NP.File.Move "C:\Temp.txt", "C:\Temp2.txt"
    NP.File.Copy "C:\Temp2.txt", "C:\Temp.txt"
    NP.File.Delete "C:\Temp2.txt"
    NP.Shell "C:\Temp.txt"
End Sub

NP.Screen: CM, Height, Width, TwipsPerPixelX, TwipsPerPixelY
NP.Text: EncodingConvert, EncodingConvertByCodePage, GetTextHeight, Md5Hash, TrimAny.
NP.SQL

Using VBADatabase Class:

This class is used in MS Access.
1st: db object should be declared in the same manner as NP Class.
Second: the default database should be set.

Then you could start coding:

Public db As New VBAExtend.VBADatabase
Sub Test()
    db.SetDB CurrentDB
   '...... your code
End Sub

db.IsField
db.ListFields
db.IsTableOrQuery
db.RefreshLinkChooseDB
SettingDelete, SettingGet, SettingSave: Manage the setting form setting table in this database.

Switch off the debug mode when executing Office macroأ¢â‚¬عک:

Open an Office document that contains macro and edit the macro in VBA Window it or open Microsoft Access Module
From Tolls menu choose your project properties then choose Protection
check the Lock project for viewing
Type a password and click OK
close your document and reopen it

Points of Interest

Exposing assembly to both 32 and 64 bit application:

Form project properties choose compile then choose advanced option then choose target CPU to AnyCPU
If your assembly is addin then build it as release only not as debug.
Register your assembly by by suitable regasm version
If your assembly has a strong name then remove all precious version form the GAC and then add it to GAC
The following code will show how to find the right path to regassm and gacutil and how to register an assembly for both 32 and 64 bit.

        Dim Name = "VBAExtend"
        Dim Ext = ".dll"

        Dim sDir = Windows.Forms.Application.StartupPath & "\"
        Dim FullName = sDir & Name & Ext
        If IO.File.Exists(sDir & FullName & ".TLB") Then
            IO.File.Delete(sDir & FullName & ".TLB")
        End If

        Dim ProgramFiles = System.Environment.GetEnvironmentVariable("ProgramFiles")
        Dim windir = System.Environment.GetEnvironmentVariable("windir")
        Dim GACUtilPath, RegasmPath As String
        If IO.Directory.Exists(ProgramFiles & " (x86)") Then
            GACUtilPath = ProgramFiles & " (x86)\Microsoft SDKs\Windows\v7.0A\bin"

            RegasmPath = windir & "\Microsoft.NET\Framework64"
        Else
            GACUtilPath = ProgramFiles & "\Microsoft SDKs\Windows\v7.0A\bin"
            RegasmPath = windir & "\Microsoft.NET\Framework"
        End If

        If IO.File.Exists(GACUtilPath & "\NETFX 4.0 Tools") Then GACUtilPath &= "\NETFX 4.0 Tools"

        If IO.Directory.Exists(GACUtilPath & "\x64") Then GACUtilPath &= "\x64"
        If IO.Directory.Exists(RegasmPath & "\v4.0.30319") Then
            RegasmPath &= "\v4.0.30319"
        Else
            RegasmPath &= "\v2.0.50727"

        End If

        Console.Clear() 'Cls
        Console.WriteLine("RegasmPath: " & RegasmPath)
        Console.WriteLine("GACUtilPath: " & GACUtilPath)
        Console.WriteLine()
        Console.WriteLine()
        If IO.Directory.Exists(windir & "\assembly\GAC_64\" & Name) Then
            IO.Directory.Delete(windir & "\assembly\GAC_64\" & Name, True)
        End If
        If IO.Directory.Exists(windir & "\assembly\GAC_32\" & Name) Then
            IO.Directory.Delete(windir & "\assembly\GAC_32\" & Name, True)
        End If
        If IO.Directory.Exists(windir & "\assembly\GAC\" & Name) Then
            IO.Directory.Delete(windir & "\assembly\GAC\" & Name, True)
        End If

        RegasmPath &= "\Regasm.exe"

        GACUtilPath &= "\gacutil.exe"

        Dim cmd = RegasmPath & " /nologo /unregister """ & FullName & """"

        Shell(cmd, AppWinStyle.NormalFocus, True)
        cmd = GACUtilPath & " /nologo /u " & Name & Ext
        Shell(cmd, AppWinStyle.NormalFocus, True)
        Console.WriteLine()
        Console.WriteLine()

        cmd = RegasmPath & " /nologo """ & FullName & """ /tlb:""" & FullName & ".tlb"""

        Shell(cmd, AppWinStyle.NormalFocus, True)
        Console.WriteLine()
        Console.WriteLine()

        cmd = RegasmPath & " /nologo """ & FullName & """ /codebase"
        Shell(cmd, AppWinStyle.NormalFocus, True)
        REM gacutil  /nologo /f /i %FullName%
        Console.WriteLine()
        Console.WriteLine()

        Console.Write("Press any key to conteniue")
        Console.ReadKey()

How to write a VBA Addin

Create new class library project that uses v2 of .NET Framework and ComVisible
The add in need ether PIA or Visual Studio Tools for Office
Add the following references:
- System
- System.Windows.Forms
- Extensibility = Microsoft Add-In Designer
- Microsoft.Vbe.Interop
- Microsoft.Office.Core
Add a class that Implements IDTExtensibility2

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

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

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

How this addin work with VBA7 and office 64 bit:

In Office 64 bit the VBAAddin class is not called by the COM.
The Office 64 bit called another class named SharedOfficeAddin
VBA7 is not support direct addins so this project act as a shared office application add in
The VBE is accessed form the host application
In SharedOfficeAddin Class and on OnConnection method we define the host application and call the another method for initializing the VBA add in

To access VBE form application class a register AccessVBOM value should be set to 1
The key is under local machine\Software\Microsoft\Office\14.0\Office application name\Security

Public Sub OnConnection(ByVal application As Object, ByVal connectMode As ext_ConnectMode, _
                        ByVal addInInst As Object, ByRef custom As Array) Implements IDTExtensibility2.OnConnection
    Try
        applicationObject = application
        addInInstance = addInInst
        '......
        Dim _VBE = applicationObject.VBE

        If _VBE.Version >= "7" Then
            If _VBAAddin Is Nothing Then _VBAAddin = New VBAAddin
            _VBAAddin.OnConnection(_VBE)
        End If

    Catch ex As Exception
    End Try
End Sub

How to Analyze the VBA Module

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

Regular Expressions Learning

Many applications may help you learn Regular Expressions:

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

About Office Addins:

Microsoft Office support add ins written in VBA it self as word add in (*.dotm) and access addin (*.mda) files
Hear is some useful application titles for devolving addins:

Using Register It Application to register 3d party assemblies:

The included register it application witch used to register this assembly could be used to register other dot net assemblies to enable using it in office applications or other com applications. As example we use it to register Molecular Weight Calculator freeware to be used in excel sheet.
to do so:

Put register it application in the DLL folder
Run register it application as administrator.
Open you office document
Add reference to the generated tbl file to this document
Use the assembly in your VBA code

History

20^th July, 2011:	Initial version
4^th Feb, 2011:	Adding expose dot net library to VBA
7^th Jul, 2012:	Using Register It Application to register other assemblies.
21^th Sep 2012:	Adding expose dot net controls and adding installation.