redim multiarray

[ranswert]

How do I declare a multidimensional array where the first dimension
upperbound is known, but the second dimension upperbound is not known ?
Also, how do I 'redim' that array when the second dimension upperbound is
known?
Thanks

 

[Mark Ivey]

I had a similar problem recently...

Although it might not have been the most appropriate method to use... I set
all the array dimensions to something extremely high like 500,000. This
allowed for the growth I would need for different datasets.

Just my $0.02 worth...

Mark

 

[Skip]

How do I declare a multidimensional array where the first dimension
upperbound is known, but the second dimension upperbound is not known ?
Also, how do I 'redim' that array when the second dimension upperbound is
known?
Thanks

I found this in my code library. It was for Access VBA but I think it should
be the same for Excel. Hope it helps. It is a demonstration routine.
=========================================================
Dim m_Array() As String

Private Sub Demo()
Dim l_x As Integer
m_Array = GetArray
For l_x = 1 To UBound(m_Array, 2)
Debug.Print "m_array(1, " & l_x & ")=" & m_Array(1, l_x) & " ...
m_array(2, " & l_x & ")=" & m_Array(2, l_x)
Next
End Sub

'=== This returns a 2 dimensional dynamic array
Public Function GetArray() As Variant
Dim l_Array() As String
Dim l_x As Integer
Dim l_y As Long

ReDim l_Array(2, 0) '-- Clear/initialize the array.
l_y = 1
For l_x = 1 To 10
'-- You can only use Preserve if you ReDim the outer dimension
' the [2] values are the number of dimensions.
'---------------------------------------------------------------
ReDim Preserve l_Array(2, UBound(l_Array, 2) + 1)
'---------------------------------------------------------------
l_y = l_y * 3
'-- Note how UBound is used to reference the number of entries in
l_Array
Debug.Print "Adding " & l_x & " and " & l_y
l_Array(1, UBound(l_Array, 2)) = l_x
l_Array(2, UBound(l_Array, 2)) = l_y
Next l_x
GetArray = l_Array
End Function

 

[Jim Thomlinson]

The rule with multi dim arrays is that you can only redim the last element in
the array. Once created the first element is permanent. So code like this
will work...

Sub test()
Dim ary() As String

ReDim ary(10, 10)
ary(1, 1) = "Tada"
ReDim Preserve ary(10, 11)
End Sub

But code like this will not work
Sub test()
Dim ary() As String

ReDim ary(10, 10)
ary(1, 1) = "Tada"
ReDim Preserve ary(11, 11)
End Sub

It has to do with the way memory is stored. A multi dim array, like any
array, is stored as a big long memory string. Adding to the final element
adds another entire block to the end of the memory. Adding to the first
element(s) would require adding more memory to each of the already existing
block which it just will not do.