Office Programming Helper Indent VB Code

NewPast

Rate this:

1 Mar 2020CPOL

Indent code, add line numbers, err handling, Add number to words and more, Expose a .NET library to Microsoft Word, Excel, Power Point macros writers and Access VBA code

Indent code, add line numbers, 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

Screenshot

System Requirements

Windows XP, Vista, 7, 8, 8.1, 10 (32Bit or 64Bit)
.NET Framework version 4 or later
Any version of Microsoft Office 2007, 2010, 2013, 2016, 2019, 365 (32Bit Or 64Bit)or any single office application such as Word or Excel.

What's New

Minimize some system requirement

For Whom This Application is Written

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

Microsoft Word, Excel and Power Point 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 to you 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 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 EorrorHandler
1:  MsgBox("This is Macro1")
    Exit Sub
EorrorHandler:
    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

Using this application to expose Some .NET to your VBA code

Declaring NP and Number to words Objects

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, 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 a 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

NP.Clipboard It is used to access computer clipboard. Example: 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

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

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 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 also provides exposing 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 interested in:

Points of Interest

How to Write a VBA Addin

Create a new class library project that uses v2 of .NET Framework and ComVisible
The add in needs either 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 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
	{
		//save the VB instance
		VBInstance = (VBE)Application;
		AddErrorHandlerMenuItem = AddToAddInCommandBar(AddErrorHandlerCaption);
		AddErrorHandlerToProjectMenuItem = AddToAddInCommandBar(AddErrorHandlerToProjectCaption);
		//sink the event
		this.AddErrorHandlerEvent = VBInstance.Events.CommandBarEvents(AddErrorHandlerMenuItem);
		//.....More Code
	}
	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)
{
//INSTANT C# TODO TASK: The 'On Error Resume Next' statement is not converted by Instant C#:
	On Error Resume Next
	//delete the command bar entry 

	AddErrorHandlerMenuItem.Delete();
	//.....More Code
	//shut down the Add-In
}

private void OnAddErrorClick(object CommandBarControl, ref bool handled, ref bool CancelDefault)
{
   //.....Your click event code here
}

//INSTANT C# NOTE: Converted event handler wireups:
	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
        '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\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 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:

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 addin (*.mda) files. Here are some useful application titles for devolving addins:

History

20^th July, 2011: Initial version
4^th February, 2011: Adding expose .NET library to VBA
21^st September, 2012: Adding expose .NET controls and adding installation
7^th August, 2013: Some useful additions
July, 2015: Color class added
January, 2018: Fit with Windows 10
2^nd January, 2019
2020-03-01

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

---------------------------
There is a developer behind every piece of code!
DNA is too complex what about it!
No junk DNA; There is a functional role for noncoding DNA

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