Steve listens and quickly understands requirements. He delivers a superior product on time, on budget and beyond the ask. I definitely recommend Steve's work!
Sunday, 24 April 2011 09:25 Last Updated on Monday, 23 July 2012 17:47
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.
|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 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:
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".