Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't create dynamic charts | Charts and Charting in Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |