Excel VBA Add-in reference not listed

1
votes

I saved an Excel project as type "Excel Add-in" to the default directory under AppData/Roaming/Microsoft/AddIns. The extension is .xlam. Upon reopening Excel, this Add-in was listed under File-->Options-->Add-ins and I toggled the checkbox to select it.

In the VBA editor, this Add-in is displayed along with the other Add-ins listed under File-->Options-->Add-ins but none of the code can be called. It is not listed in the available references and, when I use this window to browse for it, it is not detected in the AddIns directory -- despite clearly being there.

I notice that all of the other Add-in projects that were installed without problem are .xla file extensions. We are using Excel 2010 but the problem is identical when I save the project as type "Excel 97-2003 Add-in" with an extension of .xla.

Advice is appreciated.

1
vbaexcelexcel-addins
In the VBA Editor click on ThisWorkbook for your Add-In. In the Properties Window, make sure that IsAddin is set to True. - Iron Man
Are the functions in your addin marked as Public? - nwhaught
@IronMan: That did it. Of course just saving the file as an Add-in wouldn't be enough. :/ If you want the SO cred, post this as the answer and I will accept it. Thanks. - mmmbeer
Also, click the check mark to show that you have accepted this answer and it resolved your issue. Thanks. - Iron Man
@nwhaught: Yes. Thanks. I don't have enough rep yet to upvote your comment. - mmmbeer

1 Answers

2
votes

In the VBA Editor click on ThisWorkbook for your Add-In. In the Properties Window, make sure that IsAddin is set to True. This also makes it easier to edit your addin. When you need to make changes to the add-in code, follow these steps:

  1. Set the property to False
  2. Make your edits
  3. Change the property back to True
  4. Click on the Save icon
  5. Close Excel.
  6. You will be prompted if you want to save the changes made to "addin.xlam", click yes.

And as @nwhaught suggested, make sure your functions are set to Public and not Private.