error 9 subscript out of range

Question

I'm trying to populate a dynamic array with data from a sheet. I get the "error 9 subscript out of range". Why?

Sub correct()

Dim row As Integer, i As Long
Dim constants() As Double 'this declares the dynamic array
row = 1
i = 0

ReDim constans(0) 'this resizes the array(rediminsion's the array)

Do Until ThisWorkbook.Sheets("Deg 4").Cells(row, 1).Value = ""
    constants(i) = ThisWorkbook.Sheets("Deg 4").Cells(row, 1).Value
    i = i + 1 'increments array index
    ReDim Preserve constants(i) 'resize the array 1 larger and preserves previous data
    row = row + 1 'increments the worksheet row
Loop 

End Sub

Answer 1

You misspelled your array's name:

ReDim constans(0)

Should be:

ReDim constants(0)

So, by mistake, you're using ReDim to declare a new array called constans, and constants remains unallocated. When you get to the constants(i) = ... line in your procedure, you get that error because constants is unallocated.

I had never come across this particular screw-up before; I'm actually appalled that the VBA language is set up to allow this! Microsoft does warn against this (emphasis mine):

Caution: The ReDim statement acts as a declarative statement if the variable it declares doesn't exist at module level or procedure level. If another variable with the same name is created later, even in a wider scope, ReDim will refer to the later variable and won't necessarily cause a compilation error, even if Option Explicit is in effect. To avoid such conflicts, ReDim should not be used as a declarative statement, but simply for redimensioning arrays.

Ok, thanks for the well-hidden warning, but they should just not have given ReDim declarative functionality in the first place.