How to Use Your Excel Add-In Functions in VBA
Is There a Problem?
If you have built an Add-In to contain your Excel custom
functions you might have discovered that, although the functions
work fine in an Excel worksheet, you can't use them in your VBA
procedures in other workbooks. It's as if the Visual Basic Editor can't see
them. Well, that's because it can't! This article explains how
you can fix that.
First of all, consider whether this is really what you want to
do. Add-Ins are intended primarily for adding extra functionality to
your workbooks. When you load an Excel Add-In its functionality
immediately becomes available to all your workbooks. If you create a
VBA procedure that depends upon a custom function contained in
another workbook, that other workbook would have to be open whenever
you wanted to use its function. The same goes for a custom function
in an Add-In. If the Add-In is loaded that's fine, but supposing you
mail your workbook to someone else, or distribute it to your
workgroup. You have to remember to distribute the Add-In too. It
might be simpler to include a copy of the function in the workbook
code so your procedures have direct access to it (you may need to
make it a Private Function or change its name to avoid naming
I'm not saying don't do it. You just need to think about it
first, and if you're sure the Add-In will be available, then go
ahead. Here's how...
When Does the Problem Arise?
I'm writing a procedure for one of my workbooks. In my procedure
I want to use the RemoveSpaces function that I created a
while ago and saved in my Martin's Functions Add-In which is
currently installed in my copy of Excel. But when I try to run my
procedure I get an error.
The Visual Basic Editor is behaving as if the function doesn't
exist, but I know it does and I can see it if I look at the code
inside my Add-In. In fact, the code works fine if I run it from
inside my Add-In.
I need the Visual Basic Editor to be able to see the
functions in my Add-In from inside the code module of a different
Give Your Add-In a VBA Project Name
Every workbook has a VBA Project Name. It's called
VBAProject. You can change that name if you want but normally I
don't bother, because normally it doesn't matter.
Did you ever wonder why all the workbooks displayed in the
Project Explorer pane of the visual basic editor are called "VBAProject"?
If you have any of Microsoft's Add-In's installed you'll see that
they have a different name. The Microsoft developers have given
their Analysis ToolPak Add-In the VBA Project Name "funcres".
The first thing to do is give your Add-In a unique VBA Project
Name. This is because you are going to refer to it by this name in
the next step and if there are more than one with the same name the
Visual Basic Editor won't know which one to use.
In the Project Explorer pane select the name of your
Add-In. If it isn't already open, display the Visual Basic Editor's
Properties Window. You will see that there is only one
property, the Name. Type another name and press Enter.
You will have to obey the usual naming rules for VBA (i.e. no
illegal characters and no spaces). You will see that the name is immediately applied in the
Now save the changes to your Add-In. Make sure your Add-In is
selected in the Project Explorer and choose File > Save.
Set a Reference to the Add-In
In this step you tell the workbook in which you want to use your
Add-In's functions that the Add-In exists. You do this by Setting
a Reference to the Add-In. You might have come across this
technique before if you have wanted to write Excel code to
communicate with another program such as Outlook or Access.
If it's convenient, restart Excel at this point. This is because
your renamed Add-In will be reloaded and the list you are about to
see will be refreshed. If it isn't convenient, don't bother... read
the next paragraph and decide what you want to do.
Open a code module in the workbook in which you want to use the
Add-In's functions then go to Tools > References to open the
References dialog where you will see a list of all the
libraries and other objects (like Add-Ins) to which you can set a
reference. If you have restarted Excel this list will have been
refreshed and you will be able to find the Project Name that
you gave to your Add-In in the last step. Put a tick in the box next
to the name and click the OK button.
If you did not restart Excel you will have to find your Add-In
file by clicking the Browse button on the References
dialog. This opens the Add Reference window. Change the
Files of type: section to Microsoft Excel Files(*.xls;*.xla)
then browse to the folder where the Add-In is stored.
Select your Add-In and click Open. This adds your Add-In
to the list where you can select it and click OK.
don't have to do both of these procedures! Choose one or other
depending on whether or not you restarted Excel after changing your
Add-In's VBA Project name.
Now you will be able to use the
Add-In's functions in any module in the workbook in which you set
the reference and they will be recognized by the Visual Basic
If you look at the Project Explorer you will see that a
reference has been applied to the workbook...
It's important to remember that adding a reference applies only to
the workbook for which you carried out this procedure. You will have
to do it for each different workbook in which you want to use your
About Distributing Your Files
When you add a reference to an Add-In this link to the Add-In is
"hard-wired" into the file. If you move the file to another
computer, or distribute it to you co-workers the workbook will
expect to find the same Add-In, in the same place, on their
computers. Also, if the Add-In is moved or deleted from the computer
the workbook won't be able to find it and you're code won't work.
Some people advocate that the file and it's associated Add-In should
always be located in the same folder to avoid the problems this might
cause. You can, of course, set the reference again to fix the
Take these factors into account and you won't have