Two Dimensional array and subscription out of range error

[DevAccess]

Hello

I would like to store the values in two dimensional array as below;
Arr(0,0)="test1", Arr(0,1)="test2", Arr(0,2)="test3"....
Arr (1,0)="test1". Arr(1,1)="va1", Arr(1,2)="test3"...
.........
so I written code as below
Dim Arr() as Variant
Dim Counter as Long

Counter =0
Do while (some condition)
redim preserve Arr(Counter, 0)
Arr(Counter, 0)="test1"
redim preserve Arr(Counter, 1)
Arr(Counter, 1)="test1"
redim preserve Arr(Counter, 2)
Arr(Counter, 2)="test2"
Counter = Counter +1
loop

But when it finishes the first loop and goes second time the below line gives error.
redim preserve Arr(Counter, 0) ..> subscription out of range

Please assist what is wrong

 

[arnelgp]

is it a Fixed size array?

Dim arr(1 To 3, 1 To 3) As String
Dim Counter As Integer
Dim innerLoop As Integer

Counter = 1
Do While Counter <= 3
Arr(Counter, 1)="Test1"
Arr(Counter, 2)="Test1"
Arr(Counter, 3)="Test2"
Counter = Counter + 1
Loop

 

[DevAccess]

Thanks but it is not fixed size array, it is dynamic array Counter would go upto any value.

 

[arnelgp]

Use Collection:

Private Sub test2()
    Dim col As Collection
    Dim arr(1 To 3) As Variant
    Dim Counter As Integer
    Dim i As Integer
    Dim varArray As Variant
    
    Set col = New Collection
    Counter = 1
    Do While Counter <= 10 'put condition here
        arr(1) = "test1"
        arr(2) = "test2"
        arr(3) = "test3"
        col.Add arr, "arr" & Counter
        Counter = Counter + 1
    Loop
    MsgBox col.Count
    For Counter = 1 To col.Count
        Debug.Print Counter
        varArray = col.Item(Counter)
        For i = 1 To UBound(varArray)
            Debug.Print varArray(i)
        Next
        Debug.Print ""
    Next
End Sub

 

[DevAccess]

Use Collection:

Private Sub test2()
    Dim col As Collection
    Dim arr(1 To 3) As Variant
    Dim Counter As Integer
    Dim i As Integer
    Dim varArray As Variant
    
    Set col = New Collection
    Counter = 1
    Do While Counter <= 10 'put condition here
        arr(1) = "test1"
        arr(2) = "test2"
        arr(3) = "test3"
        col.Add arr, "arr" & Counter
        Counter = Counter + 1
    Loop
    MsgBox col.Count
    For Counter = 1 To col.Count
        Debug.Print Counter
        varArray = col.Item(Counter)
        For i = 1 To UBound(varArray)
            Debug.Print varArray(i)
        Next
        Debug.Print ""
    Next
End Sub

Thanks but is it not possible to have two dimension array having used redim preserve someway

 

[DevAccess]

I am not sure if this [collection] would help me to achieve what I want or not

 

[arnelgp]

here is a module that will expand your array.
Note: You only need to Redim the Arr() once in
the Loop and not every time you assign value to it.

Import the module in VBE.

to use:


Dim Arr() As Variant
Dim Counter As Long
Dim SecondTime As Boolean

Counter = 1
Do While (some condition...)
If Not SecondTime Then
SecondTime=True
Redim Preserve Arr(1 To Counter, 1 To 3)
Else
Arr = ExpandArray(Arr, 1, 1, "")
End If
Arr(Counter,1)="Test1"
Arr(Counter,2)="Test1"
Arr(Counter,3)="Test2"
Counter = Counter + 1
Loop

 

[Solo712]

Hello

I would like to store the values in two dimensional array as below;
Arr(0,0)="test1", Arr(0,1)="test2", Arr(0,2)="test3"....
Arr (1,0)="test1". Arr(1,1)="va1", Arr(1,2)="test3"...
.........
so I written code as below
Dim Arr() as Variant
Dim Counter as Long

Counter =0
Do while (some condition)
redim preserve Arr(Counter, 0)
Arr(Counter, 0)="test1"
redim preserve Arr(Counter, 1)
Arr(Counter, 1)="test1"
redim preserve Arr(Counter, 2)
Arr(Counter, 2)="test2"
Counter = Counter +1
loop

But when it finishes the first loop and goes second time the below line gives error.
redim preserve Arr(Counter, 0) ..> subscription out of range

Please assist what is wrong

Hi,
the error you get is because you have not redim the the second dimension of the array.

redim preserve Arr(Counter, [COLOR="Red"]0[/COLOR])

Should be the maximum value of the second column, or if that is unknown set up a different counter for it.

Best,
Jiri