Dimensioning Arrays
 

I think I need to use an array, but it insists I use 'constant' expressions
when declaring the array, but I want to change its size depending on the
data in use. ReDim would seem an appropriate option, but that too wants
constant expressions and hence I cannot use variables to define the required
size.

Is this a limitation of VBA (I find hard to believe) or have I missed
something obvious?

Thank

Peter M

Dimensioning Arrays
 

Peter,

You can use a variable. This little example shows that you can do that,
using an element of the same array itself

Dim av() As Long

ReDim av(3)
av(0) = 1
av(1) = 2
av(2) = 3
av(3) = 4
ReDim Preserve av(av(3))
av(4) = 9
MsgBox av(4)

--

HTH

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

"Peter M" wrote in message
...
I think I need to use an array, but it insists I use 'constant'
expressions
when declaring the array, but I want to change its size depending on the
data in use. ReDim would seem an appropriate option, but that too wants
constant expressions and hence I cannot use variables to define the
required
size.

Is this a limitation of VBA (I find hard to believe) or have I missed
something obvious?

Thank

Peter M

Dimensioning Arrays
 

Declare the array without dimensions first:

Dim arr()
ReDim arr(1 to 3)

Alan Beban

Peter M wrote:
I think I need to use an array, but it insists I use 'constant' expressions
when declaring the array, but I want to change its size depending on the
data in use. ReDim would seem an appropriate option, but that too wants
constant expressions and hence I cannot use variables to define the required
size.

Is this a limitation of VBA (I find hard to believe) or have I missed
something obvious?

Thank

Peter M