Multidimensional Array Assignment RRS feed

  • Question

  • Question on assigning arrays.  I have a 2 dimension variant vArr2D (0 to 0, 0 to 500).  This is returned from a recordset using GetRows.  I want to assign the second dimension to another variant.

    Dim vArr2D as Variant Dim vArr1D as Variant Dim rst as Recordset

    vArr2D = rst.GetRows ' Returns this (0 to 0, 0 to 500) vArr1D = vArr2D(0)() ' I want vArr1D(0 to 500) . vArr2D(0)() doesn't work.

    How do you assign 2nd dimension of vArr2D to vArr1D?
    Tuesday, February 10, 2015 5:05 PM

All replies

  • There are other ways but for your purposes simply copy to a 1D array, erg

    ReDim arr1d(LBound(arr2d) To UBound(arr2d, 2))
    For i = LBound(arr1d) To UBound(arr1d)
        arr1d(i) = arr2d(0, i)

    Tuesday, February 10, 2015 6:56 PM
  • Thanks,

    that is what I am doing now since I was in a hurry when I did it.  I was looking to optimize some code.  The GetRows returns the rows in the 2nd dimension (0 to 0, 0 to 500).  The Combobox is looking for rows in the first dimension either (0 to 500) or (0 to 500, 0 to 0) when you assign it as an array.  I think assigning one dimension of an array is not possible in VBA and a loop is the only solution.

    • Edited by mogulman52 Tuesday, February 10, 2015 7:19 PM
    Tuesday, February 10, 2015 7:14 PM
  • As I said there are other way. Eg you could use worksheets functions Index (to get a row or column as a slice) or Transpose (still a 2D but would work for your needs), and you could assign in one go to the combo.list. However accessing the worksheetfunction object is slow, slower than the loop. There may be ways of doing it in memory but complicated and overkill. Keep it simple and stick with the loop!

    Or, instead of making a new array loop the original array and add to the combo with AddItem

    Wednesday, February 11, 2015 11:30 AM
  • I tried both making a new array and using addItem.  They are almost the same speed.  Array assignment to the combobox is immediate.  This is a Word app so no Excel.  This is a fairly complex Word app that is used to edit tables and produce documents.  My evaluation of the performance came about due to a web app I did that more or less duplicates the Word app.  There was interest in using other platforms like Macs, Chromebooks, iPads etc.  I implemented it using HTML5, CSS3, Javascript, Ajax and PHP.  I even created a capability to create Word/PDF docs on the Web server by writing a special addin for Word that is run by PHP.   I thought it would be much slower due to all the Javascript, Ajax and Jason encoding but surprisingly it was pretty responsive.  So now I am looking at the Word version to see how I can speed it up.
    Wednesday, February 11, 2015 6:13 PM
  • A 500 element array is small by modern standards. You'd need a very accurate timer to measure the difference of how many nano seconds it takes to populate the combo with additem or in one go with an array copied from the 2D array. Both ways should be visually instantaneous and difficult to measure the difference. OTH if you really do think it seems slow something is wrong.

    All the other things you mentioned, which I don't pretend to follow, may take much longer to get through bottlenecks than this trivial array.

    Wednesday, February 11, 2015 6:58 PM