Often you'll create a bit of code so useful that you want it to be available whenever you're using Excel. By far the best way to do this is to create an add-in. This tutorial shows:
3 examples of code that you might want to share between workbooks;
How to create an add-in; and
How to reference an add-in that you've created in code.
Let's start with why you might want to create an add-in. Here - fairly randomly picked - are 3 examples of functions that it might be useful to share between workbooks.
A Function to Return Someone's Name
In the worksheet below, we've used the FullName function to create a name suitable for mail merges, given a person's first and last names as inputs:
The function joins the first and last names together with a space in between, capitalising each part of the name.
Here is the code for a basic version of this function:
Function FullName(FirstName As String, LastName As String) As String
Dim ProperFirst As String
Dim ProperLast As String
ProperFirst = UCase(Left(FirstName, 1)) & LCase(Mid(FirstName, 2))
ProperLast = UCase(Left(LastName, 1)) & LCase(Mid(LastName, 2))
FullName = ProperFirst & " " & ProperLast
End Function
This function will struggle with names like Samuel L. Jackson and Kate O'Riordan, but this tutorial is about sharing code, not writing it!
A Function to Return a File Path
A much simpler function could be used to return the path to all of your files:
Public Function WiseOwlFilePath() As String
WiseOwlFilePath = "C:\Wise Owl Files\"
End Function
Using this function could ensure that if you ever move your files, you'll only need to change the file path in code in one place.
A Function to Determine if a Workbook is Open
To avoid reinventing wheels, you could share a function to find out if a given workbook is open or not:
Function DoesWorksheetExist(SheetName As String)
Dim w As Worksheet
For Each w In ActiveWorkbook.Worksheets
If UCase(w.Name) = UCase(SheetName) Then
DoesWorksheetExist = True
Exit Function
End If
Next w
DoesWorksheetExist = False
End Function
This function loops over all of the worksheets in the active workbook, trying to find the one we're looking for. If it can't find a worksheet with the name specified, it returns False; otherwise, it returns True.
Creating the Code for your Add-In in the VBA Code Editor
You can create an add-in just like for any other workbook:
Here we've created the 3 functions show in the previous part of this tutorial, and put them in a module which we've called UsefulCode.
You should now give your VBA project a sensible name, so it's easier to find:
Right-click on the project in the code editor and choose to show its properties, as here.
Type in a sensible name and description for the project, then select OK:
Here we've called the project WiseOwlCode.
Saving your Workbook as an Add-In
Now that you've created the functions and procedures that you want to share (note that by default they'll all be Public, not Private), you can create your add-in. Firstly, switch to Excel and choose to save your file:
Choose this option from the File menu.
You can now choose to save your file as an add-in:
Choose the file type as Excel Add-In (*.xlam). Be warned that you'll have to scroll a fair way down to reach this.
You'll make life easier for yourself if you stick to the folder offered, as this will be where Excel looks for add-ins by default.
Using Add-In Functions in Excel
The previous parts of this tutorial show you how to create add-ins; it's time now to use them. To do this, you must link to the add-in.
To add a reference for an add-in that you've created, show the Excel options dialog box (choose File => Options) to get to this, then go to the Add-Ins tab:
Choose Excel Add-ins then click on the Go... button as shown to choose the add-in(s) that you want to load:
If you stored your add-in in a non-default folder you'll need to click on this button to browse to it.
Find and select your add-in:
The add-in we created.
You'll then see this selected in the list:
Choose OK to accept this.
You can now use any of the functions in your add-in within any Excel workbook (this will be true even after you exit Excel and go back in):
Here we've used functions to return people's full names, the path to where files are stored and whether particular worksheets exist or not.
Using the Function Wizard to Include Functions
You'll find it easier to get at functions if you use the function wizard:
Click on this tool to insert a function.
You can then select the User Defined category (as shown here) to display a list of all the functions in your add-in:
When you choose User Defined, you can see the functions you've written and shared.
Using Add-In Functions and Procedures in VBA
To use a function in an add-in, first include the add-in in Excel (as shown above) , then reference it:
We're writing a module in a new workbook, and our DoesWorksheetExist function doesn't seem to exist.
First go into the References dialog box:
Select Tools --> References from the menu in the VBA code editor.
Now tick the box next to the add-in whose code you want to use:
Tick (in this case) the WiseOwlCode application.
You can now use any code in your referenced add-in:
That's more like it! The DoesWorksheetExist function is in a referenced application.
I'd recommend qualifying the function name with the object library to which it belongs:
If you type in the name of the add-in project, the code completes automatically.
