How To Load SINGLE Range (Cell) Value as Object - In 2D Object Array - InvalidCastException

[Cj]

Setup: ---- VB.net - Visual Studio 2010 - Excel DNA 0.32 - Excel Version 2010 - Option Strict ON


The following WORKS FINE all day long for loading MULTIPLE range values IE: ("F2:F5") or more into a 2D Object Array... No problem... as in the following

            Dim myRangeTwo As Range = ws.Range("F2:F5")  ' MULTIPLE CELL RANGE
            Dim arr2(,) As Object = CType(myRangeTwo.Value(XlRangeValueDataType.xlRangeValueDefault), Object(,))

The ws.range("F2:F5") values are stuffed into the myRangeTwo range variable as 2D Objects and then those are easily stuffed into the 2D object array...


But this does not work for a SINGLE cell range...

            Dim myRangeTwo As Range = ws.Range("F2:F2")    ' SINGLE CELL RANGE F2 ONLY
            Dim arr2(,) As Object = CType(myRangeTwo.Value(XlRangeValueDataType.xlRangeValueDefault), Object(,))

This triggers an Invalid Cast Exception error on trying to load into the arr2(,).. because the ws.range("F2:F2") is stuffed into the myRangeTwo variable as a "string" not as an object
therefore is not possible to stuff it into an Object Array and so correctly causes the Invalid Cast Error...

Govert or anyone... how do you handle this seemingly ridiculously simple problem ??

thanks... Cj

[Govert van Drimmelen]

It seems from your example that the Range object's Value property returns an Object(,) array if the range has more than one cell, but a single object of the relevant type if the range has a single cell.

The problem is not that the string returned by the single-celled range's Value property cannot be stuffed into an Object(,) array, but that the CType(...) type conversion does not do any stuffing - it won't make a new array if the value you pass in is not an array already. In the multi-cell case, the array was already returned as the object you get from the Value property, and the CType(...) makes the type conversion to allow the local variable assignment.

You could check the range size, and write your own extraction function like this (I've not tried to compile it, but you'll get the idea):

    Function GetRangeValueAsArray(theRange As Range) As Object(,)

        If theRange.Cells.Count = 1 Then

            Dim theResult(0, 0) As Object

            theResult(0, 0) = theRange.Value

            Return theResult

        End If

        ' This is the normal case

        Return CType(theRange.Value, Object(,))

    End Function

You could also use an array initializer statement to initialize the 2D array like this:

    Dim theResult As Object(,) = {{ theRange.Value }}

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Cj [webf...@gmail.com]
Sent: 18 February 2015 10:17 PM
To: exce...@googlegroups.com
Subject: [ExcelDna] How To Load SINGLE Range (Cell) Value as Object - In 2D Object Array - InvalidCastException

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Cj]

Most Excellent Govert... You Da Man.. Thanks... Cj...