Guide to Writing Custom Functions in Excel: Part I, Using VBA

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 ' 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:

1. Place the function in a module
2. 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”.