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:

PublicSub Macro1()
MsgBox("This is Macro1")
EndSub

Sample of code after applying this add in:

PublicSub Macro1()
OnErrorGoTo EH
11 MsgBox("This is Macro1")
ExitSub
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
EndSub

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 AsNew 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.

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.

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:

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