Excel Programming Options: XLLs for performance

The following are the available ways to develop new software on Excel, that are still worthwhile and viable on recent versions of Excel, as mentioned on MSDN and other places:

  • C API and XLLs: essentially fully integrated DLLs that are accessed as Excel worksheet functions.
  • VBA: a popular option to quickly generate something which accomplishes a particular busines goal. Good for a small, low resource project, ultimately hard to get fully stable because of inherent language features.
  • COM: Legacy Windows infrastructure in C++; reasonably powerful, well supported historically by 3rd parties. VBA uses COM to interact with Excel, so it’s going one level lower for direct access.
  • The Microsoft .NET Framework: Microsoft’s multilanguage platform can also “speak” with Excel via Visual Studio Tools for Office (VSTO). Excel 2002 and later versions can access code resources contained within .NET assemblies.
  • Direct accces via C#: bypass the weight, and just use excel files as objects using tips from OpenOffice. See the ExcelReader on CodeProject for more information.
  • IRTDInterface: If you are using Excel 2002 or higher, you can call certain RealTimeData C# assemblies [as long as they follow the interface]. See
    http://msdn2.microsoft.com/en-us/lib…ffice.10).aspx
    http://msdn2.microsoft.com/en-us/lib…ffice.10).aspx
I’ve always been curious what the differences in performance are for using the above approaches. Using the following cell function or its programmed equivalents, the blogger Codematic decided to benchmark the various options of how to approach programming things in Excel:
{=INDEX(B1:B104,MAX(ROW(B4:B104)*(B4:B104<>0)))}
The table below summarizes the results.

Performance (Excel 2003)
57,000 formulas, calculated 10 times
Version Time Performance
Seconds % of Excel
Excel 27 100%
VBA 29 107%
VB6 32 119%
C# 260 963%
C++ (XLL+) 13 48%

While clearly the last option is the fastest one, it is worth pointing out that they were obtained with a commercial product that prouduces a custom platform for developing fast XLLs (Planatech), with the original projects potentially available for review. Also, you can just reach for the XLL development tools available from Microsoft, for example the Excel 2007 XLL development kit. So for anything that is performance critical, such as pricing applications, or requiring fast access to legacy systems, it seems the XLL approach is the best available one.

Personally I’m surprised that the C#/VSTO option takes such a hit. Codematic claims it’s related to “passing through the Primary Interop assemblies for Office 2003”, and that the costs may not outweigh the benefits of using this technology. The main benefit of using .Net, is that you can access code written in any other language which has also been optimized for .Net, and many options do exist. My guess is that Microsoft will eventually address this issue,since both Excel and .Net are their projects and directly within what they are able to influence.

Performance over .Net also significantly depends on exactly how you are using the inherent functionality. For example, DotNetPerls compares a few functionally equivalent syntaxes available to programmers. He says “using the Cells[] indexer on a range, or getting the Range with the Range property or get_Range() is very slow.”

From what I understand this has to do with cell value retreival across COM boundaries, according to CodingHillbilly, a self-professed Interop unExpert. Basically, this may be the same issue encountered in VBA , where it is better to interact with ranges and not cells individually if you can avoid it.

Takeaway:

So you have a few options when attempting to code something new against Excel. VBA is a great prototying tool when you need to quickly create something that works, and the worksheet functions aren’t good enough. C# and VSTO give you much greater flexibility to step outside of Excel and integrate it with many other systems and languages. But most importantly, reach for XLLs when you want speed or access to high end server resources.

Advertisements
Comments
One Response to “Excel Programming Options: XLLs for performance”
  1. Excel Expert says:

    Awesome post. You have shared very useful information.

    It is always required to keep you updated with various options in programming and this post would be very helpful for any Excel Programmer

What do you think? Leave a comment.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s