Put your custom functions into an excel add-in
Mar
1
Written by:
2013/03/01 10:42 AM
This is primarily a HowTo document on "how to save your custom functions as an Excel Add-in".
Microsoft Excel is the most widely used application in business today. It is packed with features. From all sorts of functions to reporting and graphing.
After you use Excel for some time, you come to understand that although it has tons of functions that can do almost anything in Excel, there might be a few that are unique to your particular circumstance. It is then that you discover, macros and Visual Basic for Applications to automate repetitive tasks or even write your own functions. The next step is to take all your fantastic functions and to put that into an add-in so that you can share your creations with your collegues.
You can build a Microsoft Excel add-in to add tools or commands or functions to a user's Excel environment. In fact any excel workbook can be turned into an Excel add-in. But how do you distinguish an add-in from a regular Excel workbook. Well it's really quite easy. Excel add-ins have some distinguishing characteristics. These are:
- An add-in has the file extension .xla (Excel 97-2003) or .xlam (Excel 2007 onward) to indicate that it is an add-in
- After a Excel workbook is saved as an add-in, the workbook becomes invisible to the user. But you can still make use of the workbook within your add-in for storing calculations and data that your adding might use.
- Users cannot use the SHIFT key to bypass functionality or events built into your add-in
- Excel messages (alerts) are not displayed by code running in an add-in
Save your workbook as an Excel Add-in
Here are some simple steps to create an Excel add-in.
- Open a regular blank workbook, or open a workbook that contains your functions and macros.
- Create or add new functions by using the Macro recorder or the Visual Basic Editor under the Developer tab.
- Test your functions to make sure they work correctly.
- Save your workbook and select the "Excel Add-in" as the "Save As Type"
- Give you Excel add-in a descriptive name.
You can save your excel add-in any where you like. Generally excel will save the add-in in the default add-in folder. This might differ from operating system as well as excel version. But generally you can find it in Users\%user name%\AppData\Roaming\Microsoft\AddIns. On My vista PC as well as my Windows 7 Lapop my addins are saved in C:\Users\rbravery\AppData\Roaming\Microsoft\AddIns. Other locations might be C:\Documents and Settings\Username\Application Data\Microsoft\AddIns\
Load your Excel Add-in
In order to have access to the functionality in your Excel Add-in you need to tel Excel to load the add-in to make it available to you. To do that follow these general directions:
- Go to Excel options
- Select Add-in
- Click on the "Go" Button near the bottom of the dialog box. Adjacent to "Manage Excel Add-ins"
- From the available list of add-ins, select and check your add in that you recently saved.
- Click the "OK" button
Your Excel Add-in is now ready to be used in all and any workbook opened on your PC. The only caveat is that the add-in and the functionality that it provides does not travel with the workbook. So if you open that workbook on another machine, then the add-in and functionality will not be available. Unless you install the add-in onto the new machine.
Where to from here?
Well the sky is the limit. Only you own imagination, or lack thereof would limit what you could do. Perhaps you have never created a macro nor have you created a User Defined Function. Perhaps you are unfamiliar with VBA and Macros. Do not be. I will be posting a few more articles on how you can create simple Macros and Functions. Then using this article you will be able to create your own add-ins in no time.
Otherwise do a Google search, there is tons of information on VBA and Macros out there on the net. Go to the Microsoft sites and learn there.
About macros in Excel - Excel - Office.com
Record and use Excel macros - Excel - Office.com
Introduction to custom macros in Excel - Excel - Office.com
Quick start: Create a macro - Excel - Office.com
blog comments powered by