How to create a custom add-in
You can write a function in VBA and then create a custom add-in like Solver quickly and easily.
Description of the process of creating a custom add-in:
1. First we create a custom function using VBA
We will create the compound interest calculation function. We start the Visual Basic editor by clicking on the developer tab and then click Visual Basic from the code command group. In the VBA editor we write the following code:
Function Compound_Interest(PV as Double, R as Double, N as Double) As
Compound_Interest = (PV * (1+R)^N) – PV
PV = Present Value or Amount of Loan or Investment
R = Interest rate per year
N = Loan or Investment period in years
A Double variable can hold a number between –1.79769313486231e308 and
–4.94065645841247e–324 for negative values or between 4.94065645841247e–324 and 1.79769313486231e308 for positive values.
After the function has been created in VBA we go back tou worksheet and enter ‘=com…”. The moment we enter these characters the function Compound_Interest is displayed. We can select it to place it in the cell, insert a bracket and input the arguments or parameters like PV, interest rate and period by clicking on the relevant worksheet cells. Finally we close the bracket and press enter to get the compound interest.
2. Now we click on the Microsoft Office Button, Save As and select Excel Macro Enabled workbook.
3. In the Save As dialog box we give the file name ‘compound_interest’ and next to the heading ‘Save as type’ text-box we choose Excel Add-In (*.xlsm) from the drop down menu. The file is saved in the Add-Ins folder automatically.
4. Finally we click on Save
5. We close the file
6. We click on the MS Office button
7. Select ‘Excel Options…
8. In the Excel Options window we click on Add-Ins on the left
9. On the right in the Add-Ins area we click on ‘Go…’ next to Manage
10. The Add-Ins window opens
11. We check the box next to Compound_Interest
12. Click on OK
13. We open a ‘New’ workbook and enter Amount, Interest rate per year, Period (years), 400000, 5% and 5 in the cells B3, B4, B5, C3, C4 and C5 respectively
14. In cell B6 we enter compound Interest
15. In cell C6 we calculate the compound interest
Watch the video to see how we have created a custom add-in for the compound interest calculation.
Note: You can also use the ‘insert function’ feature to use the add-in as shown in the image below the video!
Build an Excel Add-In