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
{
virtual HRESULT __stdcall ServerStart (
struct IRTDUpdateEvent * CallbackObject,
long * pfRes ) = 0;
virtual HRESULT __stdcall ConnectData (
long TopicID,
SAFEARRAY * * Strings,
VARIANT_BOOL * GetNewValues,
VARIANT * pvarOut ) = 0;
virtual HRESULT __stdcall RefreshData (
long * TopicCount,
SAFEARRAY * * parrayOut ) = 0;
virtual HRESULT __stdcall DisconnectData (
long TopicID ) = 0;
virtual HRESULT __stdcall Heartbeat (
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
{
virtual HRESULT __stdcall UpdateNotify ( ) = 0;
virtual HRESULT __stdcall get_HeartbeatInterval (
long * plRetVal ) = 0;
virtual HRESULT __stdcall put_HeartbeatInterval (
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:
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
{
virtual HRESULT __stdcall ServerStart (
struct IRTDUpdateEvent * CallbackObject,
long * pfRes ) = 0;
virtual HRESULT __stdcall ConnectData (
long TopicID,
SAFEARRAY * * Strings,
VARIANT_BOOL * GetNewValues,
VARIANT * pvarOut ) = 0;
virtual HRESULT __stdcall RefreshData (
long * TopicCount,
SAFEARRAY * * parrayOut ) = 0;
virtual HRESULT __stdcall DisconnectData (
long TopicID ) = 0;
virtual HRESULT __stdcall Heartbeat (
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:
- Excel encounters an =RTD() function in a formula.
- 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.
- 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.
- Once the server has a result it calls UpdateNotify() to notify Excel that it has a result.
- 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.
- Excel brings the values into the worksheet.
- 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.
#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;
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:
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.
#include "stdafx.h"
#include "SimpleRTDServer.h"
#include <boost>
#include <boost>
#include <string>
#include <list>
using namespace std;
using namespace boost;
HRESULT CSimpleRTDServer::ServerStart(IRTDUpdateEvent * CallbackObject, long * pfRes )
{
if(CallbackObject == NULL || pfRes == NULL)
{
return E_POINTER;
}
m_callBackObj = CallbackObject;
*pfRes = 1;
return S_OK;
}
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)); task.detach();
return S_OK;
}
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];
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; 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(); SafeArrayPutElement(*parrayOut, index, &value);
}
m_new_results.clear();
hr = S_OK;
}
return hr;
}
HRESULT CSimpleRTDServer::DisconnectData(long TopicID)
{
m_results.erase(TopicID);
return S_OK;
}
HRESULT CSimpleRTDServer::Heartbeat(long * pfRes)
{
HRESULT hr = S_OK;
if(pfRes == NULL)
hr = E_POINTER;
else
*pfRes = 1;
return hr;
}
HRESULT CSimpleRTDServer::ServerTerminate()
{
return S_OK;
}
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;
}
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
