ReDim string in loop

[Arne Hegefors]

I am redimming an array in a loop. I do not know in before hand how many
times the loop will run. the array must have the siame size as the number of
iterations in the loop. My problem is that I ReDim the array using the number
of iterations as upper boundary. However I get the error message "index
outside of interval". I do not understand how this can happen. Perhaps
someone can help me? Any help is appeciated! Thanks very much in advance!

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
ReDim strMaturityArray(0 To r)
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
strMaturityArray(r) = Mid(rngSecID.Offset(r, 0), InStr(InStr(1,
rngSecID.Offset(r, 0), " ") + 1, rngSecID.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(r)
End If
r = r + 1
Loop

 

[Bob Phillips]

Where is the Redim statement Arne? I would have expected to see it within
the loop, something like

Do Until ...
Redim Preserver ary (r)
ary(r) = some_value
r=r+1
Loop

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Arne Hegefors]

Hello Bob! I have a ReDim at the second line in my loop. Am I missing
something (I have a terrible cold and cannot really think straight..). Every
time I run the loop I increase the array using the variable r. At the very
beginning of the loop I redim the array so that I will always have space. I
suppose you have seen the code I have missed something but I honestly do not
see it. Please, any help is appreciated. Thank you very much!

"Arne Hegefors" skrev:

 

[Andrew Taylor]

Um, the ReDim statement is the second line of her code..

Also note typo:
Redim Preserver ary (r)
should be
Redim Preserve ary (r)

 

[Andrew Taylor]

Anne - what value does r have initally? If it's negative you would
get a "Subscript out or range" error.

Andrew

 

[Andrew Taylor]

These typos are catching - "Subscript out OF range"

 

[Arne Hegefors]

Hello! Yes I think there might have been a problem with r previously in the
code. I really dont get it but after having made some changes it all works
fine now. At least that part of the code! Thanks for all your help!

"Andrew Taylor" skrev:

 

[Bob Phillips]

Arne,

That was me, I must have a cold as well.

I cannot reproduce your problem, but there are at least 2 problems in your
code, you create a sparse array, and you overwrite the data.

Maybe this will work better.

ReDim strMaturityArray(0 To iArray)
Do Until IsEmpty(rngSecId.Offset(r, lngTypeColumn))
If rngSecId.Offset(r, lngTypeColumn).Text = strGovBond Then
ReDim Preserve strMaturityArray(0 To iArray)
strMaturityArray(iArray) = Mid(rngSecId.Offset(r, 0), _
InStr(InStr(1, rngSecId.Offset(r, 0), " ")
+ 1, _
rngSecId.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(iArray)
iArray = iArray + 1
End If
r = r + 1
Loop

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Arne Hegefors]

Thanks Bob! No problem! I will look into your code! Many thanks again!

"Bob Phillips" skrev:

 

[Alan Beban]

Where is the Redim statement Arne? I would have expected to see it within
the loop, something like

Do Until ...
Redim Preserver ary (r)
ary(r) = some_value
r=r+1
Loop

I think it's probably much more efficient to ReDim Preserve once at the
end, a la

Dim ary()
reDim ary(n)
Do Until . . .
ary(r) = some_value
r=r+1
Loop
ReDim Preserve ary(r-1)

where n is a number big enough to accommodate all possible iterations,
rather than ReDim Preserve in each iteration of the loop.

Alan Beban

 

[Bob Phillips]

Yeah, makes sense, but Sod's law will say that one time you don't dim it big
enough.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dave Peterson]

I remember seeing Myrna Larson's code a long time ago.

She would redim in groups of 100 keeping track of how many were used:

I found one sample where she used 20, but the theory would still hold:

Function GetFileList(FileSpec As String, FileNames() As String) As Integer
Dim Max As Integer
Dim NumFiles As Integer
Dim AFile As String

Max = 0
NumFiles = 0

AFile = Dir$(FileSpec)
Do While Len(AFile)
NumFiles = NumFiles + 1
If NumFiles > Max Then
Max = Max + 20
ReDim Preserve FileNames(1 To Max) As String
End If
FileNames(NumFiles) = AFile
AFile = Dir$
Loop
GetFileList = NumFiles
If NumFiles Then ReDim Preserve FileNames(1 To NumFiles)

End Function 'GetFileList

From:
http://groups.google.co.uk/group/mi...29169?lnk=st&q=&rnum=5&hl=en#08a17ff238229169

or
http://tinyurl.com/hkowm