Steve Powell

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" (figures 1 and 2).

Figure 1: Choosing the "ATL Project" template.

Figure 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 figures 3-5).

Figure 3: Add an ATL Simple Object.

Figure 4: Choose a name for the new class.

Figure 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 figures 6 and 7):

Figure 6: The Add Method dialog.

Figure 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 (figure 8).

Figure 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 figure 9.

Figure 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 figure 10.

Figure 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 (figure 11). The custom CAGR function will now be available.

Figure 11: Loading the automation server in Excel.

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

Figure 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.