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

Jean-François Corbett · Accepted Answer · 2013-11-04T09:54:17.637

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.

Thanks for the tip on the declarative ReDim. It hasn't caught me before but fore-warned is fore-armed. I won't be fooled by it in future! — Mark Fitzgerald, Nov 04 '13 at 10:33
Thanks this really helped, also thanks for the warning about ReDim. Im only using ReDim because its the only way that I know how to create a dynamic array. Is the a better way to do this with out using ReDim? — terence vaughn, Nov 04 '13 at 15:22
No. That's the way to use it -- as long as you spell the array name correctly! — Jean-François Corbett, Nov 04 '13 at 19:47

score 0 · Answer 2 · answered Nov 04 '13 at 10:01

The problem is in your declarations

Dim constants() As Double 'this declares the dynamic array

but you ReDim a different undeclared array

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

Adding the missing "t" to "constans" will fix that. If you want to avoid inadvertently using undeclared variables then go to Tools..> Options..>Editor tab and make sure the "Require Variable Declaration is checked - this will insert Option Explicit at the top of every module you create (but not your existing ones).

With Option Explicit and CamelCase variables declared, as you enter your variable names in lower case (i.e. camelcase) the next time you press Enter, they will change to CamelCase as declared confirming that the name entered has been declared. If it doesn't change then it's undeclared and it will cause a compile error when you try to run the procedure.

Before you run your procedure, compile it by going to Debug..> Compile VBA Project or press Alt > d > l. If you don't see a message box, then it will probably run

PS: You may get unexpected results with your current loop because my testing showed constants(0) = 1 and so on until constants(last i) = 0. Probably not what you want but if it is, then...all good!

`Option Explicit` won't help in this case. Error won't show at compile time because `constans` is *not* undeclared; it's declared by `ReDim`. See [my answer](http://stackoverflow.com/a/19764824/119775), which you might have wanted to read before posting yours. Good point regarding the camelCase trick, though; I would also suggest using autocomplete (Ctrl-space) to prevent typos. — Jean-François Corbett, Nov 04 '13 at 10:13
@Jean-FrançoisCorbett I was slowly typing while you beat me to the answer. I did notice before posting that another answer had been posted but was a bit scared that looking at it may have wiped out all my work. — Mark Fitzgerald, Nov 04 '13 at 10:37

error 9 subscript out of range

2 Answers2