Access to excel object model

Aug 18, 2010 at 8:49 AM

Maybe I'm missing something obvious but I can't work out how to declare variables so that I have access to the excel object model. This is possible isn't it?

I want to translate something from vba to use exceldna but I've hit a wall where I don't know what I should be looking at to advance. This is an example of some code which I'm having problems with

        Dim  oWb As Object ' workbook

        Dim oCaller As Object ' Range

        ' original excel: oCaller = Application.Caller
        oCaller = XlCall.Excel(XlCall.xlfCaller)
        oWb = oCaller.Workbook

I found examples saying that I should use XlCall.Excel(XlCall.xlfCaller) to get the calling instance of excel. From there I want to access the workbook object, but can't work out how to do this. Its returning an object, but is this an excel application object? How to I access the workbook object? Can this object value be typecast to something more useful?

I'm not looking to do anything complex, just access the workbook, find a sheet and pull some values from several cells, but I can't see how this should be done.

Ian

Coordinator
Aug 20, 2010 at 8:18 AM

Hi Ian,

I've tried to answer this and highlight some of the issues in my reply on the Excel-Dna Google group: http://groups.google.com/group/exceldna/browse_thread/thread/32ec88b6a62596ad, Some interesting comments followed there.

In summary: 

Excel exposes two add-in programming interfaces: the original Excel C API (also called the .xll API), and an Automation (COM) interface. Excel-Dna gives you full access to both of these interfaces, and makes the C API easy to use from .NET. For high-performance UDF functions, you should prefer the C API, and for macros the Automation API is probably nicer to deal with.

 

The XlCall.Excel(...) function is a helper to use the Excel C API, documented in the Excel SDK, the latest version of which is here: http://www.microsoft.com/downloads/details.aspx?familyid=9129A28E-D11C-4AC3-AEE3-CBB5496908CF&displaylang=en. The return from oCaller = XlCall.Excel(XlCall.xlfCaller) is not a Range object, but an ExcelDna.Integration.ExcelReference object, which is a thin wrapper that is useful mainly to pass to other C API functions. For example, you can get a string description of the range of the caller like this: strCaller = XlCall.Excel(XlCall.xlfRefText, oCaller, true).

All the XlCall.xlf... and XlCall.xlm.... values refer to constants defined in the xlcall.h header file that is part of the SDK. They are C API versions of the built-in Excel functions and macros, which are documented in the original Excel Macro help file, which you can download from here: Excel 7.0/97 Macro Function Help File for Excel 4.0 Macros.

 

Your Excel-Dna add-in can also us the Automation (COM) interfaces to interact with Excel. Care should be taken when accessing the Excel object model from within a UDF (as opposed to a macro) - this scenario is explicitly not supported by Microsoft, though it seems to work OK if you're careful. You should mark the function as IsMacroType=true, and take care on how you interact with Excel. As from any VBA or Automation Add-In function, your UDFs cannot set cells or change the Excel environment in any way (except for setting cell comments?).

If you want to use the Excel Automation (COM) interface from Excel-Dna, you need to get hold of the right Application object. The helper property ExcelDna.Integration.ExcelDnaUtil.Application will give you the Application object for the Excel instance hosting your add-in. From there you can access the object model as usual, either late-bound (using Object variables in VB, dynamic in C# 4, or using reflection), or by referencing an appropriate Interop assembly that defines the COM types. You can convert an ExcelReference to a Range object using: oCallerRange = ExcelDnaUtil.Application.Range(XlCall.Excel(XlCall.xlfRefText, oCaller, true)).

 

From macros, event handlers for CommandBars you set up or event handlers for the Ribbon interface, you can freely use the Automation (COM) interface - just get hold of the right root object using ExcelDnaUtil.Application.

 

I hope this clarified some things. Please post back if you have any more questions, or if any of this is unclear.

 

Cheers,

Govert