Fill a two-dimensional array, split code on multiple lines

Question

I have to fill a two-dimensional array with 8 x 5 = 40 values.

Below code works.

Sub ReadLabelPositions()
    Dim Arr_labelpositions() As Variant   ' Array 8 rows + 5 columns
                                          ' name label, top position, left position, top position2, left position2
    Dim Int_Counter1, Int_Counter2 As Integer
      
    ' fill array
    Arr_labelpositions() = [{"lbl_N",114, 222, 104, 212; "lbl_NO", 144, 144, 134, 154; "lbl_O", 210, 252, 210, 256; "lbl_ZO", 276, 222, 276, 232 ; "lbl_Z",300, 144, 310, 144; "lbl_ZW", 276, 54, 276, 44; "lbl_W", 210, 36, 210, 26; "lbl_NW", 144, 54, 144, 44 }]
    'loop through array
    For Int_Counter1 = 1 To UBound(Arr_labelpositions, 1)
        For Int_Counter2 = 1 To UBound(Arr_labelpositions, 2)
            Debug.Print Arr_labelpositions(Int_Counter1, Int_Counter2)
        Next Int_Counter2
    Next Int_Counter1
End Sub

I would like to split the line where I assign the values to the array, because the line is too long.

Something like this:

Arr_labelpositions() = [{"lbl_N",114, 222, 104, 212; _ <br>
                        "lbl_NO", 144, 144, 134, 154; _ <br>
                        "lbl_O", 210, 252, 210, 256; _  <br> etc...

Does this work for you? https://stackoverflow.com/questions/44976761/how-to-split-a-constant-2d-array-initialization-on-several-lines — Super Symmetry, Aug 01 '20 at 09:40

freeflow · Accepted Answer · 2020-08-01T11:24:05.227

1

VBA does not support creating arrays of more than one dimension from static values. To achieve your goal I would recommend using a combination of a Collection and Arrays. Each Item of the Collection would contain an array.

Dim myCOlection as Collection
Set myCollection=New COllection
       
With myCollection

    .add Array("lbl_N",114, 222, 104, 212)
    .add Array("lbl_NO", 144, 144, 134, 154)
   ' etc etc.

End with

you can now refer to items in each array using the syntax

 ThisValue = myCollection(x)(y)

where x is the item in myCollection (in reality myCollection.Item(x)) and Y is the index in the array.

You might also want to look to see if using a Scripting.Dictionary, rather than a Collection, would give you any advantages.

edited Aug 01 '20 at 11:24

answered Aug 01 '20 at 09:56

freeflow

4,129
3
10
18

*"VBA does not support creating arrays of more than one dimension from static values"* I believe the answer by @FaneDuru demonstrates how to do that.. – Ron Rosenfeld Aug 01 '20 at 16:30
@RonRosenfeld FaneDuru has shown how to setup what is called a jagged array (an Array of Arrays) where each item in the jagged array is accessed using (x)(y) syntax and not (x,y) syntax.. You cannot create a two dimensional array (where you access items using (x,y) using the Array function and a single set of literals as shown in the OP code . – freeflow Aug 01 '20 at 17:18

score 1 · Answer 2 · answered Aug 01 '20 at 10:44

I am afraid you cannot (in the way you try). You may use an array of arrays, build in the next way and being handled like this:

Sub testSplitArrayBis()
  Dim Arr_labelpositions() As Variant, Int_Counter1 As Long, Int_Counter2 As Long
  Arr_labelpositions() = Array(Array("lbl_N", 114, 222, 104, 212), _
                Array("lbl_NO", 144, 144, 134, 154), _
                Array("lbl_O", 210, 252, 210, 256), _
                Array("lbl_ZO", 276, 222, 276, 232), _
                Array("lbl_Z", 300, 144, 310, 144), _
                Array("lbl_ZW", 276, 54, 276, 44), _
                Array("lbl_W", 210, 36, 210, 26), _
                Array("lbl_NW", 144, 54, 144, 44))
  'loop through array
  For Int_Counter1 = 0 To UBound(Arr_labelpositions)
        For Int_Counter2 = 0 To UBound(Arr_labelpositions(Int_Counter1))
            Debug.Print Arr_labelpositions(Int_Counter1)(Int_Counter2)
        Next Int_Counter2
    Next Int_Counter1
End Sub

Fill a two-dimensional array, split code on multiple lines

2 Answers2