[JohnV[_2_]]

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

[JohnV[_2_]]

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

[Tom Ogilvy]

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