Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
redim multiarray
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
redim multiarray
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 "ranswert" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
redim multiarray
"ranswert" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
redim multiarray
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. -- HTH... Jim Thomlinson "ranswert" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ReDim Array | Excel Programming | |||
ReDim Array | Excel Programming | |||
ReDim Matrix | Excel Programming | |||
ReDim Array | Excel Programming | |||
Dim and Redim | Excel Programming |