Hello Excellers, it is indeed time for some more #macromonday fun. Today I am going to share with you how to take your Excel Macro, and turn it into an Excel Add In.
If you have created a an Excel Macro that is so awesome (or just useful!) that you think that many users could benefit from it then you could distribute it by turning it into an Excel Add In.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or DOWNLOAD example worksheets you can do so by clicking on the links below.
How To Excel At Excel – Macro Mondays Blog Posts.
[wpdm_package id=’10338′]
First Things First…What Is An Excel Add In?.
An Excel add in is simply an Excel File with an extension of .xlam or .xll. These files are loaded up when Excel starts up and contain VBA code (.xlam extensions) that add extra functionality to Excel. They usually save you time, and or help you avoid errors. There are Add Ins that are already available with Excel, and there are a lot of Add Ins that are a lot of third party Add Ins – a lot of them free to download. Just take a look around.
Convert Your Excel Macro To An Excel Add In Following The Steps Below.
Step 1. Add A Code Module To A New Workbook.
We first need to add a code module to an Excel workbook.
- Open the VBA editor by hitting ALT+F11 or the Developer Tab in your Excel workbook
- Select your workbook name in the Project Window
- Select Insert Module from the Insert Menu
Step 2. Create Your Macro.
Next we need to create the Macro that you want to convert to the Excel Add In.
- You can either use the Macro Recorder to create your Macro or write the code yourself.
- In this example I have written my own code to generate random numbers in a range of cells that the user selects.
Step 3. Add File Properties Information And Save As An Excel Add In.
- Click on the File Tab
- Click Show All Properties
- Click on the Title and Comments – These are the pieces of information that a user sees and uses when they are loading the Add In.
- Fill in these fields with something appropriate and informative to the user
- Click Save As and select the file extension .xlam
- The name you choose here will be the name of the Excel Add In
- The file path Excel saves it in automatically is changed, you can keep this location or change it
Step 5. Install The Excel Add In.
- Open an Excel workbook
- Developer Tab – Add Ins – Excel Add Ins
- Select your Add In and hit Ok in the dialog box
- The Add In will now be activated
Step 6. Assign The Macro To The Quick Access Toolbar.
- Select Customise Quick Access Toolbar
- In the Choose More Commands
- Macros – You will see the Macro you have created
- Select the Macro and hit Add to add it to the Quick Access Toolbar
You can now use the Macro in any workbook as you have enabled the Add In.
