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!

We’ve looked at VBA, Automation Add-ins and RTDs in previous posts. XLLs are significantly faster, and allow the developer to define the names/parameters of the functions. They’re also not that difficult to implement, although setting up the definitions of your functions can be a bit tricky. Unlike the other C++ add-ins, the XLL is not a COM server, it uses the Excel C API.

Managing COM resources in an Office add-in is very important since ignoring it means Excel might not even close with your add-in installed. Ironically C++ is a bit easier this way because Visual Studio can generate wrapper classes for you that take care of releasing the COM objects as long as they are created on the stack or deleted explicitly. You might think that the .Net garbage collector takes care of the objects but it doesn’t entirely, it just frees up the proxy object not the COM resource.

In a previous article I wrote about creating a buffer class in C# to access cells in a worksheet. The theory behind it was that accessing Excel one cell at a time is slow while doing it in batches is significantly faster. The same theory applies to all languages that you can program Excel in so in this article I present a class for VBA projects that does the same thing.

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

Have you ever wanted to create your own functions in Excel? There are several ways to accomplish this goal, all have their strengths and weaknesses. The options for creating a custom function are listed in the table below. In this post we'll be looking at the easiest option that requires only Excel. The other options are more difficult and time consuming but the advantage is increased speed.