Guide to Writing Custom Functions in Excel: Part II Automation Add-In

• Download Automation Add-in Solution

Introduction

In the previous article, we looked at writing custom functions that can be used in Excel formulas using VBA. VBA has the advantage of being  easy and quick. An automation add-in is another option that is also very easy assuming you know a bit about C++ programming. It is possible to use other languages such as VB, Delphi or C#, but for this article we will be using C++.

Creating an ATL Project in Visual Studio

The first step as you would expect is to create a project. Pick File->New->Project from the menu, which will bring up the “New Project Wizard”. Then select “Other Languages”, “Visual C++” and then “ATL Project” (see exhibits 1 and 2).

Exhibit 1: Choosing the “ATL Project” template.

Exhibit 2: For “Application Settings” leave the defaults.

Adding an ATL Class That Will Contain the UDF

Now that we have an ATL project, we add a class that will contain the user defined spreadsheet functions. From the class view right click on “CagrAutoExample” and choose “Add”, then “Class”. The wizard is straightforward, just pick a name on the second page and change aggregation to no on the third (see exhibits 3-5).

Exhibit 3: Add an ATL Simple Object.

Exhibit 4: Choose a name for the new class.

Exhibit 5: Change “Aggregation” to No. Leave the rest of the defaults.

Defining the CAGR Function

Next we’ll define the new function that we’ll be implementing. Go back to the “Class View” and right click on the interface IFinanicalFuncs and select “Add” and then “Add Method…”. Define a new function with the name CAGR and four parameters (as shown in exhibit 6 and 7):

Exhibit 6: The Add Method dialog.

Exhibit 7: IDL Attributes. Leave everything to the defaults.

Implementing the CAGR Function

Now that we’ve defined the method in the interface, it’s time to write the code of the CAGR function. The definition of CAGR is the following:

Open up FinancialFuncs.cpp and change it to look like this:

// FinancialFuncs.cpp : Implementation of CFinancialFuncs
#include "stdafx.h"
#include "FinancialFuncs.h"
#include <cmath />
// CFinancialFuncs
STDMETHODIMP CFinancialFuncs::CAGR(DOUBLE* BeginningValue, 
	DOUBLE* EndingValue, DOUBLE* NumberOfYears, DOUBLE* Result)
{
*Result = pow(*EndingValue / *BeginningValue, 1.0 / *NumberOfYears) - 1.0;
return S_OK;
}

Registering the DLL!

Now try registering the DLL with regsvr32 (e.g. at the command prompt, go to the debug folder within the project folder and type “regsvr32 CagrAutoExample.dll”). It will display an error message with error code 0x80070716 (exhibit  8).

Exhibit 8: Error message when trying to register the dll.

Fortunately, this problem is easy to fix. Go to the resource view and right click “CagrAutoExample.rc”. From the menu, select “Resource Includes”. In the dialog, add “Resource.h” to the “Read only symbol directives” section as shown in exhibit 9.

Exhibit 9: Visual Studio didn't add the include so we do it ourselves.

Now rebuild and register again. This time, you should be rewarded with success as seen in exhibit 10.

Exhibit 10: Simply adding “Resource.h” to “CagrAutoExample.rc” resolves the registration issue.

Testing It Out in Excel

Start Excel and go to the “Developer Tab” and click on “Add-Ins”, then click the “Automation…” button. A list of registered automation servers will appear. Find “FinancialFuncs” class in the list, click on it and then click OK (exhibit 11). The custom CAGR function will now be available.

Exhibit 11: Loading the automation server in Excel.

Next type in a few numbers and try out the new formula (exhibit 12). It will also appear in the function wizard under the category “CagrAutoExample.FinancialFuncs.1”.

Exhibit 12: CAGR in the function wizard.

Conclusion

The automation add-in is about as simple as a C++ add-in for Excel can get but it has very significant disadvantages that make it a poor choice compared to the other options. Not being able to choose the category name and being stuck with “CagrAutoExample.FinancialFuncs.1” is not ideal. The inability to document the function in the function wizard is also a problem, it would be nice if the custom function would look and act like a built-in. Performance-wise Automation Add-ins are slower than an XLL and they do not offer any unique advantages over XLLs, so the only real advantage to using them is to save writing a few lines of code.

Download

The file contains the sample code in a Visual Studio 2010 solution.

Automation Add-in Solution Download

CodeProject