using arrays in vba
 

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

using arrays in vba
 

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)

"JulieD" wrote in message
...
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

using arrays in vba
 

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

"Bob Phillips" wrote in message
...
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)

"JulieD" wrote in message
...
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

using arrays in vba
 

Thanks Bob & Fred

will give it a go tomorrow.

Cheers
JulieD

"fred" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)

"JulieD" wrote in message
...
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

using arrays in vba
 

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

JulieD wrote:

Thanks Bob & Fred

will give it a go tomorrow.

Cheers
JulieD

"fred" wrote in message
...

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

"Bob Phillips" wrote in message
.. .

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)

"JulieD" wrote in message
. ..

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