Search code examples
excelvbamultidimensional-arrayruntime-error

Redimensioning bidimensional array gives error nº 9 "subscript out of range" in second iteration of loop: - Can't figure out the reason

This error is driving me crazy.

I followed all tutorials just in case but I can't figure out the reason my code is giving error nº9 in the line where I redim the array inside the loop. It is happening in the second round of the loop.

Here is the code:

   Dim ar() As Variant
   Dim cont As Integer
   'Here goes some SQL and querying that works and does not matter for this post. The results are returned well.
  
   cont = 0
   Do Until rs.EOF
    resul = rs!f1 & " " & rs!f2
    'Error thrown in the second round of the loop in the following line:
    ReDim Preserve ar(cont, 1)
    ar(cont, 0) = rs!ID
    ar(cont, 1) = resul
    rs.MoveNext
    cont = cont + 1
Loop

I really can't figure out why is happening the error. The syntax is Ok as far as tutorials say. I wanted to create this bidimensional array so that I can attach it to a combobox.List in a userform.

Again, the recordset returned is ok, the problem seems to be the array at some point.

Solution

  • From MS documentation for ReDim statement.

    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. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array.

    I haven't been able to recreate your error but believe it has something to do with the above.