Guide to Writing Custom Functions in Excel: Part III, C++ RTD add-in

Introduction

In the previous article we developed a custom function in Excel using an Automation Add-in. It was fairly straightforward and didn’t take  long but performance isn’t one of it’s strengths and there is no way to properly document it within Excel for the user. The RTD is an automation add-in that implements the IRtdServer interface so creating it is similar. The RTD add-in is different from the other options in that it allows asynchronous calls which is a huge advantage when the function needs to make a call to a database or web service. Because RTDs are asynchronous, the user can continue to work in Excel while the function is calculating. The downsides of RTDs are that they do not appear in the function wizard and the user is forced to use a clunky syntax.

Asynchronous XLLs (topic of a future post)  are similar to RTDs in that they can pull data into Excel but the difference with RTD add-ins is that they can also push data into Excel. So if the RTD server provided stock prices and the value of a stock updated, the RTD could tell Excel that there is a new value and update Excel. An XLL cannot do that, the user would be forced to re-calculate the formula in order to get updated values.

There are several articles online about creating RTD servers. I find most of them overly complex, so in this post I hope to show you a more straightforward approach that takes advantage of the Visual Studio IDE and requires the minimum of code changes (more clicking, less code).

Getting Started

The first step is to create a C++ project (unmanaged). RTD servers are just a type of automation add-in so the first part of this post will be the same as the previous post. Select “File->New->Project” from the menu. Then select “Other Languages->Visual C++->ATL” from the template list.

Exhibit 1: Creating the new project.

Exhibit 2: Application settings page.

Implementing the IRtdServer Interface

Now that we’ve created a project we can create an ATL Simple Object that will contain our implementation of the RTD Server. Switch to the Class View, right click on the “RTDExample” project and select “Add->Class…” from the menu. Under ATL select the “ATL Simple Object” and click “Add”. All we need to specify in the wizard is the name of the class and that we don’t need aggregation. For this example I’m using “SimpleRTDServer”.

Exhibit 3: Adding the ATL Simple Object, page 1.

Exhibit 4: Enter the name in the “Short name” box.

Exhibit 6: On the “Options” page just click “no” for Aggregation. The rest are defaults.

Now that we have the new class we need it to implement the IRTDServer interface. Right click on CSimpleRTDServer in the class view and select “Add->Implement Interface”. Pick “File” and locate the Excel executable using the file browser. Then find IRtdServer from the list on the left and click the “>” button.

Exhibit 7: Using the Implement Interface Wizard to implement IRtdServer.

Making it Compile

Now try to build the project and it will complain about various things being redefined. Now it’s time to do a bit of editing. The error may  be filled with statements like:

Error    18    error C2011: 'Font' : 'struct' type redefinition    excel.tlh    4732    1    RTDExample

Double click on the error to go to the excel.tlh file. Since we don’t need all the definitions here it’s better just to copy out what we need and remove the import statement for excel.tlh. The first thing we need is the definition of LIBID_Excel. You can search for it or just use the code below.

