Excel Programming Interfaces

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, 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 calims 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 fewfunctionally 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." as per the below graph:

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.