In this article, you will see how to indent code, add line numbers, do error handling, expose a .NET library and number to words to Microsoft Word, Excel, Power Point macros writers and Access VBA code This application is not just an add-in. It offers many other helpful resources. It provides to you the simplest way to build your add-on.
- Windows XP, Vista, 7, 8, 8.1, 10 (32Bit or 64Bit), 11
- .NET Framework version 4.8
https://dotnet.microsoft.com/en-us/download/dotnet-framework/net48 - Any version of Microsoft 365 or Office 2007, 2010, 2013, 2016, 2019, 2021 (32Bit Or 64Bit) or any single office application such as Word or Excel
What's New
- Windows 11 is supported now
If you are one or more of the following, then I hope that you will find this application helpful:
- Microsoft Word, Excel and PowerPoint macros writers
- MS Access forms designer
- VBA (Visual Basic for application) Developers
- Add in builder
How This Application Differs
- This application is not just an add-in. It offers many other helpful resources.
- It provides you with the simplest way to build your add-on
Background
Although VB6 went out, VBA is still needed to develop Office macros or Microsoft Access modules so VBA add-in is important.
Using This Application to Indent Your VBA Code and Add Error Handler
- Install the app.
- Open an Office document that contains a macro and edit the macro in the VBA window it or open the Microsoft Access Module.
- Write your VBA code.
- From the Add-Ins menu, choose to Add an 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 EorrorHandler
1: MsgBox("This is Macro1")
Exit Sub
EorrorHandler:
Debug.Print "Error in: Module1.Macro1." & Erl & vbNewLine & Err.Description
Debug.Assert False
MsgBox "Error in: Module1.Macro1." & Erl & vbNewLine & Err.Description
End Sub
Using This Application to Expose Some .NET to Your VBA Code
Declaring NP and Number to Words Objects
- Open an Office document that contains a macro and edit the macro in the VBA window it or open the Microsoft Access Module
- In the VBE Window, Select Tools, References, Browse, then select the file
C:\Program Files\OfficeProgrammingHelper\Bin\OfficeProgrammingHelper.tlb - Repeat this for the file
C:\Program Files\OfficeProgrammingHelper\Bin\NumberToText.tlb - Insert the following code in any VBA Module:
Public NP As New OfficeProgrammingHelper.NP
- Now you could use the NP object in any code in your project.
Using NP Object
You could call the NP object in your code to use the functions:
- You could put or get text with the following formats: Text, Unicode text, RTF, and 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
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(10002015))
Switch Off the Debug Mode When Executing Office Macro
- Open an Office document that contains a macro and edit the macro in the VBA window or open the Microsoft Access Module
- From the 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
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 enables calculating Molecular Weight from the line formula
- Useful MS Access database function to help refresh linked tables and get the next empty integer value for the field in a query and get
LastValue
in a table
Expose .NET Controls as ActiveX
This library also exposes some .NET controls to be used in Office VBA forms and in ActiveX containers.
Other Useful VBA Add-ins
Here are some useful add-ins you may be interested in:
Points of Interest
How to Write a VBA Addin
- Create a new class library project that uses V 4.8 .NET Framework and ComVisible.
- 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 in C# or VB:
private const string AddErrorHandlerCaption = "Add error handler to this file";
private CommandBarControl AddErrorHandlerMenuItem;
private CommandBarEvents AddErrorHandlerEvent;
void IDTExtensibility2.OnConnection(object Application,
ext_ConnectMode ConnectMode, object AddInInst, ref System.Array custom)
{
this.OnConnection(Application, ConnectMode, AddInInst, ref custom);
}
private void OnConnection(object Application,
ext_ConnectMode ConnectMode, object AddInInst, ref System.Array custom)
{
try
{
VBInstance = (VBE)Application;
AddErrorHandlerMenuItem = AddToAddInCommandBar(AddErrorHandlerCaption);
AddErrorHandlerToProjectMenuItem =
AddToAddInCommandBar(AddErrorHandlerToProjectCaption);
this.AddErrorHandlerEvent =
VBInstance.Events.CommandBarEvents(AddErrorHandlerMenuItem);
}
catch (Exception ex)
{
ErrMsg(ex);
}
}
void IDTExtensibility2.OnDisconnection
(ext_DisconnectMode RemoveMode, ref System.Array custom)
{
this.OnDisconnection(RemoveMode, ref custom);
}
private void OnDisconnection
(ext_DisconnectMode RemoveMode, ref System.Array custom)
{
On Error Resume Next
AddErrorHandlerMenuItem.Delete();
}
private void OnAddErrorClick
(object CommandBarControl, ref bool handled, ref bool CancelDefault)
{
}
private bool EventsSubscribed = false;
private void SubscribeToEvents()
{
if (EventsSubscribed)
return;
else
EventsSubscribed = true;
AddErrorHandlerEvent.Click += OnAddErrorClick;
}
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
VBInstance = CType(Application, VBE)
AddErrorHandlerMenuItem = AddToAddInCommandBar(AddErrorHandlerCaption)
AddErrorHandlerToProjectMenuItem = _
AddToAddInCommandBar(AddErrorHandlerToProjectCaption)
With VBInstance.Events
Me.AddErrorHandlerEvent = .CommandBarEvents(AddErrorHandlerMenuItem)
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
AddErrorHandlerMenuItem.Delete()
End Sub
Private Sub OnAddErrorClick(ByVal CommandBarControl As Object, _
ByRef handled As Boolean, _
ByRef CancelDefault As Boolean) Handles AddErrorHandlerEvent.Click
End Sub
- Generate a 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\VBE\6.0\Addins64\{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
Both Addins
and Addins64
keys are required for 64-bit addin.
- Create a setup project that installs and registers the class for com and adds the above reg file to the registry.
How to Analyze the VBA Module
Although we could move from one procedure to another using CodeModule
an object, the application will be more quick execution than if we get all the Module code, then analyze it using Regular Expressions and use it to analyze each procedure.
Regular Expressions Learning
Many applications may help you learn Regular Expressions:
- Expresso form http://www.ultrapico.com
- csharpregexdemo
About Office Addins
Microsoft Office supports add-ins written in VBA itself as word add-in (*.dotm) and access add-in (*.mda) files. Here are some useful application titles for devolving add-ins:
Related Article
History
- 20th July 2011: Initial version
- 4th February 2011: Adding expose .NET library to VBA
- 21st September 2012: Adding expose .NET controls and adding installation
- 7th August 2013: Some useful additions
- July 2015: Color class added
- January 2018: Fit with Windows 10
- 2nd January 2019
- 1st March 2020
- 2023/08/12 Version 4