extern "C" const GUID __declspec(selectany) LIBID_Excel =
    {0x00020813,0x0000,0x0000,{0xc0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};

Next we need the definition of IRtdServer itself which shouldn’t be too hard to find or you can just copy it from exhibit 8 if you’re feeling lazy.

Exhibit 8: IRtdServer definition.

struct __declspec(uuid("ec0e6191-db51-11d3-8f3e-00c04f3651b8"))
IRtdServer : IDispatch
{
    //
    // Raw methods provided by interface
    //

      virtual HRESULT __stdcall ServerStart (
        /*[in]*/ struct IRTDUpdateEvent * CallbackObject,
        /*[out,retval]*/ long * pfRes ) = 0;
      virtual HRESULT __stdcall ConnectData (
        /*[in]*/ long TopicID,
        /*[in]*/ SAFEARRAY * * Strings,
        /*[in,out]*/ VARIANT_BOOL * GetNewValues,
        /*[out,retval]*/ VARIANT * pvarOut ) = 0;
      virtual HRESULT __stdcall RefreshData (
        /*[in,out]*/ long * TopicCount,
        /*[out,retval]*/ SAFEARRAY * * parrayOut ) = 0;
      virtual HRESULT __stdcall DisconnectData (
        /*[in]*/ long TopicID ) = 0;
      virtual HRESULT __stdcall Heartbeat (
        /*[out,retval]*/ long * pfRes ) = 0;
      virtual HRESULT __stdcall ServerTerminate ( ) = 0;
};

Although it will compile at this point, we will need also need the IRTDUpdateEvent definition. It provides the call back method that we will use to notify Excel that results are ready. Without it, the add-in would not be able to return any values (typically considered important). The definition can be found in excel.tlh or in exhibit 9.

Exhibit 9: IRTDUpdateEvent definition.

struct __declspec(uuid("a43788c1-d91b-11d3-8f39-00c04f3651b8"))
IRTDUpdateEvent : IDispatch
{
    //
    // Raw methods provided by interface
    //

      virtual HRESULT __stdcall UpdateNotify ( ) = 0;
      virtual HRESULT __stdcall get_HeartbeatInterval (
        /*[out,retval]*/ long * plRetVal ) = 0;
      virtual HRESULT __stdcall put_HeartbeatInterval (
        /*[in]*/ long plRetVal ) = 0;
      virtual HRESULT __stdcall Disconnect ( ) = 0;
};

Open up "stdafx.h" and comment out or remove the import line and paste in the definitions from the excel.tlh file. The code should look like this:

//#import "C:\Program Files (x86)\Microsoft Office\OFFICE11\EXCEL.EXE" raw_interfaces_only, raw_native_types, no_namespace, named_guids, auto_search


extern "C" const GUID __declspec(selectany) LIBID_Excel =
    {0x00020813,0x0000,0x0000,{0xc0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};

struct __declspec(uuid("ec0e6191-db51-11d3-8f3e-00c04f3651b8"))
IRtdServer : IDispatch
{
    //
    // Raw methods provided by interface
    //

      virtual HRESULT __stdcall ServerStart (
        /*[in]*/ struct IRTDUpdateEvent * CallbackObject,
        /*[out,retval]*/ long * pfRes ) = 0;
      virtual HRESULT __stdcall ConnectData (
        /*[in]*/ long TopicID,
        /*[in]*/ SAFEARRAY * * Strings,
        /*[in,out]*/ VARIANT_BOOL * GetNewValues,
        /*[out,retval]*/ VARIANT * pvarOut ) = 0;
      virtual HRESULT __stdcall RefreshData (
        /*[in,out]*/ long * TopicCount,
        /*[out,retval]*/ SAFEARRAY * * parrayOut ) = 0;
      virtual HRESULT __stdcall DisconnectData (
        /*[in]*/ long TopicID ) = 0;
      virtual HRESULT __stdcall Heartbeat (
        /*[out,retval]*/ long * pfRes ) = 0;
      virtual HRESULT __stdcall ServerTerminate ( ) = 0;
};

Now one error should remain when you attempt to build:

Error	1	error C2259: 'ATL::CComObject' : cannot instantiate abstract class	

c:\program files (x86)\microsoft visual studio 8\vc\atlmfc\include\atlcom.h	186

This error will go away on it’s own once SimpleRTDServer has some code.

Handling Requests

We’ve got our RTD Server but it isn’t all that interesting yet. Let’s make it do something. For demonstration purposes, it won’t actually do anything useful, but we’ll have it delay a bit to simulate network activity then calculate something and return a result. The flow of events in an RTD server look like this:

1. Excel encounters an =RTD() function in a formula.
2. Excel calls ConnectData() and supplies the RTD server with the Topic ID and arguments supplied to the RTD function. The Topic ID is used to match results with calls to the RTD function.
3. The RTD server is then free to do some work. You can either queue up the request, or create a thread to process the request, etc.
4. Once the server has a result it calls UpdateNotify() to notify Excel that it has a result.
5. When Excel is ready it calls RefreshData() to request results from the RTD server. The RTD server then supplies the results that it has ready to Excel in the form of a SAFEARRAY with topic IDs and their values.
6. Excel brings the values into the worksheet.
7. When the a formula is deleted and Excel no longer needs a topic it notifies the RTD by calling the DisconnectData() and supplies the ID of the topic that’s no longer needed.

Let’s implement the methods of the IRtdServer interface. We’ll start by removing the default implementation found in SimpleRTDServer.h, which is now just returning E_NOTIMPL. Replace the SimpleRTDServer.h with the code from exhibit 10.

Exhibit 10: SimpleRTDServer.h.

// SimpleRTDServer.h : Declaration of the CSimpleRTDServer

#pragma once
#include "resource.h"       // main symbols
#include <map>
#include <string>
#include <boost>

#include "RTDExample.h"


#if defined(_WIN32_WCE) && !defined(_CE_DCOM) && !defined(_CE_ALLOW_SINGLE_THREADED_OBJECTS_IN_MTA)
#error "Single-threaded COM objects are not properly supported on Windows CE platform, such as the Windows Mobile platforms that do not include full DCOM support. Define _CE_ALLOW_SINGLE_THREADED_OBJECTS_IN_MTA to force ATL to support creating single-thread COM object's and allow use of it's single-threaded COM object implementations. The threading model in your rgs file was set to 'Free' as that is the only threading model supported in non DCOM Windows CE platforms."
#endif

using namespace ATL;
using namespace boost;
using namespace std;

// CSimpleRTDServer

class ATL_NO_VTABLE CSimpleRTDServer :
	public CComObjectRootEx<ccomsinglethreadmodel>,
	public CComCoClass<csimplertdserver,>,
	public IDispatchImpl<isimplertdserver, *wmajor="*/" *wminor="*/">,
	public IDispatchImpl<irtdserver, wmajor="*/" wminor="*/">
{
public:
	CSimpleRTDServer()
	{
	}

	DECLARE_REGISTRY_RESOURCEID(IDR_SIMPLERTDSERVER)

	DECLARE_NOT_AGGREGATABLE(CSimpleRTDServer)

	BEGIN_COM_MAP(CSimpleRTDServer)
		COM_INTERFACE_ENTRY(ISimpleRTDServer)
		COM_INTERFACE_ENTRY2(IDispatch, IRtdServer)
		COM_INTERFACE_ENTRY(IRtdServer)
	END_COM_MAP()



	DECLARE_PROTECT_FINAL_CONSTRUCT()

	HRESULT FinalConstruct()
	{
		return S_OK;
	}

	void FinalRelease()
	{
	}

public:


	// IRtdServer Methods
public:
	STDMETHODIMP ServerStart(IRTDUpdateEvent * CallbackObject, long * pfRes);
	STDMETHODIMP ConnectData(long TopicID, SAFEARRAY * * Strings, VARIANT_BOOL * GetNewValues, VARIANT * pvarOut);
	STDMETHODIMP RefreshData(long * TopicCount, SAFEARRAY * * parrayOut);
	STDMETHODIMP DisconnectData(long TopicID);
	STDMETHODIMP Heartbeat(long * pfRes);
	STDMETHODIMP ServerTerminate();

private:
	std::list<std::wstring> StringsAsList(SAFEARRAY * * Strings);
	IRTDUpdateEvent * m_callBackObj;
	std::map<long,>> m_results;
	std::list<long> m_new_results;
	boost::thread m_backgroundThread;
};
class CWorkerTask
{
private:
	long m_topicID;
	IRTDUpdateEvent * m_callBackObj;
	std::list<long> * m_pNewResults;
	std::list<std::wstring> m_args;
public:
	CWorkerTask(long topicID, IRTDUpdateEvent * callBackObj, std::list<long> * newResults, std::list<std::wstring> args);
	double operator()();
};
OBJECT_ENTRY_AUTO(__uuidof(SimpleRTDServer), CSimpleRTDServer)

Now switch to SimpleRTDServer.cpp (exhibit 11). The code here uses the boost libraries (http://boost.org) to handle the threading. If you prefer to use something else it shouldn’t be too hard to replace boost. The way this works is that in ConnectData we create a CWorker object that knows what the parameters were and is passed to a packaged_task. The packaged_task is linked to a shared_promise which holds the value calculated by the thread. When the thread is done it calls UpdateNotify() which tells Excel to ask for the values from the RTD. It then returns the value calculated that will be copied to the shared_promise in the m_results map. It calls UpdateNotify() before returning the value so there’s a possibility that RefreshData will be called before the result is ready. In RefreshData we check/wait for the value to be ready before getting it thus avoiding the problem.

Exhibit 11: SimpleRTDServer.cpp.

// SimpleRTDServer.cpp : Implementation of CSimpleRTDServer

#include "stdafx.h"
#include "SimpleRTDServer.h"
#include <boost>
#include <boost>
#include <string>
#include <list>

using namespace std;
using namespace boost;

// CSimpleRTDServer

// Called during loading of the dll. CallbackObject is what we use to notify Excel that
// we're done calculating some values and we're ready to refresh the data in the spreadsheet.
HRESULT CSimpleRTDServer::ServerStart(IRTDUpdateEvent * CallbackObject, long * pfRes /* <= 0 means failure */)
{
	if(CallbackObject == NULL || pfRes == NULL)
	{
		return E_POINTER;
	}
	m_callBackObj = CallbackObject;
	*pfRes = 1;
	return S_OK;
}
// Whenever a new topic is needed Excel will call this. The GetNewValues parameter tells Excel to use
// the previous value until the call to RefreshData or display the default while waiting.
HRESULT CSimpleRTDServer::ConnectData(long TopicID, SAFEARRAY * * Strings, VARIANT_BOOL * GetNewValues, VARIANT * pvarOut)
{
	CWorkerTask worker(TopicID, m_callBackObj, &m_new_results, StringsAsList(Strings));
	boost::packaged_task<double> pt(worker);
	
	
	m_results[TopicID] = boost::move(pt.get_future());
	boost::thread task(boost::move(pt)); // start thread.
	task.detach();
	return S_OK;
}

// After we call UpdateNotify, Excel calls this function to request the values
// of the topics that have been updated.
HRESULT CSimpleRTDServer::RefreshData(long * TopicCount, SAFEARRAY * * parrayOut)
{
	HRESULT hr = S_OK;
	if(TopicCount == NULL || parrayOut == NULL || (*parrayOut != NULL))
	{
		hr = E_POINTER;
	}
	else
	{
		*TopicCount = m_new_results.size();
		SAFEARRAYBOUND bounds[2];
		VARIANT value;
		long index[2];

		// Create a safe array		
		bounds[0].cElements = 2;
		bounds[0].lLbound = 0;
		bounds[1].cElements = *TopicCount;
		bounds[1].lLbound = 0;
		*parrayOut = SafeArrayCreate(VT_VARIANT, 2, bounds);
		int i = 0;
		for(list<long>::const_iterator itor = m_new_results.begin(); itor != m_new_results.end(); ++itor)
		{
			index[0] = 0;
			index[1] = i;
			
			VariantInit(&value);
			value.vt = VT_I4;
			value.lVal = *itor; // Topic ID
			SafeArrayPutElement(*parrayOut, index, &value);

			index[0] = 1;
			VariantInit(&value);
			value.vt = VT_R8;
			if(!m_results[*itor].is_ready())
				m_results[*itor].wait();
			value.dblVal = m_results[*itor].get(); // Result
			SafeArrayPutElement(*parrayOut, index, &value);			
		}
		m_new_results.clear();
		hr = S_OK;
	}
	return hr;
}
// Excel tells us that it doesn't need a topic by calling this.
// We remove the TopicID from the results. In a real add-in you'd
// probably want to check if the thread is still running and stop it.
HRESULT CSimpleRTDServer::DisconnectData(long TopicID)
{
	m_results.erase(TopicID);
	return S_OK;
}
// Excel calls this to determine if we're still alive.
// If pfRes is non-negative then we're still good.
HRESULT CSimpleRTDServer::Heartbeat(long * pfRes)
{
	HRESULT hr = S_OK;
	if(pfRes == NULL)
		hr = E_POINTER;
	else
		*pfRes = 1;
	return hr;
}
// Before Excel unloads the dll it calls this.
HRESULT CSimpleRTDServer::ServerTerminate()
{
	return S_OK;
}
// Converts the parameters supplied to ConnectData "Strings" into a list of wstrings 
// to make it easier to work with.
std::list<std::wstring> CSimpleRTDServer::StringsAsList(SAFEARRAY * * Strings)
{
	std::list<std::wstring> result;
	LONG lbound, ubound;
	SafeArrayGetLBound(*Strings,1,&lbound);
	SafeArrayGetUBound(*Strings,1,&ubound);
	
	VARIANT* pvar;
	SafeArrayAccessData(*Strings, (void HUGEP**) &pvar);
	for(long i = lbound; i <= ubound; i++)
	{
		BSTR bs = pvar[i].bstrVal;
		
		result.push_back(std::wstring(bs,SysStringLen(bs)));
	}
	SafeArrayUnaccessData(*Strings);

	return result;
}

// CWorkerTask

// This is what we'll be doing in the thread. Typically you might want to query a service of somekind here.
double CWorkerTask::operator()()
{
	this_thread::sleep(boost::posix_time::seconds(5));
	double dPerimeter = 0.0;
	for(std::list<std::wstring>::const_iterator itor = m_args.begin(); itor != m_args.end(); ++itor)
	{
		dPerimeter += boost::lexical_cast<double>(*itor);
	}
	m_pNewResults->push_front(m_topicID);
	m_callBackObj->UpdateNotify();
	
	return dPerimeter;
}
CWorkerTask::CWorkerTask(long topicID, IRTDUpdateEvent *callBackObj,std::list<long> * newResults, std::list<std::wstring> args)
{
	m_callBackObj = callBackObj;
	m_pNewResults = newResults;
	m_topicID = topicID;
	m_args = args;
}

Registering the RTD Server

Before we can use it the RTD server must be registered. When you build the project Visual Studio should register the DLL for you. In case you receive an error go to the command line (run as administrator), change directories to the debug folder of your project and register it manually (exhibit 13).

Exhibit 13: Registering the RTD server.

regsvr32 RTDExample.dll

Setting up debugging

To debug the project right click on the project in the solution explorer and click properties. Find the debugging page and change it to look like exhibit 14 (add excel.exe as the command).

Exhibit 14: Debug settings.

Using the RTD Server

To use the RTD server build the project and debug. Inside Excel enter the RTD formula from exhibit 12/13. At first it will display 0 but that will be replaced by the result in a few seconds. The Add-In example calculates the perimeter of a polynomial currently (or in other words it’s equivalent to the SUM function).

Exhibit 15: Place this formula in a cell to test the RTD.

=RTD("rtdexample.simplertdserver.1","",1.5,2.5,3.5)

Exhibit 16: The RTD server in action.

Conclusion

Although it’s not terribly exciting, the real purpose of an RTD is to do work that’s either slow (network, I/O of some kind) or to handle something that changes on its own (stocks, temperature) and needs to push data to Excel. UpdateNotify can be called at any time from the RTD and from any thread. It need not be a thread started by ConnectData but instead could be a thread that polls some external resource (e.g. fetches data from a web service) or is triggered by an external event (live feed of market data, or some sensor).

Calling the RTD server is really clunky as you’ve just seen, who is going to remember how to call your function if there’s no hint of how many parameters there are or what each one is.  We can solve these problems by combining the RTD with an XLL add-in. The XLL provides the better interface and the RTD does all the real work. We’ll look at that solution in a later post.

Download

Visual Studio 2005 Sample Solution:

RTD Add-in Solution Download

CodeProject