Steve Powell

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 except this time the screenshots will be from Visual Studio 2010. 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 a 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. 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 5: Don't need anything for the "File Type Handler Options".

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 list will probably be filled with statements like:

<p><code>Error??? 18??? error C2011: 'Font' : 'struct' type redefinition??? excel.tlh??? 4732??? 1??? RTDExample <br /></code></p> <p>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 which can be found on line 26947 (approximately):</p> <pre class="brush: cpp;toolbar: false" lang="cpp">extern "C" const GUID __declspec(selectany) LIBID_Excel =

{0x00020813,0x0000,0x0000,{0xc0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};</pre>

<p>Next we need the definition of IRtdServer itself which we can find on line 2204 (approximately):</p> <pre class="brush: cpp;toolbar: false" lang="cpp">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;

System Message: WARNING/2 (x:\Blog\venv\content\excel-custom-functions-3.rst, line 86)

Definition list ends without a blank line; unexpected unindent.

virtual HRESULT __stdcall ServerTerminate ( ) = 0;

};</pre> <p>Open up "stdafx.h" and comment out or remove the import line and paste in the definitions from the excel.tlh file. Now the project should build without any errors. The code should look like this:</p> <pre class="brush: cpp;toolbar: false" lang="cpp">//#import "C:Program Files (x86)Microsoft OfficeOFFICE11EXCEL.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;

System Message: WARNING/2 (x:\Blog\venv\content\excel-custom-functions-3.rst, line 117)

Definition list ends without a blank line; unexpected unindent.

virtual HRESULT __stdcall ServerTerminate ( ) = 0;

};</pre> <h2>Handling Requests</h2> <p>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:</p> <ol> <li>Excel encounters an =RTD() function in a formula. </li> <li>Excel calls <strong>ConnectData()</strong> 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. </li> <li>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. </li> <li>Once the server has a result it calls <strong>UpdateNotify()</strong> to notify Excel that it has a result. </li> <li>When Excel is ready it calls <strong>RefreshData()</strong> 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. </li> <li>Excel brings the values into the worksheet. </li> <li>When the worksheet is closed and Excel no longer needs a topic it notifies the RTD by calling the <strong>DisconnectData() </strong>and supplying the Topic ID of the topic that?s no longer needed. </li> </ol> <p>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 section with the comment ?IRtdServer Methods? with the following:</p> <pre class="brush: cpp;toolbar: false" lang="cpp"> // 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();</pre>

<p>Next we switch to SimpleRTDServer.cpp and start implementing those methods.</p>