xlCall not playing nice with a non-modal form

Oct 27, 2011 at 9:20 AM

Good morning,

I have been developing an Excel Add-in using ExcelDNA, and I seem to have hit a barrier.

I am using the following code to get the current Workbook object

object objWB = XlCall.Excel(XlCall.xlfGetWorkbook);

I am calling this when utilising a function I wrote to create a custom document property which requires a workbook object to be passed in.

This executes when I click an 'OK' button on a non-modal form created in Visual Studio, however, this returns an Unhandled Software Exception of type: XlCallException.

Any idea as to why this might be an issue?

//Thanks

Coordinator
Oct 27, 2011 at 4:14 PM

Hi Adam,

You have to distinguish the Excel C API and the Excel COM automation interface, both of which you can fully access from Excel-DNA.

If you want to use the Worksheet and Workbook objects that you know from VBA, you are using the Excel COM automation interface. To use these from C# you need to reference the Microsoft.Office.Interop.Excel Primary Interop Assembly (PIA) or use one of the laternative methods for talking to the COM interface - 'dynamic' types for late binding in C# 4 or NetOffice (http://netoffice.codeplex.com) for version-independent assemblies.

Excel-DNA helps you hook up to the COM automation object model by letting you get the right Application object at the root of the object model. A call to "ExcelDnaUtil.Application" will get you the Application COM object for the Excel instance hosting your add-in. This is the Application object that is always magically available in the VBA environment. From there you can use the COM object model as usual - e.g. getting the active workbook by Application.ActiveWorkbook.

On the other hand the C API, which you would access through the XlCall.Excel function in Excel-DNA has no other dependencies and works with all Excel versions and great performance, but the API is harder to use. The documentation for the various C API functions, like GET.WORKBOOK which is the equivalent of your xlfGetWorkbook call, is available in a help file from Microsoft: http://support.microsoft.com/kb/128185. For this particular case, the GET.WORKBOOK function is an information function that returns details about an open workbook. E.g. the formula returns the name of the active sheet in the workbook named SALES.XLS: XlCall.Excel(XlCall.xlfGetWorkbook, 38, "SALES.XLS"). So I don't think that's really what you were looking for.

Also, you have to be quite careful when calling the C API that Excel is ready for you - in particular the C API cannot be called from a thread that is not the main Excel thread, and only a limited set of functions is available from within a worksheet function being calculated.

In your cases I suspect you would rather use the COM automation model to manipulate Excel, and so you will need to deal with the interop assembly issue and remember to get the right Application instance with a call to ExcelDnaUtil.Application.

Let me know if any of this needs further explaining.

Regards,

Govert