BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
If you've written a killer function, you'll want to be able to share it between workbooks. The best way to do this is using an add-in.
- Sharing Code across Workbooks using Add-Ins
- Useful Examples of Code to Share
- Creating an Add-In (this blog)
- Using Add-In Functions
This blog is part of our Excel VBA online tutorial. Wise Owl also run excellent classroom-based VBA courses!
Posted by Andy Brown on 05 March 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Creating an Add-In
All of the instructions that follow are for Excel 2010 - the steps for Excel 2007 are nearly identical, and for Excel 2003 are similar.
To create an add-in, first create the code in VBA, then save the file with the right name, type and location.
Creating the Code for your Add-In in the VBA Code Editor
You can create an add-in just like for any other workbook:
Here we've created the 3 functions show in the previous part of this blog, and put them in a module which we've called UsefulCode.
You should now give your VBA project a sensible name, so it's easier to find:
Right-click on the project in the code editor and choose to show its properties, as here.
Type in a sensible name and description for the project, then select OK:
Here we've called the project WiseOwlCode.
Saving your Workbook as an Add-In
Now that you've created the functions and procedures that you want to share (note that by default they'll all be Public, not Private), you can create your add-in. Firstly, switch to Excel and choose to save your file:
Choose this option from the File menu.
You can now choose to save your file as an add-in:
Follow the numbered steps below.
To do this:
- Choose the file type as Excel Add-In (*.xlam). Be warned that you'll have to scroll a fair way down to reach this.
- Type in a name for this add-in.
- Click on the Save button.
You'll make life much easier for yourself if you stick to the folder offered, as this will be where Excel looks for add-ins by default.
Now that you've created and saved your add-in, it's time to use its functions within other workbooks!
- Sharing Code across Workbooks using Add-Ins
- Useful Examples of Code to Share
- Creating an Add-In (this blog)
- Using Add-In Functions