This adds your Add-In to the list where you can select it and click OK. Change the Files of type: section to Microsoft Excel Files(*.xls *.xla) then browse to the folder where the Add-In is stored. If you did not restart Excel you will have to find your Add-In file by clicking the Browse button on the References dialog. Put a tick in the box next to the name and click the OK button. 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.
#HOW TO USE EXCEL ADD INS CODE#
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. read the next paragraph and decide what you want to do. This is because your renamed Add-In will be reloaded and the list you are about to see will be refreshed. If it's convenient, restart Excel at this point. 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. You do this by Setting 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. Make sure your Add-In is selected in the Project Explorer and choose File > Save. You will see that the name is immediately applied in the Project Explorer. You will have to obey the usual naming rules for VBA (i.e. You will see that there is only one property, the Name. If it isn't already open, display the Visual Basic Editor's Properties Window. In the Project Explorer pane select the name of your Add-In. 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. The first thing to do is give your Add-In a unique VBA Project Name. The Microsoft developers have given their Analysis ToolPak Add-In the VBA Project Name " funcres". You can change that name if you want but normally I don't bother, because normally it doesn't matter.ĭid 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. Give Your Add-In a VBA Project NameĮvery workbook has a VBA Project Name. 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. In fact, the code works fine if I run it from inside my Add-In. 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. But when I try to run my procedure I get an error. 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. I'm writing a procedure for one of my workbooks. You just need to think about it first, and if you're sure the Add-In will be available, then go ahead. 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). You have to remember to distribute the Add-In too. If the Add-In is loaded that's fine, but supposing you mail your workbook to someone else, or distribute it to your workgroup. The same goes for a custom function in an Add-In. 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. When you load an Excel Add-In its functionality immediately becomes available to all your workbooks. Add-Ins are intended primarily for adding extra functionality to your workbooks. Well, that's because it can't! This article explains how you can fix that.įirst of all, consider whether this is really what you want to do. It's as if the Visual Basic Editor can't see them. 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.