Confusion regarding use of API calls

[Ian Murphy]

I've been trying to use the excelDNA library and I've got some
questions. In one of the examples there is a funcion:

' An easy way to add a menu and menu items for a command....
' This command sounds a tone and sets cell C3 to the value 42
<ExcelCommand(MenuName:="&My Menu", MenuText:="Ring &Bell")> _
Sub RingBell()
Console.Beep()
Dim r as New ExcelReference(3,3)
r.SetValue(42)
End Sub

the line 'dim r as new Excelreference(3,3)' creates a structure
pointing to the cell at row 3, column 3. However it doesn't indicate
on which workbook or worksheet the cell is going to be. How can I
specify the cell, from the worksheet, from the workbook?

From what I can tell you create a reference to each cell, but if you
need to run through, say 10000 rows and examine 2 cells on each row,
are you supposed to create objects for each cell? Is there no way of
just saying

worksheet.cells(3,3).value
?

[Govert van Drimmelen]

Hi Ian,

Firstly, within Excel-Dna *macros* (not functions) like this example,
I believe it is safe to call the Automation (COM) API just like you
would from a COM add-in or from a VBA macro. So I think it is safe to
call:
value = ExcelDnaUtil.Application.ActiveSheet.Cells(3,3).Value

If you want to use the XLL API, you can use the ExcelReference helper
class as in the example you quote. ExcelReference thinly encapsulates
a range reference as used by the API functions. The ExcelReference
constructor takes up to five arguments - RowFirst, RowLast,
ColumnFirst, ColumnLast, SheetId, so you can refer to a large array
without issue.

The SheetId here is an internal handle Excel has for each sheet. It
can be retrieved from a sheet name using the xlSheetId call, which
returns an ExcelReference containing the right SheetId, like so
xlrefSheet2 = XlCall.Excel(XlCall.xlSheetId, "Sheet2")
For a different workbook, use the standard "[BOOK1.XLSX]Sheet1" style
referencing.

With no sheetId passed to the ExcelReference constructor, the active
(front) sheet is used.

I think this answers your immediate questions.

---------------------

For most cases, your macros will be easier to write if you use the COM
API. One reason to use the SDK API is to have consistency between your
macros and your user-defined functions. There might also be
performance advantages to using the SDK API.

Using the COM API from within user-defined *functions* is not really
supported.
However, if you mark your function as IsMacroType=true, I believe you
are in the same position as VBA or an Automation add-in, so you
actually have some limited access to the COM API. As an example, I
also add a function at the bottom that calculates the sum of cells
having a given color (stolen from the OzGrid site). I have made a
wrapper and some helper functions to expose this VBA-style function
(which takes Range arguments) as an Excel-Dna function.
I'd love to know if anyone has a problem when running functions like
these which only do 'friendly' calls to the Excel Automation
interfaces. It works fine for me in limited testing. Of course, trying
to set the values of cells from within a function is not (easily)
possible, just like in VBA or Automation Add-Ins.

I've rambled on a bit. An example to look at is pasted below.

Please post back if you need more clarification.

Kind regards,
Govert

