Assigning a value to an dynamic array element resu

Question

Sub ArrTest()
Dim Arr() As Variant

    Arr(1, 1) = "Test"

End Sub

I did not dimension the array as I do not know what size it will need to be. I will eventually be writing this array to a spreadsheet. I was attempting to assign the upper left hand corner of the cells that would be written in the future as "test".

Now if I understand correctly dynamic arrays start to index at 0, so the first element is really Arr(0,0). I tried Arr(0,0) and got the same error.

I guess the real question is:

How to assign a value to an element of an undefined dynamic array?

Answer 1

You'll have to dim the array to a size before you use it. This is pretty simple though:

Sub ArrTest()
    Dim Arr() As Variant

    redim Arr(0 to 1, 0 to 1)
    Arr(1, 1) = "Test"

    redim preserve Arr(0 to 2, 0 to 1)
    Arr(2, 1) = "Test2"

End Sub

The redim method will redimension your array. Using the keyword Preserve will insure that the data stored in the array is not lost during the redimensioning.