Using Excel Add-Ins to Share VBA Code
Part three of a four-part series of blogs

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.

  1. Sharing Code across Workbooks using Add-Ins
  2. Useful Examples of Code to Share
  3. Creating an Add-In (this blog)
  4. 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:

Example of add-in code in VBA

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:

Displaying VBAProject properties

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:

VBAProject properties dialog box

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:

File Save As menu

Choose this option from the File menu.

 

You can now choose to save your file as an add-in:

Saving workbook as an add-in

Follow the numbered steps below.

To do this:

  1. Choose the file type as Excel Add-In (*.xlam).  Be warned that you'll have to scroll a fair way down to reach this.
  2. Type in a name for this add-in.
  3. 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!

 

  1. Sharing Code across Workbooks using Add-Ins
  2. Useful Examples of Code to Share
  3. Creating an Add-In (this blog)
  4. Using Add-In Functions
This blog has 0 threads Add post