VBA TUTOR NEWSLETTER ~ TUTORIAL FOURTEEN: CREATING AND USING AN 'XLA' ADD-IN FILE

>GRSoftware >VBA Tutor >Newsletter Tutorials >Tutorial 13 >Tutorial 15

VBA TUTOR NEWSLETTER ~ TUTORIAL FOURTEEN:

CREATING AND USING AN 'XLA' ADD-IN FILE.

For advanced users wanting to distribute their project(s), 'xla' add-ins are the ideal solution for storing and distributing complete applications, or custom functions and utilities.

STEP ONE: Introduction to 'Add-in's'

A Microsoft Excel add-in is a workbook with an .xla extension. They are installed by default in the "\Program Files\Microsoft Office\Office\Library folder". Loading an add-in makes the feature available in Excel and adds any associated commands to the appropriate menus.

STEP TWO: Creating the Code for the 'Add-In'

The first task is to create the 'AddinInstall()' and 'AddinUnnstall()' events. The install event adds a menu item in the 'Format' menu. The uninstall event deletes the menu item from the 'Format' menu. If you have trouble following the code, use the help in the VB Editor for reference to the 'objects' ( eg. 'CommandBars' ) and 'methods' ( eg. 'add' ) used.

In the project window of the VB Editor, enter this following code into the 'ThisWorkbook' module.

Option Explicit
Private Sub Workbook_AddinInstall()
Dim objCmdBrPp As CommandBarPopup
Dim objCmdBtn As CommandBarButton
Set objCmdBrPp = Application.CommandBars ("Worksheet Menu Bar").Controls("Format")
On Error Resume Next
Set objCmdBtn = objCmdBrPp.Controls.Add (Type:=msoControlButton, before:=4)
With objCmdBtn
.Caption = "&Favorite Date Format"
.OnAction = "AddInCode.favDateFormat"
End With
Set objCmdBrPp = Nothing
Set objCmdBtn = Nothing
End Sub

Private Sub Workbook_AddinUninstall()
Dim Count As Integer
On Error Resume Next
For Count = 1 To Application.CommandBars("Format").Controls.Count
If Application.CommandBars("Format").Controls(Count).Caption = "&Favorite Date Format" Then
Application.CommandBars("Format").Controls(Count).Delete
Exit Sub
End If
Next Count
End Sub

The procedure to be run must reside in a separate 'AddInCode' module.

In the VB Editor, insert a new module. [ 'Insert | Module' ]
Press F4 to open the Properties window, and set the module's Name property to "AddInCode".
Copy the following code into the AddInCode module.

[ You might notice that the main part of this procedure was used in 'Tutorial 13'. For its use here in a MenuBar, we add some code to test if there is a selection to prevent errors. ]

Public Sub favdateFormat()
If Selection Is Nothing Then
Exit Sub
End If
Selection.NumberFormat = "dd-mm-yy"
End Sub

In Excel on the 'File' menu, click 'Properties'. Enter "Favorite Date Format" for the 'Title', with something like "Sample add-in" in the 'Comments'.
(The title identifies an add-in in the Add-In Manager. The comment is displayed when an add-in is selected in Add-In Manager)
Click 'OK' to close.
Save your project to a working folder as "favDate.xls".

STEP THREE: Saving, Loading and Using the 'Add-in'

With 'favDate.xls' open in Excel, on the 'File' menu select 'Save As'.
Move to the '\Program Files\Microsoft Office\Office\Library' folder
Set the file type to 'Microsoft Excel Add-In', and click 'Save'. (This creates a file named 'favDate.xla' in the folder.)
Close 'favDate.xls' and open a blank workbook.
On the 'Tools' menu, select 'Add-Ins'.
Check the box indicating the 'Favorite Date Format' add-in and click 'OK' to close.

The procedure in the 'AddInCode' module as well as the events 'AddinInstall()' and 'AddinUnnstall()' will now be available in all workbooks. To test ...

Enter a date into a cell, such as "1-1-00", then select that cell.
On the 'Format' menu, select 'Favorite Date Format'.
The date in the cell should be formatted the way you prefer.

OK! Thats it! All done.

NOTE: NOTE: NOTE: NOTE: NOTE: NOTE: NOTE: NOTE:

Don't feel like copying the code or having trouble with the code, then download 'favDate.xla'.

This tutorial was based upon a more complex Microsoft Developer Network tutorial that can be found @ MSDN ~ Creating Add-ins in Microsoft Excel 97

HOPEFULLY, THIS CODE EXAMPLE WILL ADD TO YOUR ABILITY TO BECOME A VBA POWER USER!