Click here to Skip to main content
Click here to Skip to main content

VBA Extend

, 20 May 2014
Rate this:
Please Sign up or sign in to vote.
Format, add line numbers, error handling, expose a .NET library to VBA, and number to words in Office.

Downloads:

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:

  • Microsoft Excel users.
  • 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:

  • 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

This application will expose the following functions to Excel, Access  and other VBA host

  • Number to words converter in both English and Arabic
  • MW function to calculate the molecular weight of any chemical compound.
  • Desktop screen size can be known from the VBA
  • Many file related function
  • Clipboard functions: Clear, SetText, GetText, Ascii2Unicode &  Unicode2Ascii
  • Many other dot net function are exposed to VBA

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

How This Application Differ

  • Almost all other addin are 32 bit only
  • This application is not just an addin. It offer many other helpful resource
  • It is provide to you the simplest way to build your addon
  • It is very useful if you want to upgrade your VBA addin that written in VB6 to 64bit VB.Net

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 2013 (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 RegisterForComClients.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: Create, 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 Tools 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

Other Included Library:

VSErrorHandler:

This is an addin to visual studio that add try catch close to each VB  code item

CommonAddin Class:

This class is used to be the base class for both VBA and VS addin and  included the common procurers so no need to write it every time you write an  addin

NumbersToText:

This library will convert numbers to words in English or Arabic and could  be used in your office applications

Dim c As New NumberToArabicWords
Debug.Print(c.ConvertToWords(0)) 'لا شيء
Debug.Print(c.ConvertToWords(1)) 'فقط ليرةً سوريةً واحدة لاغير
Debug.Print(c.ConvertToWords(1000)) 'فقط ألف ليرةٍ سوريةٍ لاغير
Debug.Print(c.ConvertToWords(1015)) 'فقط ألفاً وخمس عشرة ليرةً سوريةً لاغير
Debug.Print(c.ConvertToWords(2)) 'فقط ليرتان سوريتان لاغير
Debug.Print(c.ConvertToWords(212000)) 'فقط مئتان وإثنا عشر ألف ليرةٍ سوريةٍ لاغير
Debug.Print(c.ConvertToWords(10002015)) 'فقط عشرة ملايينٍ وألفان وخمس عشرة ليرةً سوريةً لاغير
Debug.Print(c.ConvertToWords(3)) 'فقط ثلاث ليراتٍ سوريةٍ لاغير
Debug.Print(c.ConvertToWords(3000)) 'فقط ثلاثة آلاف ليرةٍ سوريةٍ لاغير
Debug.Print(c.ConvertToWords(3015)) 'فقط ثلاثة آلافٍ وخمس عشرة ليرةً سوريةً لاغير
Debug.Print(c.ConvertToWords(5)) 'فقط خمس ليراتٍ سوريةٍ لاغير
Debug.Print(c.ConvertToWords(44000)) 'فقط أربعة وأربعين ألف ليرةٍ سوريةٍ لاغير
Debug.Print(c.ConvertToWords(44015)) 'فقط أربعة وأربعين ألفاً وخمس عشرة ليرةً سوريةً لاغير
Debug.Print(c.ConvertToWords(100)) 'فقط مائة ليرةٍ سوريةٍ لاغير

Useful ready to import VBA Code:

You will find in the bin directory of the application some useful vba  code ready to use:

  •  Molecular Weight Calculation code that enable calculating Molecular  Weight form the line formula
  •  Useful MS Access database function to help in refresh linked tables and  to get the next empty integer value for the field in a query and to get  LastValue in a table

Expose .Net Controls as ActiveX

This library provide also exposing some .Net controls to be used in office VBA forms and in ActiveX containers

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 upgrade VB6 Addin to be fit with 64 bit office

VB6 and VB.Net is not similar and the upgrade in not simple. Here is a plan for code upgrade

  • Understanding the deferens between VB6 and .Net
  • Ensure that your code in compiled well in VB6
  • Install both VB6 and VS on the same machine
  • Choose the upgrade application to help you such as VBUC
  • Make the 1st upgrade with targeting just convert the code
  • Target 2 is to manually fix your code and make it compiled well
  • Last target is to complete the upgrade to make your application act as the VB6 version

for more info about upgrading please see CodeConverter

How to Analyze the VBA Module

Although we could move from one procedure to anther using CodeModule  object, the application will be more  quick execution than if we get all Module code, then analyze it using Regular Expressions and we use it to  analyze 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

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 RegisterForComClients.exe Application to register 3d  party assemblies:

The included 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 RegisterForComClients application in the DLL folder
  • Run  it application as  administrator.
  • Select the Dll to be registerd
  • Open you  office document
  • Add reference  to the generated tbl file to this document
  • Use the assembly in your VBA code

History

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

NewPast

Lebanon Lebanon
Mini Software Developer, VBA Macro Writer & Web developer
VB, ASP.Net, php & MySQL
------------------------------------------
Look at your eyes:
There is a developer behind every piece of code!
 
Eyes are too complex what about them!

Comments and Discussions

 
GeneralMy vote of 5 PinprofessionalJMK8929-Nov-13 5:20 
GeneralRe: My vote of 5 PingroupNewPast29-Nov-13 5:32 
QuestionRaise Exception in VBA from Dotnet PinmemberiSwoop17-Jun-13 12:09 
Thanks for this great article and have a question. Is there a simple way to raise an exception in VBA from dotnet. So that we can use the traditional vba error handling (on error goto ProcExit). We could then raise an error in dotnet and have the vba program catch the error?
 
Once again, I've seen that you've touched on it and if I've missed it in your code, maybe you could elaborate a bit more on this subject. Thanks again!g
AnswerRe: Raise Exception in VBA from Dotnet PingroupNewPast.Net18-Jun-13 4:40 
GeneralVery good PinmemberSlacker00721-Sep-12 1:12 
AnswerRe: Very good PingroupNewPast.Net21-Sep-12 8:15 
QuestionGood work there PinmvpSacha Barber16-Feb-12 0:43 
Questionwork along this line Pinmemberuildriks19-Oct-11 2:27 
AnswerThanks for your kind Pingroupchemnoor19-Oct-11 2:37 
SuggestionConsider mztools PinmemberTieske819-Aug-11 12:59 
GeneralRe: Consider mztools Pinmemberchemnoor20-Aug-11 19:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 20 May 2014
Article Copyright 2011 by NewPast
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid