Runtime error 9 subscript out of range - assigning array to variable


mark Stephens


I have a piece of code that:

Redims an array
Collects variables to fill it
assigns the array to a variable publicly declared as a variant

I am at a loss as this system works fine for me throughout the project but
in this instance throws up the error (Runtime 9 as in in the subject line of
this post) and will not fill the variable. Since the use of it seems
identical to other procedures throughout the workbook (and it works fine
there), I cannot for the life of me figure out why it has decided to stop
working here.

I have added watches for both arrays, and they both contain what they
should, but the second procedure fails at then end on the last line:
pvarrThatWorks = parrArray1 ()

Why I have no idea at all. If anyone has any ideas I would be most grateful.
Below is a procedure in which it works followed by the one that
(inexplicably) doesn't. I am sure it's blindingly obvious and I will feel
like a klutz (hope so:).

Thanks in advance, regards, Mark


Public pvarrThatWorks As Variant

Sub ProcedureThatWorks

Dim iCol As Integer

ReDim parrArray1 (10, 20)

For i Row = 1 To 10

'This fills the array
For iCol = 1 To 20

parrArray1 (iRow, iCol) =
Sheets("Database").Cells(iRow, iCol).Value

Next iCol

Next piRowInMainDatabase

pvarrThatWorks = parrArray1 ()

End Sub


Public pvarrThatDoesntWork As Variant

Sub ProcedureThatDOESNOTwork()

Dim iCol As Integer

'Creates an array containing Time and row number in main database which is
then searched to get the earliest for loading into the GUI, is added to each
time a new Callback is added for today
ReDim parrArray2 (10, 2)

For i Row = 1 To 10

parrArray2 (iRow,1) = Sheets("Database").Cells(iRow, 1).Value
parrArray2 (iRow,2) = iRow

Next i Row

pvarrThatDoesntWork = parrCallbacksDueToday()

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question