Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / operating-systems / Windows

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

0.00/5 (No votes)
22 Aug 2011CPOL4 min read 17.6K  
Automation Add-In

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.

New Project

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

New Project 2 - Application Settings

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.

Add Class - ATL Simple Object

Exhibit 4: Choose a name for the new class.

Add Class 2 - Names

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

Add Class 3 - Options

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

Parameter attributesParameter typeParameter name
[in]DOUBLE *BeginningValue
[in]DOUBLE *EndingValue
[in]DOUBLE *NumberOfYears
[retval]DOUBLE *Result

Exhibit 6: The Add Method dialog.

Add Method

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

Add Method 2

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:

cagr_definition

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

C++
// 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.

Registration Error

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.

Resource Includes

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.

Registration Success

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.

Automation Servers

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.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)