Custom Functions in Excel: Part V, XLL-RTDs

Introduction 

Why would you  want to call an RTD from an XLL? Recall that the way to call an RTD is through the RTD function in Excel, so the user needs to somehow remember how many parameters your function takes and in what order to supply them and even what the function is called. Good luck explaining that to your users!

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

Exhibit 1: The example from part 3 of this series. Not exactly user-friendly. I can’t even tell what it does can you?

Other Posts in the Series

• Part 1 – VBA 
• Part 2 – Automation Add-in
• Part 3 – Real Time Data Add-in
• Part 4 – XLLs 

XLL/RTD Hybrid  

Calling an RTD from an XLL is actually very easy. The RTD doesn’t define multiple functions with names but it’s not too hard to have different functions in the RTD. Just use the first parameter to be the function name and then call separate functions inside the RTD based on that name.

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

Exhibit 2: Calling multiple functions in one RTD server.

To create a “hybrid” of the two you need separate projects for the RTD and XLL. Assuming those functions are defined in an RTD let's move onto the XLL. The XLL can use a helper function to call into the RTD.  It’s relatively  straightforward but you need to be aware of a couple of things:

1. RTDs take string parameters only. If you want to restrict some parameters to numbers or dates, or boolean values you need to do so in the XLL but you need to take care of the conversion of those types to strings before calling CallRTD.
2. Make sure the XLL and RTD are using the same number format.
3. The XLL shouldn’t call the RTD if it’s being called from the function wizard.

XLOPER12 CallRTD( LPWSTR library, LPWSTR server, int numArgs, LPWSTR args[]) 
{
	
	int iStrings = -1;
	// 28 parameters max
	LPXLOPER12 lparameters[30];
	static XLOPER12 result;	
	
	int li = 0;
	lparameters[li++] = (LPXLOPER12)TempStr12(library);
	lparameters[li++] = (LPXLOPER12)TempStr12(server);	


	for(int i = 0; i < numArgs; i++)
	{
		lparameters[li++] = (LPXLOPER12)TempStr12(args[i]);
	}

	int rc = 0;

	rc = Excel12v(xlfRtd, &result, li, (LPXLOPER12 *)lparameters);

	if(rc != xlretSuccess)
	{
		debugPrintf("Excel12v returned %d.\n", rc);
	}

	/* Free the the temp memoery */
	FreeAllTempMemory();
	result.xltype |= xlbitXLFree;
	return result;
} 

Exhibit 3: The CallRTD helper.

Finally it’s time to use the CallRTD helper. The parameters to the function need to be converted into strings and the XLL function should return the result of the RTD. It looks like it should call the RTD, wait for it to finish and return the value but what happens is that the call to CallRTD returns immediately with the default value of the RTD function (it could for instance be “Loading…”). Then when the RTD is ready it notifies Excel and Excel calls the XLL function again, this time CallRTD will return the final value. 

__declspec(dllexport) double WINAPI GetStockPrice(char * stockTicker)
{
	LPWSTR args[1];
	WCHAR wticker[256];
	if(stockTicker == NULL || strlen(stockTicker) == 0)
		return 0.0;
	MultiByteToWideChar(CP_ACP, 0, stockTicker, -1, wticker, 256);
	args[0] = wticker;
	XLOPER12 result = CallRTD(L"RTDExample.RTDServer", L"", 1, args);
	return result.val.num;
} 

Now you can use it in a spreadsheet like this:

=GetStockPrice("ABC.XY") 

The function will return a random number that will update every couple of seconds with a new number (it moves randomly from the previous price so it's not all over the place).

RTD Design 

The RTD example in the download is a bit different from then the one from my RTD article. The old version started a new thread for each call to GetStockPrice. Imagine if the user were to fill 100k rows with GetStockPrice. The results wouldn’t be pretty, so this version has a limit number of threads (that can be adjusted), and instead of launching new threads the topic ID is just placed in a queue. Alternatively if the RTD were doing mostly network communication you could use an asynchronous socket and all the requests could go through a single worker thread. 

Requirements for the Sample 

The sample makes use of the 2010 XLL SDK and the boost libraries (1.5). Before building the project you need to build the boost libraries and the Framework project in the samples directory in the XLL SDK. Then add the directories to Visual studio (your directory structure may be different):

Include Files: 

c:\boost_1_50_0 

C:\2010 Office System Developer Resources\Excel2010XLLSDK\INCLUDE

Library Files: 

C:\2010 Office System Developer Resources\Excel2010XLLSDK\LIB\x64 

c:\boost_1_50_0\stage\lib  

 

 

Conclusion 

Using this technique gives you the best of both worlds. The function is easy to use for the end user (XLL) and you get the asynchronous goodness and the ability to push updates (RTD). Plus there's no downsides! If you already have an RTD that you've developed it's easy to add the XLL component because the RTD component needs no modification and the user will be able to mix both even on the same sheet.

CodeProject