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.
Aug 20, 2010 at 8:18 AM
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.
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.