using arrays in vba

[JulieD]

Hi All

trying to come to grips with arrays ... i have the situation where i am
getting users to enter up to 60 numbers in a userform, i then need to use
these numbers in lots of different ways. So i have declared an array and i
populate it by cycling through the controls etc (all this works fine).
However, when i Dimmed the array i nominated the number of elements ..
Dim pipes(59) as long

but, of course if they don't enter 60 elements the array is too big. Now i
know that i could count the number of items (prior to populating the array)
and use the redim statement to set the size, but this seems like a bit of a
waste of time ... i've tried redim preserve after populating the array but
get told that the array has already been something or other (sorry not in
front of the screen and can't remember the exact wording).

what i would like to do is define & populate the array in the most efficient
way possible ... and would welcome any suggestions.

Regards
JulieD

 

[Bob Phillips]

Julie,

Try redimming as you go. Something like

For Each ctl in MyControls
If ctl.Value <> "" then
Redim Preserve myArray(i)
i = i + 1
End If
Next ctl

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[fred]

If you are to use Bob's suggestion then you must first declare the array as
a dynamic array. That is without the number of elements.

Dim pipes() as long

Fred

 

[JulieD]

Thanks Bob & Fred

will give it a go tomorrow.

Cheers
JulieD

 

[Alan Beban]

Fred's suggestion is the key. Rather than

Dim pipes(59) As Long

use

Dim pipes() As Long
ReDim pipes(1 to 59) [or ReDim pipes(0 to 59) if appropriate]

Then *do not* ReDim inside the loop as Bob Phillips suggested, which
inefficiently ReDim Preserves for every successful iteration (and in
any event doesn't work without implementing Fred's suggestion), but
ReDim Preserve once after the end of the loop as you originally tried to do.

Alan Beban