Excel-DNA Coding

[Rusul Ibrahimi]

Hi,

I am considering Excel-DNA for an add-in. My biggest issue is coding with it. Currently there is no IntelliSense with late binding. Alternatively, I can use PIA.

I have two questions:

1. How does using PIA reflects on performance?
   
2. With late binding, where is the documentation so I know how to access the Excel mode, ex: range, sheet, all sheets, workbook, multiple workbooks, etc.?

Thank you.

[Govert van Drimmelen]

Hi Rusul,

Using the Office PIA assemblies (in VB.NET or C#) will give you good performance, though late-binding in VB.NET is nearly as fast.

Using the 'dynamic' late-binding in C# is a bit slower than both.

It depends on what type of interaction you need with Excel. If you want to place large amounts of data onto the sheet, the C API (which you access in Excel-DNA via the XlCall.Excel helper) might be even faster, though it's harder to use.

Whether you use late binding (either VB.NET or C# 4 with 'dynamic') the object model is the same as with the Excel PIA assemblies, are is documented on MSDN here: http://msdn.microsoft.com/en-us/library/wss56bz7.aspx

It's also the same object model you use in VBA.

The C API sometimes fast, but poorly documented and incomplete compared to the COM object model. Some pointers to the Excel '97 help files (which document the macros available via the C API) are available here: https://exceldna.codeplex.com/wikipage?title=Excel%20C%20API&referringTitle=Documentation

A nice guide for moving from VBA to VB.NET with Excel-DNA was written by Patrick O'Beirne, and can be found here: http://sysmod.wordpress.com/2012/11/06/migrating-an-excel-vba-add-in-to-a-vb-net-xll-with-excel-dna-update/

I hope that gives you a start.

Otherwise it might help if you could explain what you will be doing on your add-in.

Regards,

Govert

[Rusul Ibrahimi]

Hi Govert,

Thank you for your quick reply. Does ExcelDnaUtil uses C API?

How is Excel-DNA using PIA faster than VSTO using PIA?

Regards.

[Govert van Drimmelen]

Hi Rusul,

Excel-DNA mostly uses the C API, but ribbons and stuff always work through COM. And the RTD features (including Excel-DNA's async support) works through the COM RTD interfaces.

You Excel-DNA has full access to the complete C API and the complete COM Automation interface.

You need to distinguish between worksheet UDF functions and macros. For functions, the only alternative to the C API is to make a COM add-in (no PIA involved) and the resulting functions are slower than the functions registered through the C API.

For macros that manipulate the sheet (from a menu button or shortcut key) you'd normally use the COM Automation interface. In that case using the PIA or late-bound VB.NET is faster than late-bound ('dynamic') C#. Whether you talk to the PIA classes from Excel-DNA or VSTO will be the same. VSTO also adds some higher-level wrapper classes of its own on top of the PIA classes (in the Microsoft.Office.Tools* namespaces). I have no knowledge or experience of these, but they're managed classes on top of the PIA classes.

> Does ExcelDnaUtil uses C API?

I presume you mean ExcelDnaUtil.Application?

No. ExcelDnaUtil.Application just returns the .NET Runtime Callable Wrapper (RCW) of the Application COM object. But you can cast this to the PIA Excel.Application class if you reference the PIA assembly. In .NET the cast to a COM type like that is basically a IUnknown.QueryInterface call, which will succeed in this case.

Internally the implementation of ExcelDnaUtil.Application might use the C API to get hold of the right Application object, but shouldn't have to worry about this.

> How is Excel-DNA using PIA faster than VSTO using PIA?

It would be the same.

[Rusul Ibrahimi]

Thanks again Govert. This is very useful for me.

Best regards,

Rusul