How to declare a dynamic array
 

I want to declare an array, the size of which depends on the length of the
contents of a cell - so, if the cell contains 123456, I want to declare a 6
element array etc.
However, when I try to run the following code, I'm told that I have to use a
constant, as against an expression, when defining the array size.
Can anyone suggest a way around this, please?

Sub StringProcess()
Dim ArrayLength As Integer
ArrayLength = Len(ActiveCell.Value)
Dim StoreArray(ArrayLength) As Integer
End Sub

Thanks in advance for your help

Pete

Sub StringProcess()
Dim ArrayLength As Integer
ArrayLength = Len(ActiveCell.Value)
Dim StoreArray() As Integer
ReDim StoreArray(ArrayLength)
End Sub

"Peter Rooney" wrote in message
...
I want to declare an array, the size of which depends on the length of the
contents of a cell - so, if the cell contains 123456, I want to declare a
6
element array etc.
However, when I try to run the following code, I'm told that I have to use
a
constant, as against an expression, when defining the array size.
Can anyone suggest a way around this, please?

Sub StringProcess()
Dim ArrayLength As Integer
ArrayLength = Len(ActiveCell.Value)
Dim StoreArray(ArrayLength) As Integer
End Sub

Thanks in advance for your help

Pete

Thanks, Duke - much appreciated!

Pete



"Duke Carey" wrote:

Sub StringProcess()
Dim ArrayLength As Integer
ArrayLength = Len(ActiveCell.Value)
Dim StoreArray() As Integer
ReDim StoreArray(ArrayLength)
End Sub

"Peter Rooney" wrote in message
...
I want to declare an array, the size of which depends on the length of the
contents of a cell - so, if the cell contains 123456, I want to declare a
6
element array etc.
However, when I try to run the following code, I'm told that I have to use
a
constant, as against an expression, when defining the array size.
Can anyone suggest a way around this, please?

Sub StringProcess()
Dim ArrayLength As Integer
ArrayLength = Len(ActiveCell.Value)
Dim StoreArray(ArrayLength) As Integer
End Sub

Thanks in advance for your help

Pete

If you don't have
Option Base 1
at the top of your module, then StoreArray will be from 0 to arraylength.

You could be more explicit:

Option Explicit
Sub StringProcess()
Dim ArrayLength As Integer
Dim StoreArray() As Integer
ArrayLength = Len(ActiveCell.Value)
If ArrayLength 0 Then
ReDim StoreArray(1 To ArrayLength)
Else
'what should happen
MsgBox "Empty activecell"
End If
End Sub



Duke Carey wrote:

Sub StringProcess()
Dim ArrayLength As Integer
ArrayLength = Len(ActiveCell.Value)
Dim StoreArray() As Integer
ReDim StoreArray(ArrayLength)
End Sub

"Peter Rooney" wrote in message
...
I want to declare an array, the size of which depends on the length of the
contents of a cell - so, if the cell contains 123456, I want to declare a
6
element array etc.
However, when I try to run the following code, I'm told that I have to use
a
constant, as against an expression, when defining the array size.
Can anyone suggest a way around this, please?

Sub StringProcess()
Dim ArrayLength As Integer
ArrayLength = Len(ActiveCell.Value)
Dim StoreArray(ArrayLength) As Integer
End Sub

Thanks in advance for your help

Pete



--

Dave Peterson

Dave, A good tip with some useful error trapping!

Thanks a lot!

Pete



"Dave Peterson" wrote:

If you don't have
Option Base 1
at the top of your module, then StoreArray will be from 0 to arraylength.

You could be more explicit:

Option Explicit
Sub StringProcess()
Dim ArrayLength As Integer
Dim StoreArray() As Integer
ArrayLength = Len(ActiveCell.Value)
If ArrayLength 0 Then
ReDim StoreArray(1 To ArrayLength)
Else
'what should happen
MsgBox "Empty activecell"
End If
End Sub



Duke Carey wrote:

Sub StringProcess()
Dim ArrayLength As Integer
ArrayLength = Len(ActiveCell.Value)
Dim StoreArray() As Integer
ReDim StoreArray(ArrayLength)
End Sub

"Peter Rooney" wrote in message
...
I want to declare an array, the size of which depends on the length of the
contents of a cell - so, if the cell contains 123456, I want to declare a
6
element array etc.
However, when I try to run the following code, I'm told that I have to use
a
constant, as against an expression, when defining the array size.
Can anyone suggest a way around this, please?

Sub StringProcess()
Dim ArrayLength As Integer
ArrayLength = Len(ActiveCell.Value)
Dim StoreArray(ArrayLength) As Integer
End Sub

Thanks in advance for your help

Pete



--

Dave Peterson