Declaring Dynamic Multi-dimensional Array

  • Thread starter Thread starter JohnV
  • Start date Start date
J

JohnV

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
 
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
 
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).Value

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
 
Back
Top