Have you ever wanted to create your own functions in Excel? There are several ways to accomplish this goal, all have their strengths and weaknesses. The options for creating a custom function are listed in the table below. In this post, we’ll be looking at the easiest option that requires only Excel. The other options are more difficult and time consuming, but the advantage is increased speed.
| Disadvantages | Advantages |
VBA | Category is “User Defined” only. | Very Easy |
Automation add-in | Category is Prog-ID. Function/parameter descriptions are not possible, slower than XLLs | Easy |
RTD | Need to remember clunky RTD syntax e.g. =RTD(“ServerName”,,”MyFunction”,…) | Fast, Background calculation |
XLL | Moderately complicated | Fastest, can choose names and document variables |
RTD + XLL | Most complicated | Names you choose and background calculation |
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 in exhibit 1 and an explanation of the CAGR function can be found here.
Exhibit 1: 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 exhibit 2.
Exhibit 2: Adding a Module to Place the Custom Function In
Next, change the name of the module to something more descriptive as shown in exhibit 3. I changed “module1
” to “CustomFunctionModule
”.
Exhibit 3: 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
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 in exhibit 4.
Exhibit 4: 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.
Exhibit 5: The Function Wizard
Sadly, there is “No help available”.