How to Create an Excel add in
Follow the steps below to create add in in Excel.
The ribbon
Click on Developer tab and then click on Visual Basic.
In the Project Explorer pane select VBAProject (Book1).
This selects the empty workbook.
Note: You can press ALT+ F11 to open Visual Basic directly than click on VBAProject (Book 1).
The code
Click on the Insert tab and select Module. A new window will open. Write the following line of code in the window:
Function Age(DoB As Date)
End Function
Press enter.
Note: The VBA Editor itself will write End function.
Explanation: Typing “Function” defines the code that follows as a function, as opposed to a macro or subroutine, which is denoted by the word “Sub”. Next is the function’s name, which can be anything you like. In this case it is Age. A function usually needs one or more “arguments”. These are entered in brackets after the function name. In this case only one argument is needed, the date of birth that will be used to calculate the person’s age. When you type the word “As” after the argument’s name the VB editor presents you with a list of possible entries. You can type or pick from the list.
Next type the following line of code:
Age = Int((Date - DoB) / 365.25)
The finished function should look like this
Function
Age(DoB As Date) Age = Int((Date - DoB) / 365.25)
End Function
Now press F5. A dialog box will open. Type name for your macro in the Macro Name field and click create. Now close the Visual Basic Editor by clicking on the Red Cross button on the top right corner. Click on Developer tab and then click on Macro. You will now be able to see your macro.
Note: to type a description of your function, Click the Options button to open the Macro Options dialog box.
The workbook containing your code module now has to be saved as an Excel Add-In (*.xla) file.
In the Excel window go to File, Save to open the Save As dialog. Enter a name for your Add-In file (the usual file naming rules apply) and use the Save as type: option to change the file type to Microsoft Excel Add-In (*.xla). Click ok.
Go to Tools, Add-Ins to open the Add-Ins dialog. If you have stored your Add-In in the default location you will see its name displayed in the Add-Ins available: window (if you have stored your Add-In in a different folder, use the Browse button to find it). Click on your Add-In’s name to see its description at the bottom of the dialog box.
To install your Add-In place a tick in the check-box next to your Add-In’s name and click OK.
As soon as the Add-In is installed its functions will be available in Excel. Find them in the User Defined section of the Function Wizard (Paste Function Tool) or simply type them into a cell as you would any built-in function.
That’s it, you have now successfully created your own add in.
Leave a Reply