none
Excel's array dimensions mismatch in .NET RRS feed

  • Question

  • I have posted this question to two MSDN forums. However, I didn't get positive answer. Here's my question.

    As you know, in Excel you can transfer any range into array by simple statement:

    Dim arr As Variant
    arr = Range("A1:D10")
    


    This array always represents 2-dimensional array (even if you transfer only one column) and the lower bound is always 1, no matter what Option Base is set, and the first dimension always has index "1". This means that top-left element is arr(1, 1).

    So, I wrote simple method for getting this top-left value.

     

    Sub ArrayDimensionsMismatch()
    
            Dim xlApp = New Excel.Application With {.Visible = False}
            Dim book = xlApp.Workbooks.Open("D:\Test.xlsx")
            Dim sheet = DirectCast(book.Worksheets(1), Excel.Worksheet)
    
            ' Getting type.
            ' The type is "System.Object[,]", what means it's multidimensional array.
            Console.WriteLine("Array type: {0}", sheet.Range("A1:D10").Value.GetType)
    
            ' Getting array.
            Dim arr = DirectCast(sheet.Range("A1:D10").Value, Object(,))
    
            ' Getting lower bound of first dimension (.NET uses "0" by default).
            ' lowerBound = 1.
            Dim lowerBound = arr.GetLowerBound(0)
    
            ' As GetLowerBound accepted "0", then we use "0" for first dimension.
            ' ERROR: "Index was outside the bounds of the array".
            Dim topLeft1 = arr.GetValue(lowerBound, 0)
    
            ' Using "1" for first dimension.
            ' IT WORKS!
            Dim topLeft2 = arr.GetValue(lowerBound, 1)
    
            Console.WriteLine("Lower bound: {0}", lowerBound)
            Console.WriteLine("Top-left value: {0}", topLeft2)
    
            Console.ReadKey()
    
            book.Close(SaveChanges:=False)
            xlApp.Quit()
            xlApp = Nothing
    
            GC.Collect()
            GC.WaitForFullGCComplete()
    
    End Sub
    

    Here's my question: why GetLowerBound accepts "0", but GetValue rejects it? I mean if it's detected that the first dimension is "1", then GetLowerBound should accept "1".
    There is no knowledge that is not power.
    • Changed type JohnyL Tuesday, November 29, 2011 7:40 AM
    • Changed type JohnyL Tuesday, November 29, 2011 7:40 AM
    Tuesday, November 29, 2011 4:39 AM

Answers

  • I got to know, finally, that GetLowerBound(0) returns the lower bound of first dimension whether it's 0, 1 or something else!
    There is no knowledge that is not power.
    • Marked as answer by JohnyL Wednesday, November 30, 2011 3:56 AM
    Tuesday, November 29, 2011 8:26 AM

All replies

  • Hello,

    • Array.GetLoweBound - Gets the lower bound of the specified dimension in the Array.
    • Array.GetValue (int32, int32) - Gets the value at the specified position in the two-dimensional Array.

    Office arrays are 1-based. This is the reason of what you see. You can check that array's information while debugging your code.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    Tuesday, November 29, 2011 6:54 AM
  • @Andrei Smolin

    Can you answer my question? I know all you wrote. In my post I write that Office arrays are always 1-based and first dimension is 1 (I even made it bold). Please, answer my original question!


    There is no knowledge that is not power.
    Tuesday, November 29, 2011 7:02 AM
  • Hello,

    Zero passed to GetLowerBound is the index number of the array's first dimension.

    Calling array.GetValue(1, 0) is equivalent to getting array(1,0) in VB. Since arrays are 1-based, pasing zero to GetValue causes the exception. Calling GetValue(1,1) is equivalent to getting array(1,1) - this retruns the top-left element of the 2D array. And no exception.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader
    Tuesday, November 29, 2011 7:53 AM
  • I got to know, finally, that GetLowerBound(0) returns the lower bound of first dimension whether it's 0, 1 or something else!
    There is no knowledge that is not power.
    • Marked as answer by JohnyL Wednesday, November 30, 2011 3:56 AM
    Tuesday, November 29, 2011 8:26 AM