Interop: Cannot call get_Range, and cannot use two-dimensional object array returned by Range().Value2?

Question

Trying to replace Cells(RowIndex,ColumnIndex).Value() calls by row-wise references for performance, I permanently fail at referencing the result.

Starting with Excel Interop - Efficiency and performance, which contains the tip to use get_range, i.e.

//get values
object[,] objectArray = shtName.get_Range("A1:Z100").Value2;
iFace = Convert.ToInt32(objectArray[1,1]);

//set values
object[,] objectArray = new object[3,1] {{"A"}{"B"}{"C"}};
rngName.Value2 = objectArray;

I figured I cannot use get_Range since that method is not callable for me, says VB at run-time (not a visible member).

Now I came up with stuff like:

Dim Values As Object(,)
Values = Sheet.Range(Sheet.Cells(RowIndex, 1), Sheet.Cells(2, 17)).Value2
For Index As Integer = 0 To 16
  MsgBox(Values(0, Index))
Next

However, referencing Values with two dimension indices always returns an "index was outside the bounds of the array" exception. Inspecting the array with the debugger shows a nice 2-dimensional array which should has 17 elements on the second dimension, so Value (0,0) should indeed be a valid reference -- but it isn't:

The debugger lets me inspect Value, I can also drill down to Value(0,0) and see the correct value, but re-evaluating just that element, i.e. inspecting "Value (0,0)" returns above message.

How can I reference what my Value2 call returns?

Doug Glancy · Accepted Answer · 2011-06-02T13:39:48.810

1

Perhaps something like this? Note that if you want the array to have the column as the first element you'd use change the line below as noted:

Imports Microsoft.Office.Interop
    Module Module1
        Sub main()
            Dim appExcel As Excel.Application
            Dim wb As Excel.Workbook
            Dim ws As Excel.Worksheet
            Dim values As Object

            appExcel = GetObject(, "Excel.Application")
            wb = appExcel.Workbooks(1)
            ws = wb.Worksheets(1)
            With ws
                values = .Range(.Cells(1, 1), .Cells(2, 5)).Value2
                'if column is first element use appExcel.Worksheetfunction.Transpose(.Range(.Cells(1, 1), .Cells(2, 5)).Value2)
                For i As Int32 = LBound(values, 1) To UBound(values, 1)
                    For j As Int32 = LBound(values, 2) To UBound(values, 2)
                        System.Windows.Forms.MessageBox.Show(values(i, j))
                    Next j
                Next
            End With
        End Sub
    End Module

edited Jun 02 '11 at 13:39

answered Jun 02 '11 at 04:11

Doug Glancy

27,214
6
67
115

The problem is that UBound and LBound return 1 for both dimensions of the array even though the first dimension contains one element with index 0 and the second 17 elements with index 0-16, as shown by the debugger. I totally don´t get it. – TheBlastOne Jun 02 '11 at 08:22
I´ll try/re-verify that at my desk asap, though. – TheBlastOne Jun 02 '11 at 08:25
@TheBlastOne, I understand what you are saying/seeing with the conflicting indices. However, the code above worked for me and returned all the values with no runtime errors. – Doug Glancy Jun 02 '11 at 13:38
Okay, that works. And the same principle works for my precise case. The problem was that a) the interactive debugger does not handle the runtime type information correctly, and b) I somehow failed to use the correct index. In short: You're right. Thanks. – TheBlastOne Jun 03 '11 at 08:44

score 0 · Answer 2 · answered Jun 01 '11 at 10:25

0

Try

Dim Values As Variant
Dim shtName as Worksheet
Set shtName = <your sheet>
Values = shtName.Range("A1:Z100")

answered Jun 01 '11 at 10:25

chris neilsen

52,446
10
84
123

1. I have integer row and column indices, not A1 style references. How do I convert them easily to A1 notation? And: 2. I cannot use Variant, the IDE makes Object out of all Variant references at edit-time -- I forgot to mention I am using VB 2010 Express. 3. DirectCast(Values.Value,Object(,))(0,0) generates exactly the same problems as described in my question. – TheBlastOne Jun 01 '11 at 11:14
May be something to do with 0 based vs 1 based arrays. Have a look at this post [Link](http://www.add-in-express.com/forum/read.php?FID=5&TID=6995) As to A1 notation, best to stick to the `Range(Sheet.Cells(RowIndex, 1), Sheet.Cells(2, 17))` method – chris neilsen Jun 01 '11 at 11:28
A1 notation: Agree. However, Value(0,0) is never valid, and Value (1,1) is not, too. Even if the debugger shows those entries. (It shows the array is 0-based.) That's exactly what makes me wonder, and what I am searching a solution for. – TheBlastOne Jun 01 '11 at 12:10

Interop: Cannot call get_Range, and cannot use two-dimensional object array returned by Range().Value2?

2 Answers2