[AccessD] ReDim Preserve a multidimensional array

Hi all

Charlotte reminded recently that a Variant can nicely hold an array.

Just had to deal with how to ReDim a multidimensional array for another
dimension than the last while preserving the content.
You can't do that in VBA (browse the on-line help if in doubt), but
here's a workaround:

You can simulate it by copying the array via one temporary Variant.

Here's an example:

Public Sub ArrayCopy()

  Dim avarA() As Variant
  Dim avarT   As Variant
  Dim lngX    As Long
  Dim lngY    As Long
  
  ReDim avarA(0 To 1, 0 To 1)
  
  avarA(0, 0) = "AA"
  avarA(1, 0) = "BA"
  avarA(0, 1) = "AB"
  avarA(1, 1) = "BB"
  
  Debug.Print UBound(avarA, 1), UBound(avarA, 2)
  Debug.Print LBound(avarA, 1), LBound(avarA, 2)
  
  ' Create copy of array.
  avarT = avarA()
  ' ReDim to 10 as Ubound for first dimension and erase array.
  ReDim avarA(LBound(avarA, 1) To 10, LBound(avarA, 2) To UBound(avarA,
2))
  ' Refill array from copy.
  For lngX = LBound(avarT, 1) To UBound(avarT, 1)
    For lngY = LBound(avarT, 2) To UBound(avarT, 2)
      avarA(lngX, lngY) = avarT(lngX, lngY)
    Next
  Next
  
  Debug.Print UBound(avarA, 1), UBound(avarA, 2)
  Debug.Print LBound(avarA, 1), LBound(avarA, 2)
  Debug.Print UBound(avarT, 1), UBound(avarT, 2)
  Debug.Print LBound(avarT, 1), LBound(avarT, 2)
  Debug.Print avarA(0, 0)
  Debug.Print avarA(1, 0)
  Debug.Print avarA(0, 1)
  Debug.Print avarA(1, 1)
  
End Sub

/gustav