Macros are very powerful tool in Excel. One can create all logic and funda in them and use them repeatedly. But when it comes to distributing a macro, its a real pain. Recently I came across that same problem when one of my colleague asked me to create a function to spell the amount in words, also he wanted the this funtion could be distributed easily to other clients also. Working on the problem I came across Excel Add in.
An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. This is a very convieneint and Infact meant for the purpose of distribution.
How to create: Open an Excel file, Press ALT + 11, this will open a VBA window as shown here
Put your code in the function and save it. File>Save as \ Microsoft Excel Add-in (*.xla). Once saved and re-opened the Workbook will be hidden and can only be seen in the “Project Explorer” via the Visual Basic Editor. It is NOT hidden in the same way as the Personal.xls as this can be seen (and made visible) via Windows>Unhide.
Once completed users can easily install your Add-in like below
- Save a copy to C:\WINDOWS\Application Data\Microsoft\AddIns\ . If not any location, just take note of it for step 4.
- Open any Workbook.
- On the Tools menu, point to Add-Ins and click Browse. Locate your add-in from where you saved it, select it and then click OK.
- Ensure your add-in is in the Add-ins available: box and it is checked.
Now click OK and the add-in is installed.
Yes You have done it. Voilla, Add In is installed and will be available to any excel you open now. You can use the function written in xla files (shown as above) like any other microsoft build in function.
Enjoy the Excel Power!!!! Please do write in case any addtions, suggestions and comments, these matters a lot to me 🙂