<DnaLibrary>
<Reference AssemblyPath="System.Windows.Forms.dll"/>
<![CDATA[

Imports System.Windows.Forms
Imports ExcelDna.Integration

Public Module RangeTests


<ExcelCommand(MenuName:="RangeTests", MenuText:="Show value in C3
(COM interface)")> _
Sub ShowCell_C3_COM()
Dim app as Object
Dim value as Object

app = ExcelDnaUtil.Application
value = app.ActiveSheet.Cells(3,3).Value

MessageBox.Show("The value in cell C3 is " & value)
End Sub

<ExcelCommand(MenuName:="RangeTests", MenuText:="Show value in C3
(SDK interface)")> _
Sub ShowCell_C3_SDK()
Dim xlref as ExcelReference
Dim value as Object

xlref = new ExcelReference(2,2) ' Zero-based indexing here.
value = xlref.GetValue()

MessageBox.Show("The value in cell C3 is " & value)
End Sub


<ExcelCommand(MenuName:="RangeTests", MenuText:="Show value in Sheet2!
C3 (COM interface)")> _
Sub ShowCell_Sheet2_C3_COM
Dim app as Object
Dim value as Object

app = ExcelDnaUtil.Application
value = app.WorkSheets("Sheet2").Cells(3,3).Value

MessageBox.Show("The value in cell C3 on Sheet2 is " & value)
End Sub

<ExcelCommand(MenuName:="RangeTests", MenuText:="Show value in Sheet2!
C3 (SDK interface)")> _
Sub ShowCell_Sheet2_C3_SDK
Dim xlrefSheet2 as ExcelReference
Dim xlref As ExcelReference
Dim value As Object

' Call xlSheetId to return a reference with the corrent SheetId
xlrefSheet2 = XlCall.Excel(XlCall.xlSheetId, "Sheet2")
xlref = New ExcelReference(2, 2, 2, 2, xlrefSheet2.SheetId)
value = xlref.GetValue()

MessageBox.Show("The value in cell C3 on Sheet2 is " & value)
End Sub


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'A UDF test

<ExcelFunction(IsMacroType:=True)> _
Function SumColor( _
<ExcelArgument(AllowReference := True)> theColor as Object, _
<ExcelArgument(AllowReference := True)> theSumRange as Object)

Dim refColor as ExcelReference = theColor
Dim refSumRange as ExcelReference = theSumRange

SumColor = _SumColor(ReferenceToRange(refColor),
ReferenceToRange(refSumRange))
End Function

Private Function ReferenceToRange(xlref as ExcelReference) as Object
Dim app As Object = ExcelDnaUtil.Application
Dim refAddress As String

refAddress = ReferenceAddress(xlref)

ReferenceToRange = app.Range(ReferenceAddress(xlref))
End Function

Private Function ReferenceAddress(xlref As ExcelReference ) as String
Dim refRightBottom As ExcelReference
Dim addrLeftTop as String
Dim addrRightBottom as String
Dim bangIndex as Integer

addrLeftTop = XlCall.Excel(XlCall.xlfGetCell, 1, xlref)

refRightBottom = New ExcelReference(xlref.RowLast, xlref.RowLast,
xlref.ColumnLast, xlref.ColumnLast,

xlref.SheetId)
addrRightBottom = XlCall.Excel(XlCall.xlfGetCell, 1, refRightBottom)

If (addrLeftTop = addrRightBottom) Then
ReferenceAddress = addrLeftTop
Else
bangIndex = addrRightBottom.IndexOf("!")
ReferenceAddress = addrLeftTop & ":" &
addrRightBottom.Substring(bangIndex + 1)
End If
End Function

Private Function _SumColor(rColor As Object, rSumRange As Object) As
Double
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums cells based on a specified fill color.

''''''''''''''''''''''''''''''''''''''

Dim rCell As Object
Dim iCol As Integer
Dim vResult as Double
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = rCell.Value + vResult
End If
Next rCell
_SumColor = vResult
End Function

End Module

]]>
</DnaLibrary>

[Govert van Drimmelen]

OK, so my SumColor UDF can be made _much_ simpler. Converting from an
ExcelReference to a Range is a 1-liner.

Conversion from an ExcelReference instance to an Excel Range COM
object:

Private Function ReferenceToRange(xlref as ExcelReference) as Object

ReferenceToRange =
ExcelDnaUtil.Application.Range(XlCall.Excel(XlCall.xlfRefText, xlref,
True))
End Function

(Of course you can strongly-type the return value if you have a
reference to the Interop library, which can be embedded with the
new .Net 4 support.)

The whole wrapper for the SumColor function is thus:

<ExcelFunction(IsMacroType:=True)> _
Function SumColor( <ExcelArgument(AllowReference := True)> refColor
As Object, _
<ExcelArgument(AllowReference := True)> refSumRange As Object)

SumColor = _SumColor(ReferenceToRange(refColor),
ReferenceToRange(refSumRange))

End Function

Private Function ReferenceToRange(xlref As ExcelReference) As Object
ReferenceToRange =
ExcelDnaUtil.Application.Range(XlCall.Excel(XlCall.xlfRefText, xlref,
True))

End Function

Private Function _SumColor(rColor As Object, rSumRange As Object) As
Double
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums cells based on a specified fill color.
''''''''''''''''''''''''''''''''''''''

Dim rCell As Object
Dim iCol As Integer
Dim vResult as Double
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = rCell.Value + vResult
End If
Next rCell
_SumColor = vResult
End Function

[Ed Parcell]

Hi Govert,

Background: As part of a larger project, I have written a ExcelDNA UDF
that uses late-binding to ExcelDnaUtil.Application to pick up
formatting information from the calling sheet. Using late-binding
allows me to avoid including a reference to a particular version of
the Excel Interop dll. I have built a .NET Library to do this, while
still allowing Intellisense to work correctly. I'm not sure if this is
a sensible approach - what are your thoughts? I'll be happy to release
this library under an open-source licence in due course.

In testing, I have seen sporadic occurrences of COMException with the
message indicating an HRESULT of 0x800AC472. The best reference I have
found for this is Geoff Darst's post at
http://social.msdn.microsoft.com/forums/en-US/vsto/thread/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/
. It looks like Excel occasionally locks the object model, resulting
in all COM accesses failing during that period. This usually is caused
by some user interaction with Excel, but it looks like to me from
testing like it can happen to an unattended machine also. People have
reported that retrying alleviates the issue - I am currently testing
this, with a fallback of not picking up formatting information as part
of the function call.

Hope this information is useful.

Best regards,
Ed.

[Govert van Drimmelen]

Hi Ed,

Thanks for the report and the interesting link.

I think your late-binding wrapper is a very sensible approach and
would be a cool example.
The .Net 4 support for embedding interop types might be an
alternative, but I have not tried to make it work myself.

The discussion that your link points to indicates what happens when
you call Excel while it is 'busy' (e.g. when the mouse is down). This
would be the same from Excel-Dna commands and menu / ribbon handlers
as from VSTO event handlers and COM add-ins generally.

I'm more intruiged with the implications of accessing the Automation
(COM) object model from UDF functions. You say you get sporadic
exceptions, but it sounds like at least Excel does not crash. The
errors might be worked around.
One of the things I'm trying to understand is whether an Excel-Dna UDF
registered as IsMacroType=true is in a different position to VBA
functions, or functions exposed as Automation Add-Ins. Certainly
Microsoft cautions against calling the Excel object model from within
an .xll UDF, so this is certainly risky behaviour..... But maybe the
situation is a bit more subtle.

If your UDF error is fairly reproducible, maybe you could try to
translate it to a VBA function, and see if you get the same behaviour
and sporadic errors. I'd be happy to help with this. What I really
want to know is whether it is safe and advisable to translate VBA
functions to VB.NET exposed through Excel-Dna, even if the functions
call the object model. We know that VBA functions are all considered
IsMacroType=true.

I'd love to hear about more experiences of people fearlessly flaunting
the rules, and accessing the Excel Automation object model from within
their UDFs.

Regards,
Govert

On Aug 19, 9:55 am, Ed Parcell <edparc...@gmail.com> wrote:
> Hi Govert,
>
> Background: As part of a larger project, I have written a ExcelDNA UDF
> that uses late-binding to ExcelDnaUtil.Application to pick up
> formatting information from the calling sheet. Using late-binding
> allows me to avoid including a reference to a particular version of
> the Excel Interop dll. I have built a .NET Library to do this, while
> still allowing Intellisense to work correctly. I'm not sure if this is
> a sensible approach - what are your thoughts? I'll be happy to release
> this library under an open-source licence in due course.
>
> In testing, I have seen sporadic occurrences of COMException with the
> message indicating an HRESULT of 0x800AC472. The best reference I have

> found for this is Geoff Darst's post athttp://social.msdn.microsoft.com/forums/en-US/vsto/thread/9168f9f2-e5...

> > >    - Hide quoted text -
>
> - Show quoted text -...
>
> read more »

[Ian Murphy]

Hi Govert, thanks for the fast response. Though I have to admit I
still don't see how to do what I want.
You posted an example function above which is a good example of the
problem I'm seeing, which I've changed to a function

function Cell_Sheet2_C3_SDK as string

Dim xlrefSheet2 as ExcelReference
Dim xlref As ExcelReference
Dim value As Object

' Call xlSheetId to return a reference with the
corrent SheetId
xlrefSheet2 = XlCall.Excel(XlCall.xlSheetId, "Sheet2")
xlref = New ExcelReference(2, 2, 2, 2,
xlrefSheet2.SheetId)
value = xlref.GetValue()

return value
End Sub

if you put =cell_sheet2_c3_sdk() in a cell it should return the value
which is in cell c3 of sheet2, however there is a problem.

The call to

xlrefSheet2 = XlCall.Excel(XlCall.xlSheetId, "Sheet2")

doesn't specify which workbook. If you have a dozen workbooks open and
make a call like this to excel it should return the reference to
'sheet2' on the currently active workbook, no? If the workbook where
the formula is used is not the active one it will return an incorrect
reference.

I've dug though the examples and can't work out how to do this
sequence:
- Get the calling cell reference (XlCall.Excel(XlCall.xlfCaller)
should return this, I think)
- From the cell get the worksheet of the calling cell
- From the worksheet get the workbook of the calling cell
- from the workbook find sheet X in of the calling workbook
- from sheet X get the contents of cell x,y

in vba this would be more or less like this:
if cacheddbconn is nothing then
oCell = Application.Caller
osht = ocell.parent
oWb = oSht.Parent
oParamSht = oWb.Sheet("Params")
strDbServer = oParamsheet.cells(10,1)
etc....

I have a collection of functions which pull data from a database, but
the details of the database are on a worksheet within the workbook.
From function I need to be able to read this worksheet to pull the
connection details for the initial connection.

Ian

[Govert van Drimmelen]

Hi Ian,

Thanks for your patience.
Excel's notion of the Current Cell/Sheet/Book vs. the Active Cell/
Sheet/Book can be confusing.

The call to
xlrefSheet2 = XlCall.Excel(XlCall.xlSheetId, "Sheet2")

will refer to Sheet2 in the Current book (the one with the cell that
is calling your UDF) so this is exactly what you are asking for.
You need not explicitly retrieve the caller for your use case.

If you want to get a SheetId for a sheet on another book, use the
string "[OtherBook.xls]SheetX" to refer to that sheet.

Your function that would retrieve the value of cell C3 from Sheet2 on
the Current workbook (not the Active (front) workbook) is thus:

<ExcelFunction(IsMacroType:=True)> _
Function ReadCell_Sheet2_C3()

Dim xlrefSheet2 as ExcelReference
Dim xlref As ExcelReference
Dim value As Object

' Call xlSheetId to return a reference with the corrent SheetId
xlrefSheet2 = XlCall.Excel(XlCall.xlSheetId, "Sheet2")
xlref = New ExcelReference(2, 2, 2, 2, xlrefSheet2.SheetId)
value = xlref.GetValue()

ReadCell_Sheet2_C3 = value
End Function

If you would like to see or use information about the calling cell
(which is not needed in your use case) you could start by reading the
full cell reference. Such a function would be:
<ExcelFunction(IsMacroType:=True)> _
Function ShowCallerInfo()
Dim Caller as ExcelReference
Dim BookAndSheetName as String
Dim RefText as String

Caller = XlCall.Excel(XlCall.xlfCaller)
BookAndSheetName = XlCall.Excel(XlCall.xlSheetNm, Caller)
RefText = XlCall.Excel(XlCall.xlfRefText, Caller)

ShowCallerInfo = "Caller Book and Sheet Name: " & BookAndSheetName &
"; Full Reference: " & RefText
End Function

Please post back if this is still not clear.

Regards,
Govert

[Govert van Drimmelen]

Hi again,

To understand Active and Current concepts better better, you might
like to try the following function in different open books in an Excel
instance.
Tile the windows, and press Ctrl+Alt+F9 to recalculate all. The Active
book changes as you select different books, but the Current book is
stable for every caller.

<ExcelFunction(IsMacroType:=True)> _
Function ShowActiveAndCurrentSheets()
Dim Caller as ExcelReference
Dim CallerRefText as String
Dim ActiveSheet as ExcelReference
Dim ActiveSheetName
Dim CurrentSheet as ExcelReference
Dim CurrentSheetName

Caller = XlCall.Excel(XlCall.xlfCaller)
CallerRefText = XlCall.Excel(XlCall.xlfRefText, Caller)

ActiveSheet = XlCall.Excel(XlCall.xlSheetId)
ActiveSheetName = XlCall.Excel(XlCall.xlSheetNm, ActiveSheet)

CurrentSheetName = XlCall.Excel(XlCall.xlSheetNm, Caller)
CurrentSheet = XlCall.Excel(XlCall.xlSheetId, CurrentSheetName)
' CurrentSheet.SheetId will be equal to Caller.SheetId at this point

ShowActiveAndCurrentSheets = "ActiveSheet: " & ActiveSheetName & _
" (" & ActiveSheet.SheetId & "), Current: " & CurrentSheetName & _
" (" & CurrentSheet.SheetId & "), Caller: " & CallerRefText
End Function


Cheers,
Govert

[Ian Murphy]

Aha, in vba and COM, unless you specify the worksheet it assumes
activeworksheet, which can cause all sorts of problems.
I was just assuming it would work the same way.

I've seen loads of code in both vba and external using COM which
breaks if you do something as simple as pressing alt-tab (change
workbook) while its executing. Others which fail if excel loses focus
during the process.

In short, its always been better to explicitly specify, starting from
some point like a cell, that the parent of this cell is worksheetx and
the parent of this ws is workbookY. On workbookY there should exist a
sheet called "data" where I will place my value in cell 2,2 ...
checking everything along the way.


Ian