While developing any Microsoft Office add-in (Excel, Word, Outlook, Powerpoint, Access, etc.), you will eventually encounter performance problems and often it will be difficult to see what is causing the poor performance. One way to diagnose poor performance is to pause the code at random with Visual Studio and hopefully Visual Studio will stop inside the slow function. The odds are higher that it will stop in the slowest function because that’s what’s taking up the most time. The other more exact option is to use a profiler, even if your budget is zero you can still do it for free. SlimTune which you can get here: http://code.google.com/p/slimtune/ is fantastic for profiling C# add-ins (it supports .net 4.0). Figuring out how to use it wasn’t immediately obvious, so let me show you how to do it (I’m also writing this in case I forget).

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!

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

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.

In the previous article we looked at writing custom functions that can be used in Excel formulas using VBA. VBA has the advantage of being easy and quick. An automation add-in is another option that is also very easy assuming you know a bit about C++ programming. It is possible to use other languages such as VB, Delphi or C# but for this article we will be using C++.