Debugging VBA code - appending values to array

Question

I am trying to use code of the following form to populate an array of x rows:

Dim myarray() As Variant
Dim string1 As String

Dim myarray_ubound As Integer
myarray_ubound = 0

For i = 1 to x

  myarray_ubound = myarray_ubound + 1
  ReDim Preserve myarray(1 To myarray_ubound, 1 To 2)

  myarray(myarray_ubound,1) = i
  myarray(myarray_ubound,2) = string1

Next i

However, when I run it it gets stuck after the first loop, telling me that a subscript is out of range. Debugging takes me to the ReDim command.

I used myarray_ubound as an alternative to calling the UBound function many times, but I have tried using this as well and I get the same error.

Can anyone spot what's gone wrong?

Answer 1

you can only redim the last element of the array (not the first), see below. As you know x I would suggest to redim your array with this value from the beginning.

Dim myarray() As Variant
Dim string1 As String

Dim myarray_ubound As Integer
myarray_ubound = 0

For i = 1 To 10

  myarray_ubound = myarray_ubound + 1
  ReDim Preserve myarray(1 To 2, 1 To myarray_ubound)

  myarray(1, myarray_ubound) = i
  myarray(2, myarray_ubound) = string1

Next i

Answer 2

See: http://msdn.microsoft.com/en-us/library/aa266231.aspx "If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all."

Try

ReDim Preserve myarray(1 To 2, 1 To myarray_ubound)

instead.

Answer 3

Since you are declaring the variable as a Variant i don't see why you need to re-dim. (maybe related to memory issues, feel free to fill in).

I would suggest:

For i = 1 to 10

myArray(1, i-1) = i
myArray(2, i-1) = string1

next i