Redim Preserve dynamic array gives subscript out of range - Access

G

Guest

I have the following code, which as far as I can tell from the Knowledge
Base, should be correct. However, when it gets to the Redim Preserve
statement I get a Runtime Error 9: Subscript out of range.

Dim rstDROP As ADODB.Recordset
Dim strSQLDROP As String
Set rstDROP = New Recordset
Dim intCountOfDropArray As Integer
Dim sngTotalDropTime As Single
Dim arrDropArray() As Variant
ReDim arrDropArray(1 To 4, 1 To 1)

....
(SQL statement is built in strSQLDROP, file is opened, first set of records
is read into arrDropArray. So far so good...)
(the file contains an unknown number of records, each of which contains an
unknown number of dates. For each record I am finding the number of dates it
contains, the earliest and latest dates in the record, and the "drop date"
(when can we purge this record) which starts out equal to the latest date but
is then added to later in the module based on various criteria. So we have 4
pieces of info for each record.)
....
(We've detected the end of the current record and want to start a new one.
To do this we must increment the last dimension of the array by one...and
this is where the error occurs.)

.MovePrevious 'go back and close out previous record
arrDropArray(2, intCountOfDropArray) = !datBenDate 'nth end date in array
arrDropArray(3, intCountOfDropArray) = sngTotalDropTime 'nth number of dates
arrDropArray(4, intCountOfDropArray) = !datBenDate 'nth drop date
sngTotalDropTime = 0
.MoveNext 'now start entering the new record
ReDim Preserve arrDropArray(4, UBound(arrDropArray, 2) + 1)
intCountOfDropArray = intCountOfDropArray + 1
arrDropArray(1, intCountOfDropArray) = !datBenDate 'nth start date in array

What am I doing wrong?
 
G

George Nicholson

Redim Preserve only works when you change the last dimension of an array
ReDim arrDropArray(1 To 4, 1 To 2)
....
ReDim Preserve arrDropArray(4, UBound(arrDropArray, 2) + 1)

This should work:
ReDim Preserve arrDropArray(1 to 4, 1 to UBound(arrDropArray, 2) + 1)

What you had *might* work if you had an "Option Base 1" statement at the top
of your module, specifying that arrays start with element 1 (the default is
element #0). I'm sort of assuming you don't and that is the problem. Your
Preserve specifies that the 1st dimension should have 4 elements starting at
0 (e.g., "0 to 3"), but you can only change the last dimension of an array
with Preserve, so you get an error. (You're probably not doing what you
think with the 2nd dimension either: resizing it to " 0 to x" not "1 to x").
However, I don't know that to be a fact since I've never tried it, I just
specify my starting points explicitly and my arrays are happy puppies.

HTH,
 
G

Guest

Option Base 1 did the trick. Thank you! Foolishly I had thought that since
you could only adjust the upper bound, you didn't have to respecify the lower
bound...
 
G

George Nicholson

Thanks for the feedback. Glad to know Option Base really makes a difference
like that.

Maybe I'll even use that knowledge some day... :)
 

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

Top