I was reasonably sure I shared it about a year and a half ago, but
maybe not...
In any case, I'll see if I can dig up the code from wherever I put it.
The basic idea is that since everything is a variant, there are few
restrictions on what cannot be done. The price, of course, is
performance in that all access is through variant data structures.
Here's an example I put together that allows the resizing of any
dimension of a array of variants in variants.
Option Explicit
Sub ReDimN(ByRef aStruc, ByVal whatDim As Integer, ByVal newSize As
Integer)
Dim oldDim As Integer, i As Integer
'this code is missing safety checks
If whatDim = 1 Then
oldDim = UBound(aStruc)
ReDim Preserve aStruc(1 To newSize)
For i = oldDim + 1 To newSize
aStruc(i) = aStruc(oldDim)
Next i
Else
For i = LBound(aStruc) To UBound(aStruc)
ReDimN aStruc(i), whatDim - 1, newSize
Next i
End If
End Sub
Sub test2D()
Dim aStruc, temp, i As Integer
ReDim aStruc(1 To 8)
'we need temp because
VB won't allow redim aStruc(i) (1 to 10)
ReDim temp(1 To 10)
For i = 1 To 10
temp(i) = i
Next i
For i = LBound(aStruc) To UBound(aStruc)
aStruc(i) = temp
Next i
ReDimN aStruc, 1, 12
ReDimN aStruc, 2, 15
End Sub
Sub test3D()
Dim aStruc, temp, i As Integer, j As Integer, k As Integer
ReDim aStruc(1 To 8)
'we need temp because
VB won't allow redim aStruc(i) (1 to 10)
ReDim temp(1 To 10)
For i = LBound(aStruc) To UBound(aStruc)
aStruc(i) = temp
Next i
ReDim temp(1 To 5)
For i = LBound(aStruc) To UBound(aStruc)
For j = LBound(aStruc(i)) To UBound(aStruc(i))
For k = LBound(temp) To UBound(temp)
temp(k) = i * j * k
Next k
aStruc(i)(j) = temp
Next j
Next i
ReDimN aStruc, 1, 10
ReDimN aStruc, 2, 12
ReDimN aStruc, 3, 8
End Sub
--
Regards,
Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article ,
says...
Tushar Mehta wrote:
. . . A long time ago, I wrote a VBA class based on this concept. . . . it also allowed one to resize *any* dimension of a N
dimension array while preserving the existing data.
Are you willing to share it with us?
Alan Beban