If you've written subs or functions in your own add-in, you can use them in your worksheet as you would with any other sub/function.
Subs can be called using shapes or buttons, the QAT, the Ribbon, or from the list of macros brought up by ALT+F8.
Functions can be called by typing the function name into a cell e.g.
=CUBE(3)
Calling Add-In Code from Another VBA Module
If you want to call a sub or function that resides in an add-in from another VBA module, you have to do something different.
One option is to add a reference from your workbook to the add-in. It's the same process as adding a reference to PERSONAL.XLSB.
Give Your Add-In a Nice Name
If the VBA editor, click on your add-in, and in the Properties box give it a more personalised or descriptive name. For mine I've called it Cube AddIn.
Add A Reference
Make sure that you click on the workbook you want to add the reference to, and from the VBA editor menu choose Tools -> References.
In the displayed list check the box beside your renamed add-in, and then click on OK.
You'll see that your workbook now has a new reference to the add-in.
Problems With This Approach
Whilst this works, you have to follow these steps for every workbook that you want to use the add-in code in and that can be cumbersome, but there is a better way.
Application.Run
If you use Application.Run to call your sub or function, you don't need to set a reference to the add-in. This is a much neater solution.
If we look at my CUBE function add-in as an example, it takes one number as an argument. So if we want the cube of 2, we would write
Result = Application.Run("CUBE",2)
Where Result is a variable I've declared to hold the value that CUBE returns.
Excel Slicer Formatting
myrthe
I have made multiple add-ins; each department of the company has its own.
To avoid copying subs and functions, I thought it would be a good idea to make a ‘common’ add-in, that will contain the code for common subs and functions (like; erase formatting).
In the department add-ins, I can link to the subs in the common add-in (like; save file as).
I made a reference between the projects. The referencing works fine, but it looks like it slows down the execution. Can this be caused by using functions through referencing? Or will this have another cause?
Catalin Bombea
Hi Myrthe,
If you are you calling a macro from another closed add-in, Excel will open that add-in before executing the code, the opening process might slow down the execution.
Kadr Leyn
Thanks for tutorial.
I created a Add-Ins on Toolbar using CommandBars.Add code :
…
Set MyBarNew = CommandBars.Add(“Sheet Navigate”, , False, True)
With MyBarNew
Set MyButton1 = .Controls.Add(msoControlButton)
With MyButton1
.Caption = “Update”
.Style = msoButtonCaption
.BeginGroup = True
.OnAction = “Update_Lst”
End With
…
Philip Treacy
Thanks Kadr
Mircea Nemteanu
Is it really that simple or is it a little bit more work to do, like shown at the URL below?
https://docs.microsoft.com/en-us/visualstudio/vsto/calling-code-in-vsto-add-ins-from-other-office-solutions?view=vs-2019
I was just testing with calling in Excel a method from the add-in, like in the example shown here, =CUBE(3), and errors out with #NAME?
I guess I need to go through all the process described in the Microsoft docs.
Philip Treacy
Hi Mirceau,
Yes it should be as simple as described in this post. The article you link to is a different thing, about developing add-ins with Virtual Studio.
If you have an add-in (which is just another VBA workbook) then creating a reference to it or using Appication.Run should work. Have you tested thiings with the CUBE() add-in I created?
If you are still having issues, please start a topic on the forum and supply the workbook and add-in and we’ll see if we can help.
Regards
Phil
Synkronizer
Thanks for sharing this valuable content on your blog. Also, I read many other blogs of your website all of these are very informative and useful blogs. I Added your website to my favorite list. keep sharing.