Re: [ExcelDna] Function to return valid ExcelReference.GetValue with multiple areas

[Patrick O'Beirne]

A1:A3 is one area.
As I am just about to leave, I have not read the code you gave, but this
may be of help:

A single XlCall.xlfReftext is limited to 255 characters, so the
following concatenates the addresses from the individual areas of the
reference:

Private Function ReferenceToRange(ByVal xlRef As ExcelReference) As Object
Dim cntRef As Long, strText As String, strAddress As String
strAddress = XlCall.Excel(XlCall.xlfReftext,
xlRef.InnerReferences(0), True)
For cntRef = 1 To xlRef.InnerReferences.Count - 1
strText = XlCall.Excel(XlCall.xlfReftext,
xlRef.InnerReferences(cntRef), True)
strAddress = strAddress & "," & Mid(strText,
strText.LastIndexOf("!") + 2)
' +2 because IndexOf starts at 0
Next
ReferenceToRange = ExcelDnaUtil.Application.Range(strAddress)
End Function

[Govert van Drimmelen]

Hi,

I think the multi-areas ranges are problematic. The ExcelReference you
build seems fine, but I think the xlCoerce function called by Excel-
DNA to convert the reference to a value is failing.

I suggest you extract and combine the values for the different areas
separately, as you would do with a Range COM object.

-Govert

On Feb 15, 2:50 am, csm <cmillsh...@gmail.com> wrote:
> Thanks, but that isn't the issue here.  I'm trying to convert a range with
> two areas to a valid reference returned by my function and accepted by
> Excel.