Ways to Make Custom Functions in Excel
| Type | Disadvantages | Advantages |
|---|---|---|
| VBA | Category is "User Defined" only. | Very Easy |
| Automation AddIn | Category is Prog-ID. Function/parameter descriptions are not possible. Slower than XLLs. | Easy |
| RTD | Users to remember difficult RTD syntax e.g. =RTD("ServerName",, "MyFunction",..) | Fast, background calculation |
| XLL + RTD | Most complicated | Names you choose and background calculations |
Over the next few posts I will attempt to write a basic tutorial for using each technique. I'll start with the simplest option,using VBA.
Using VBA to Create Custom Functions
Using VBA is by far the easiest way to add custom functions to an Excel spreadsheet. The advantages are: it's quick, it's easy and it doesn't require anything extra. Performance wise VBA is lacking compared to the other techniques when using compiled languages (C/C++, Delphi). In many scenarios the performance gains of using one of the other techniques may not be worth it; if it saves 1 millisecond and it's used 100 times on a spreadsheet would anyone notice the difference?
In this simple example we will create a CAGR (Compound Annual Growth Rate) function. The definition of the CAGR function is shown below. An explanation of the CAGR function can be found here.
Definition of the CAGR function.
The first step is to create an Excel file, let's call it "VBAFunction.xlsm" or "VBAFunction.xls" if you are using a version prior to 2007. Switch to the Visual Basic Editor (ALT-F11), and right click on the project and add a module as shown in below.
Adding a module to place the custom function in.
Next change the name of the module to something more descriptive as shown in below. I changed "module1" to "CustomFunctionModule".
Changing the module name.
Now that we have the module all we need to do is write some code. Open up the module by double clicking on its name in the project tree. Enter the following code:
Option Explicit ' Must declare variables before use.
' Because this function is public and in a module it will be useable
' from Excel.
Public Function CAGR(BeginningValue As Variant, _
EndingValue As Variant, NumberOfYears As Variant) _
As Double
CAGR = Application.WorksheetFunction.Power( _
(EndingValue / BeginningValue), 1 / NumberOfYears) - 1
End Function
We start with "Option Explicit" because it forces us to declare variables using Dim before we can use them. This way we avoid bugs created by misspelling a variable name which is bound to happen eventually and could be a major headache to figure out. The function itself is very simple. In order for the function to be useable from Excel follow these two rules:
- Place the function in a module.
- Make sure it's declared "Public".
Now you can go back to the spreadsheet and use the function in a formula as shown below.
Using the CAGR function.
If you look in the function wizard you can find the CAGR function we just defined in the "User Defined" category. The parameter names will be the same as we defined above, there just will not be a description of the function or the parameters. That's one of the limitations of this technique, to get descriptions we need to use an XLL.
The function wizard. Sadly there is "No help available".