How to Use Your Excel Add-In Functions in VBA

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 conflicts).

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

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 Project Explorer.

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.

NOTE: You 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 Editor...

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 Add-In's functions.

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

Take these factors into account and you won't have problems.