Declaring Dynamic Multi-dimensional Array
apparently you didn't follow what I said in the latter part of my answer.
Dim myArray as Variant
myArray = Range("A1").Resize(lngRowCount,lngColumncount).Val ue
debug.print lbound(myarray,1), ubound(myarray,1)
debug.print lbound(myarray,2), ubound(myarray,2)
gives you the dimensions.
But, more importantly, you can make your code magnitudes faster with:
Dim lngLastRow as long, lngLastCol as Long
Dim lngRow as long, lngCol as long
Dim sStr as String
dim aryEmployeeList as Variant
aryEmployeeList = ActiveSheet.UsedRange.Value
lngLastRow = Ubound(aryEmployeeList,1)
lngLastCol = Ubound(aryEmployeeList,2)
' now treat aryEmployeeList like any array
' to demo:
for lngRow = 1 to lngLastRow
for lngCol = 1 to lngLastCol
sStr = sStr & aryEmployeelist(lngRow,lngCol) & " "
Next
debug.print sStr
sStr = ""
Next
There is no need to loop either picking up the values or putting them back
down (if you need to).
Regards,
Tom Ogilvy
"JohnV" wrote in message
...
Thank you for you help. That did the trick.
Now I am wondering if there is an easy way to determine
the size of a multidensional array whose dimensions change
all the time.
For those of you interested here is my code:
dim aryEmployeeList() as string
ActiveCell.SpecialCells(xlLastCell).Select
lngLastRow = ActiveCell.Row
lngLastCol = ActiveCell.Column
ReDim aryEmployeeList(1 To lngLastRow, 1 To lngLastCol)
For lngRow = 1 To lngLastRow
For lngCol = 1 To lngLastCol
Cells(lngRow, lngCol).Select
aryEmployeeList(lngRow, lngCol) = Selection.Value
Next
Next
Thanks,
JohnV
-----Original Message-----
Dim myArray() as String
redim myArray(1 to lngRowCount, 1 to lngColumnCount)
A quick way to turn a range's values into an array is
Dim myArray as Variant
myArray = Range("A1").Resize
(lngRowCount,lngColumncount).Value
debug.print lbound(myarray,1), ubound(myarray,1)
debug.print lbound(myarray,2), ubound(myarray,2)
Regards,
Tom Ogilvy
JohnV wrote in message
...
I want to build a dynamic multidimensional array. I
will
then populate the array from the rows of a worksheet.
The
problem is the only example I can find for declaring a
mulidimensional array is:
dim myArray(1 to 5, 1 to 10) as single
Where I am using the following variables for the upper
bounds of the array:
lngRowCount, lngColumnCount
When I try to declare the array I get an error:
dim myArray(1 to lngRowCount, 1 to lngColumnCout) as
string
Compile Error: Constant expresssion required.
Any Help?
Thanks,
JohnV
|