locked
Array dimensions mismatch between Excel and .NET [VB 2010] RRS feed

  • Question

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

     

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

     

    And as you know, this array's lower bound is always 1, no matter what Option Base is set. This means that top-left element is arr(1, 1) and arrays is always 2-dimensional.

    So, I wrote simple test console application which shows weird behavior of dimension index.

    Imports Excel = Microsoft.Office.Interop.Excel
    
    Sub ArrayMismatch()
    
            Dim xlApp = New Excel.Application With {.Visible = False}
            Dim xlBook = xlApp.Workbooks.Open("D:\Test.xlsx")
            Dim xlSheet = DirectCast(xlBook.Worksheets(1), Excel.Worksheet)
    
            Dim arr = DirectCast(xlSheet.Range("A1:D10").Value, Object(,))
    
            Console.WriteLine("Dimension 0:")
            Console.WriteLine("Lower bound: " + arr.GetLowerBound(0).ToString)
            Console.WriteLine("Upper bound: " + arr.GetUpperBound(0).ToString)
    
            Console.WriteLine()
    
            Console.WriteLine("Dimension 1:")
            Console.WriteLine("Lower bound: " + arr.GetLowerBound(1).ToString)
            Console.WriteLine("Upper bound: " + arr.GetUpperBound(1).ToString)
    
            ' By .NET logic, it should be written as arr(0, 0).
            ' But .NET applies Excel's logic: arr(1, 1).
            Console.WriteLine("Top-left value: " + CStr(arr(1, 1)))
    
            Console.WriteLine()
    
            xlBook.Close(SaveChanges:=False)
            xlApp.Quit()
            xlApp = Nothing
    
            GC.Collect()
            GC.WaitForFullGCComplete()
    
    End Sub
    Here's a trap: To get upper bound of array in .NET, you must use GetUpperBound(0), while to get first value in first column and first row, you must write arr(1, 1). So, here's question: why in definition of boundaries .NET uses first dimension as 0 and at the same time to access first element it uses Excel's logic? This is very confusing!
    There is no knowledge that is not power.
    • Edited by JohnyL Monday, November 21, 2011 11:43 AM
    Monday, November 21, 2011 11:41 AM

Answers

  • no disrespect... but, become one of the executive in MS and propose that.
    • Marked as answer by JohnyL Wednesday, November 30, 2011 3:56 AM
    Tuesday, November 29, 2011 4:35 PM

All replies

  • Hi Motaro,

    Welcome to the MSDN Forum.

    This is an intresting question. And I am researching on it, when I get updates, I will follow this thread.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, November 23, 2011 4:15 PM
    Moderator
  • Thanks for feedback!


    There is no knowledge that is not power.
    Thursday, November 24, 2011 4:06 AM
  • There is no  requirement for an array to have it's lowerbound at zero. VB will enforce this to maintain the compatibility with the other classes of the .NET, however if you bypass the ability for the desing time to know the bounds of the array , there is no problem for the runtime to handle an array with any lowerbound value.

    In this case, at desing time, the value of  "xlSheet.Range("A1:D10").Value"   is : System.Reflection.Missing

    Therefore vb cannot enforce the rule setting the lowerbound to be 0

    ====

    Here an example that reproduce in an "easier to understand code" what is happening.

    This example creates a type  "Integer(5,5)"  with the lowerbounds set to 1

     

     

            Dim AnArray = Array.CreateInstance(GetType(Integer), New Integer() {5, 5}, New Integer() {1, 1})
            For x = 1 To 5
                For y = 1 To 5
                    AnArray.SetValue(x, x, y)
                Next
            Next
            Dim Arr(,) As Integer = CType(AnArray, Integer(,))
    

     


    • Edited by Crazypennie Thursday, November 24, 2011 6:54 AM
    • Proposed as answer by Mike FengModerator Thursday, November 24, 2011 7:56 AM
    • Unproposed as answer by JohnyL Thursday, November 24, 2011 9:35 AM
    Thursday, November 24, 2011 6:19 AM
  • I guess I need more explaining. See comments.

       Sub ArrayMismatch2()
    
            Dim xlApp = New Excel.Application With {.Visible = False}
            Dim book = xlApp.Workbooks.Open("D:\Test.xlsx")
            Dim sheet = DirectCast(book.Worksheets(1), Excel.Worksheet)
    
            Dim arr = DirectCast(sheet.Range("A1:D10").Value, Object(,))
    
            ' GetLowerBound(dimension) function requires "dimension" argument,
            ' where "dimension" is "zero-based dimension of the System.Array...".
            ' Thus, the first dimension of "arr" array is 0.
            ' lowerBoundNET = 1.
            Dim lowerBoundNET = CInt(arr.GetLowerBound(0))
    
            ' So, .NET uses "0" as first the dimension.
            ' Getting top-left value...
            ' OOPS! Error: "Index was outside the bounds of the array".
            Dim valueNET1 = CStr(arr(0, 0))
    
            ' Oh, I forgot! The "lowerBoundNET" variable told me that lower bound of array was 1.
            ' May be this?
            ' OOPS! Error: "Index was outside the bounds of the array".
            Dim valueNET2 = CStr(arr(1, 0))
    
            ' What if to use Excel's way?
            ' May be this?
            ' YES!
            Dim valueExcel = CStr(arr(1, 1))
    
            ' Conclusion. For the first dimension:
            '    - .NET uses "0" when retrieving lower/upper bounds
            '    - .NET uses "1" for retrieving values
    
            book.Close(SaveChanges:=False)
            xlApp.Quit()
            xlApp = Nothing
    
            GC.Collect()
            GC.WaitForFullGCComplete()
    
    End Sub
    



    There is no knowledge that is not power.
    Thursday, November 24, 2011 7:37 AM
  • My previous post is explaining exactly why this is happening

    How can VB can possibly generate the code to replace the bounds while it dont even know that type will be an array ???

    The type of what is coming from excel is System.reflection.missing.  Only at runtime, the real type will be know

    • Edited by Crazypennie Thursday, November 24, 2011 7:44 AM
    Thursday, November 24, 2011 7:41 AM
  • Hi Motaro,

    I agree with Crazypennie.

            ' lowerBoundNET = 1 means the first dimension of arr start with 1 
            Dim lowerBoundNET = CInt(arr.GetLowerBound(0))
    
            ' lowerBoundNET2 = 1 means the second dimension of arr start with 1 
            Dim lowerBoundNET2 = CInt(arr.GetLowerBound(1))
    

    >>where "dimension" is "zero-based dimension of the System.Array...".

    Yes, this means if you want to get first dimension, you should pass the 0 instead of 1 to the GetLowerBound method. The word "zero-based" is a modifier of dimension, it is not for LowerBound. As you said, the lowerBoundNET is the lowerBound, and you get 1, this means its lowerbound is 1 rather than 0.

    I hope this is helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, November 24, 2011 8:19 AM
    Moderator
  • I don't talk about lower/upper bounds - I talk about dimensions! OK. I hope next example will be more illustrative.

     

    Sub ArrayMismatch()
    
            Dim xlApp = New Excel.Application With {.Visible = False}
            Dim book = xlApp.Workbooks.Open("D:\Test.xlsx")
            Dim sheet = DirectCast(book.Worksheets(1), Excel.Worksheet)
    
            ' Being more explicit.
            Dim arr As Object(,) = DirectCast(sheet.Range("A1:D10").Value, Object(,))
    
            ' In our case, array is 2-dimension.
            ' So, by default in .NET the first dimension is 0.
            Dim firstDimension = 0
    
            ' OK. Getting lower and upper bounds of array.
            ' Applying .NET's way.
            Dim lowerBoundNET = arr.GetLowerBound(firstDimension)
            Dim upperBoundNET = arr.GetUpperBound(firstDimension)
    
            ' Number of dimensions.
            ' Excel's array has always 2 dimensions when transferring ranges to array,
            ' even if range consists of one column.
            Dim numberOfDimensions = arr.Rank
    
            Console.WriteLine("Number of dimensions: {0}", numberOfDimensions)
            Console.WriteLine("Lower bound of first dimension: {0}", lowerBoundNET)
            Console.WriteLine("Upper bound of first dimension: {0}", upperBoundNET)
    
            ' So far, so good.
            ' Let's fetch top-left value.
            Dim topLeft = arr(lowerBoundNET, firstDimension)
    
            ' ERROR: Index was outside the bounds of the array.
            ' Here's question: HOW TO DEFINE "firstDimension" VARIABLE?
            Console.WriteLine("Top left value: {0}", topLeft)
    
            Console.ReadKey()
    
            book.Close(SaveChanges:=False)
            xlApp.Quit()
            xlApp = Nothing
    
            GC.Collect()
            GC.WaitForFullGCComplete()
    
    End Sub
    


     

     


    There is no knowledge that is not power.
    • Edited by JohnyL Thursday, November 24, 2011 9:36 AM
    Thursday, November 24, 2011 9:34 AM
  • Hi Motaro,

    When we define a array like this Dim arr(,) as Object

    Yes, the dimension is 2, and according to the MSDN article, when we access the first dimension, we should pass 0 to the target function. Such as the method GetLowerBound or GetUpperBound.

    But, when we need to retrieve the specific item of a array, we need to provided the index of each demension, rather than demension.

    So what do you mean by this "Dim topLeft = arr(lowerBoundNET, firstDimension)"? You passed a dimension value to an index parameter.

    Maybe I still misunderstand you, but please believe me, I really want to try my best to provided my help.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, November 24, 2011 11:18 AM
    Moderator
  • I think, a picture is better than thousands of words.
    There is no knowledge that is not power.
    Friday, November 25, 2011 4:38 AM
  • Hi Motaro,

    I have saw that picture. Thank you for making this for us.

    Here, I want to make an assumption: arr(1,1) = arr.getValue(1,1). Do you agree with me?

    If so, please take a look at this article: http://msdn.microsoft.com/en-us/library/147tce02.aspx

    The first and second parameters is an index, it is not a dimension number.

    In this method GetLowerBound : http://msdn.microsoft.com/en-us/library/system.array.getlowerbound(v=VS.100).aspx

    It asked a dimension number.

    For a 2-dimension array in your example, if we want to get the third row, the second column cell value, how we write the code?

    I think it is arr(3,2) or arr.GetValue(3,2), do you agree? I know you mean the top-left corner cell, but the top-left cell is one of all the array cells, right? So we also need to use two index numbers to specify it, right?

    After all, my point is the index number is different from the dimension number. And the method GetLowerBound need dimension number, but when we want to get an item of an array, we need to provided the index numbers.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, November 25, 2011 7:13 AM
    Moderator
  • I hope this presentation will explain more clearly.
    There is no knowledge that is not power.
    Friday, November 25, 2011 1:34 PM
  • Dim topLeft = arr(lowerBoundNET, firstDimension)

    Accessing the 'arr' elements starts at 1 (according to excel com objects)

    so if any of the parameters (lowerBoundNET, firstDimension) is 0 value... it will error out.

    in Immediate Window try the following code

    ?arr(1, 1)

    that should give you the first upper left value of the range 'arr'

    Friday, November 25, 2011 5:36 PM
  • "so if any of the parameters (lowerBoundNET, firstDimension) is 0 value... it will error out."

    Then why "Dim lowerBoundNET = arr.GetLowerBound(0)" doesn't give me error when I use "0" for dimension?


    There is no knowledge that is not power.
    Monday, November 28, 2011 3:45 AM
  • .GetLowerBound is a method/function. it has nothing to do with calling the array element itself.

    this method (.GetLowerBound) takes a parameter, which is by design starts with 0 (zero). However, com objects programmability is by 1.


    Edit:

    "However, com objects programmability is by 1."

    I ment to say array element accessibility is by 1 in com object.

    Accidently marked 'Proposed As Answer'


    • Proposed as answer by RoninB Monday, November 28, 2011 4:40 AM
    • Unproposed as answer by RoninB Monday, November 28, 2011 4:41 AM
    • Edited by RoninB Monday, November 28, 2011 4:51 AM
    • Proposed as answer by Lie You Tuesday, November 29, 2011 6:25 AM
    • Unproposed as answer by JohnyL Tuesday, November 29, 2011 7:40 AM
    Monday, November 28, 2011 4:28 AM
  • "...by design starts with 0 (zero)...".

    GetLowerBound requires "dimension". It doesn't state that first dimension must be "0". I know that first dimension Excel's array is "1". If I pass "1" to GetLowerBound, then I would get lower bound if second dimension rather than first one.


    There is no knowledge that is not power.
    Monday, November 28, 2011 4:40 AM
  • If I pass "1" to GetLowerBound, then I would get lower bound if second dimension rather than first one.

     

    the above also works with .Net Framework, VB, C#, etc.

    We are talking about two different architecture here... one is Excel and the other is .Net

    However, some are the same methodology such as the .GetLowerBound or LBound in VBA.

    And still some are different. In sense, figure out what works and just keep it in mind for future reference.


    Edited... clarify previous post:

    the .GetLowerBound is a function/method... which is the same for most part (VBA: LBound, UBound, .Net Framework, VB.Net, C#.Net, Com Object). This is why it is the same accessing dimension that you mention... just as is 'Dimension'

    accessing array element (Not Dimension, but  elements) is more of direct access to the values.

    There is a difference in what we are talking about on this... one is 'Dimension', the other is elemental value

     

    Accessing Array element:

    Com Object, the value starts with 1

    VBA (Excel, Word, PowerPoint) array starts with 0

    .Net Framework, VB.Net, C#.Net, etc... starts with 0




    • Edited by RoninB Monday, November 28, 2011 5:06 AM
    Monday, November 28, 2011 4:49 AM
  • In VBA I can create any lower bound and any start dimension. Here's Excel's VBA code:

     

    Sub ArrayTest()
    
        Dim iRow As Integer, iCol As Integer
        Dim arr(3 To 5, 1 To 2) As String
        
        arr(3, 1) = "3,1"
        arr(4, 1) = "4,1"
        arr(5, 1) = "5,1"
        
        arr(3, 2) = "3,2"
        arr(4, 2) = "4,2"
        arr(5, 2) = "5,2"
        
        For iRow = 3 To 5
            For iCol = 1 To 2
                Cells(iRow - 2, iCol) = arr(iRow, iCol)
            Next
        Next
        
    End Sub
    

     


    As you can see from this code, the lower bound of array is 3 and the first dimensions is 1. When in VBA I transfer some range into Variant variable, it holds array with following characteristics:

    - lower bound is 1;

    - first dimension is 1.

    Thus accessing top-left element would be arr(1, 1), i.e. "first value in first dimension".

    .NET's notion arr(0, 0) will be the same - "first value in first dimension". However, what .NET applies to "first dimension" differs from Excel's VBA. In this case .NET says that first dimension must be 0, but VBA says it must be 1. Even if I would apply VBA's logic to GetLowerBound, I would get incorrect result, i.e. GetLowerBound should accept "1" for first dimension rather than 0. It means that .NET confuses things when it accepts 0 as first dimension. This makes developer think that accessing array's elements is done by .NET's rule. And although I can programatically get lower and upper bounds of array, I can't get the index of first dimension.

     


    There is no knowledge that is not power.
    Monday, November 28, 2011 6:30 AM
  • .NET's notion arr(0, 0) will be the same - "first value in first dimension". However, what .NET applies to "first dimension" differs from Excel's VBA. In this case .NET says that first dimension must be 0, but VBA says it must be 1. Even if I would apply VBA's logic to GetLowerBound, I would get incorrect result, i.e. GetLowerBound should accept "1" for first dimension rather than 0. It means that .NET confuses things when it accepts 0 as first dimension. This makes developer think that accessing array's elements is done by .NET's rule. And although I can programatically get lower and upper bounds of array, I can't get the index of first dimension.


    Like i said... for the most part. These are again, two different architecture. Some similarities and differences

    would the following give you the same output?

            Dim tArray(5, 2) As String
     
            tArray(3, 1) = "3,1"
            tArray(4, 1) = "4,1"
            tArray(5, 1) = "5,1"
     
            tArray(3, 2) = "3,2"
            tArray(4, 2) = "4,2"
            tArray(5, 2) = "5,2"
     
            For iRow = 3 To 5
                For iCol = 1 To 2
                    Cells(iRow - 2, iCol) = tArray(iRow, iCol)
                    'As you can see with the above code... you would still have to offset the cell, 'Cells(iRow-2, iCol)', with the use of subtraction. since 'Cells()' in general, is in actuality an array. So if you want to position the value in a different cell, then remove the '-2'...
                    'so again with Com Objects, just keep this in mind... as you did with the 'Cells()' array
                Next
            Next

     Also with your statement:

    .NET's notion arr(0, 0) will be the same - "first value in first dimension". However, what .NET applies to "first dimension" differs from Excel's VBA. In this case .NET says that first dimension must be 0, but VBA says it must be 1.

    not necessarily. You can declare a variable like:

            Dim tValue(0, 0) As String
            tValue(0, 0) = "0,0" 'still this elemental number position is 0's



     

    • Edited by RoninB Monday, November 28, 2011 4:25 PM
    Monday, November 28, 2011 4:04 PM
  • Dim tArray(5, 2) As String

    Everything depends on Option Base directive. If you have Option Base 0, then you have 6 elements in first dimension. If you have Option Base 1, then you have 5 elements.

    Option Base 0
    
    Sub ArrayOptionBase0()
        Dim arr(0, 0) As Integer
        arr(0, 0) = 1
        MsgBox arr(0, 0)
    End Sub
    

    Option Base 1
    
    Sub ArrayOptionBase1()
        Dim arr(1, 1) As Integer
        arr(1, 1) = 1
        MsgBox arr(1, 1)
    End Sub
    


    But the thing is that transferred array is ALWAYS one-based with first dimension as "1". To make all things unified, then .NET should add 1 internally to my arr(0, 0) so that I wouldn't have to think about platform differences.

     


    There is no knowledge that is not power.
    Tuesday, November 29, 2011 4:03 AM
  • no disrespect... but, become one of the executive in MS and propose that.
    • Marked as answer by JohnyL Wednesday, November 30, 2011 3:56 AM
    Tuesday, November 29, 2011 4:35 PM