VBA arrays, Variants and Variant arrays
This answer provides the background necessary to understand some of the code within the other answers and to understand why I rejected an alternative approach.
To declare simple variables, I write:
Dim A As Long
Dim B As String
Dim C As Boolean
Dim D As Integer
Dim E As Double
VBA has a selection of intrinsic data types that are not very different from those available with other languages.
VBA has another type:
Dim F As Variant
A Variant might be thought of as untyped or as a container. If I write:
A = 5 ' OK because A is Long
A = "abc" ' Will fail a n alphabetic string cannot be saved in a Long
A = "123" ' OK because string "123" is automatically converted to integer 123
On the other hand, I can write the following without any failures:
F = 5
F = "abc"
F = True
F = 1.23
Each of these values will be held correctly. F can be used in any expression for which its current value is appropriate:
F = 5
F = F + 2
F = "abc"
F = F & "def"
The above statements are all valid but
F = "abc"
F = F + 2
will fail because after setting F to "abc", it cannot be used in an arithmetic expression.
A Variant can also hold an Excel worksheet, a Word document or any Office object. A Variant can also hold an array. When a Variant holds an object or an array, the syntax is as though the Variant has become that object or array. So:
F = Worksheets("Data”)
F.Range("A1") = "abc"
Above, F is now effectively a variable of type Worksheet and any of a Worksheet’s properties or methods can be accessed by F. This was just to give a brief taste on the full scope of Variants; the remainder of this tutorial is limited to arrays.
I can “convert” a Variant to an array in one of two ways:
1) F = VBA.Array(1, "abc", True)
2) ReDim F(0 To 2)
VBA.Array is a function which returns a one-dimensional Variant array with lower bound 0 and enough elements to hold the supplied values. I can also write F = Array(1, "abc", True). Function Array is the same as Function VBA.Array except the lower bound depends on the present and value of the Option Base command.
I only use function Array if I am going to use function LBound to determine the lower bound. I do not fully understand what is and what is not effected by the Option Base command since it is not fully documented. I have seen differences between different versions of different Microsoft products which I am sure are accidental. I am confident a new Microsoft programmer has assumed an old product operates in a sensible manner when it does not. I am very careful to specify both lower and upper bounds if I can. If I cannot specify the lower bound, I check it. I still use routines I wrote under Excel 2003. I believe the lack of problems I encounter with old routines is because I avoid making assumptions about how Excel operates if it is not fully documented.
Returning to the tutorial, ReDim F(0 To 2) effectively converts F to an array with three elements..
All previous discussions have been about one-dimensional arrays. Conventional multi-dimensional arrays are also possible:
Dim G(1 to 5) As Long
Dim H(1 to 5, 1 To 4) As String
Dim I(1 to 5, 1 To 4, 0 To 3) As Boolean
or
Dim G() As Long
Dim H() As String
Dim I() As Boolean
ReDim G(1 to 5)
ReDim H(1 to 5, 1 To 4)
ReDim I(1 to 5, 1 To 4, 0 To 3)
With the first block, the number and size of the dimensions are fixed at compile time. With second block, the number and size of the dimensions are set at runtime and can be changed.
In either case, the syntax for access is:
G(n) = 3
H(n, m) = "abc"
I(n, m, o) = True
This type of multi-dimensional is inappropriate for your requirement. Although the bounds can be changed at runtime, the number of dimensions cannot be changed within a ReDim statement, A Select statement would be need to select from a long list of pre-prepared ReDim statements with one for each possible number of dimensions.
The alternative is ragged or jagged arrays although without them being ragged.
Consider:
Dim F As Variant
ReDim F(0 To 2)
F(0) = VBA.Array(1, 2, 3)
F(1) = VBA.Array(4, 5, 6)
F(2) = VBA.Array(7, 8, 9)
I have made F into a three element array and have then made each element of F into an array. To access the elements of the inner arrays, I write: F(n)(m) where both n and m can be 0, 1 or 2.
I can continue:
F(0)(0) = VBA.Array(10, 11, 12)
After this change, element F(0)(0)(0) has a value of 10 and F(0)(0)(1) has a value of 11.
I can continue this indefinitely. I have read that VBA has a limit of 60 dimensions with conventional multi-dimensional arrays. I have not tried but I cannot see why there would be any limit on the number of dimensions with this technique other than memory.
This technique appears to have the same limitation as regular multi-dimensional arrays. I can write F(0)(0) or F(0)(0)(0) but I cannot change the depth of the simple variable at runtime.
There is also the problem that ReDim F(0)(0 To 2) is rejected by the compiler as invalid syntax. That was why I used VBA.Array to convert F(0) to an array.
The solution is recursion. Consider:
Call ReDimVar(F, "1 To 2", "3 To 4", "0 To 5")
ReDimVar can:
ReDim F(1 To 2)
Call ReDimVar(F(1), "3 To 4", "0 To 5")
Call ReDimVar(F(2), "3 To 4", "0 To 5")
All this can be handled with simple loops. I rejected this technique because recursion is slow and your question implies significant volumes of data and many dimensions. However, to demonstrate that it would work, play with the following:
Sub TryMDVA()
' Demonstrate how to:
' 1) Convert a Variant into a multi-dimension array
' 2) Store values in every element of that multi-dimension array
' 3) Extract values from every element of that multi-dimension array
Dim Coords() As Long
Dim ElementValue As String
Dim InxB As Long ' Index for both Bounds and Coords
Dim InxD1 As Long
Dim InxD2 As Long
Dim InxD3 As Long
Dim LwrBnds As Variant
Dim MDVA As Variant
Dim UppBnds As Variant
LwrBnds = Array(1, 0, -3)
UppBnds = Array(2, 5, 4)
ReDim Bounds(LBound(LwrBnds) To UBound(LwrBnds))
ReDim Coords(LBound(LwrBnds) To UBound(LwrBnds))
Call FormatMDVA(MDVA, LwrBnds, UppBnds)
Debug.Print "Results of formatting MDVA"
Debug.Print "Bounds of MDVA are " & LBound(MDVA) & " to " & UBound(MDVA)
Debug.Print "Bounds of MDVA(1) are " & LBound(MDVA(1)) & " to " & UBound(MDVA(1))
Debug.Print "Bounds of MDVA(2) are " & LBound(MDVA(2)) & " to " & UBound(MDVA(2))
Debug.Print "Bounds or MDVA(1)(0) are " & LBound(MDVA(1)(0)) & " to " & UBound(MDVA(1)(0))
Debug.Print "Bounds or MDVA(2)(5) are " & LBound(MDVA(2)(5)) & " to " & UBound(MDVA(2)(5))
' Initialise Coords to lower bound of each dimension
For InxB = LBound(LwrBnds) To UBound(LwrBnds)
Coords(InxB) = LwrBnds(InxB)
Next
Do While True
' Build element value from coordinates
ElementValue = Coords(LBound(Coords))
For InxB = LBound(LwrBnds) + 1 To UBound(LwrBnds)
ElementValue = ElementValue & "." & Coords(InxB)
Next
' Store element value in element of MDVA specified by Coords
Call PutElement(MDVA, Coords, ElementValue)
' Step Coords. Think of Coords as a speedometer with each wheel marked
' with the available index values for a dimension. Starting on the right,
' check each wheel against the relevant ubound. If it is less than the
' ubound, step it by 1. If it is the upper bound, reset it to the lower
' bound and try the next wheel to the left. If the leftmost wheel is
' to be reset, Coords has been set to all possible values.
For InxB = UBound(LwrBnds) To LBound(LwrBnds) Step -1
If Coords(InxB) < UppBnds(InxB) Then
Coords(InxB) = Coords(InxB) + 1
Exit For
Else
If InxB = LBound(LwrBnds) Then
Exit Do
End If
Coords(InxB) = LwrBnds(InxB)
End If
Next
Loop
Debug.Print "Example values from within MDVA"
Debug.Print "MDVA(1)(0)(-3) = " & MDVA(1)(0)(-3)
Debug.Print "MDVA(1)(0)(-2) = " & MDVA(1)(0)(-2)
Debug.Print "MDVA(2)(3)(0) = " & MDVA(2)(3)(0)
Debug.Print "MDVA(2)(5)(4) = " & MDVA(2)(5)(4)
' Initialise Coords to upper bound of each dimension
For InxB = LBound(UppBnds) To UBound(UppBnds)
Coords(InxB) = UppBnds(InxB)
Next
Debug.Print "List of all values in MDVA"
Do While True
' Output value of element of MDVA identified by Coords
Debug.Print "MDVA(" & Coords(LBound(UppBnds));
For InxB = LBound(UppBnds) + 1 To UBound(UppBnds)
Debug.Print ", " & Coords(InxB);
Next
Debug.Print ") = """ & GetElement(MDVA, Coords) & """"
' Set next value of Coords. Similar to code block in PutElement
' but in the opposite direction
For InxB = UBound(LwrBnds) To LBound(LwrBnds) Step -1
If Coords(InxB) > LwrBnds(InxB) Then
Coords(InxB) = Coords(InxB) - 1
Exit For
Else
If InxB = LBound(LwrBnds) Then
Exit Do
End If
Coords(InxB) = UppBnds(InxB)
End If
Next
Loop
End Sub
Sub FormatMDVA(ByRef MDVA As Variant, LwrBnds As Variant, UppBnds As Variant)
' Size MDVA according to the bounds in the first elements of LwrBnds and
' UppBnds. If there are further elements in LwrBnds and UppBnds, call
' FormatMDVA to format every element of MDVA according to the remaining
' elements.
Dim InxB As Long
Dim InxM As Long
Dim LB As Long
Dim SubLwrBnds As Variant
Dim SubUppBnds As Variant
LB = LBound(LwrBnds)
ReDim MDVA(LwrBnds(LB) To UppBnds(LB))
If LBound(LwrBnds) = UBound(LwrBnds) Then
' All bounds applied
Else
' Another dimension to format
ReDim SubLwrBnds(LB + 1 To UBound(LwrBnds))
ReDim SubUppBnds(LB + 1 To UBound(UppBnds))
' Copy remaining bounds to new arrays
For InxB = LB + 1 To UBound(LwrBnds)
SubLwrBnds(InxB) = LwrBnds(InxB)
SubUppBnds(InxB) = UppBnds(InxB)
Next
For InxM = LwrBnds(LB) To UppBnds(LB)
Call FormatMDVA(MDVA(InxM), SubLwrBnds, SubUppBnds)
Next
End If
End Sub
Function GetElement(ByRef MDVA As Variant, ByRef Coords() As Long) As Variant
' Return the value of the element of MDVA identified by Coords
Dim InxC As Long
Dim LB As Long
Dim SubCoords() As Long
LB = LBound(Coords)
If LB = UBound(Coords) Then
' Have reached innermost array
GetElement = MDVA(Coords(LB))
Else
' At least one more nested array
ReDim SubCoords(LB + 1 To UBound(Coords))
For InxC = LB + 1 To UBound(Coords)
SubCoords(InxC) = Coords(InxC)
Next
GetElement = GetElement(MDVA(Coords(LB)), SubCoords)
End If
End Function
Sub PutElement(ByRef MDVA As Variant, ByRef Coords() As Long, _
ElementValue As Variant)
' Save the value of ElementValue in the element of MDVA identified by Coords
Dim InxC As Long
Dim LB As Long
Dim SubCoords() As Long
LB = LBound(Coords)
If LB = UBound(Coords) Then
' Have reached innermost array
MDVA(Coords(LB)) = ElementValue
Else
' At least one more nested array
ReDim SubCoords(LB + 1 To UBound(Coords))
For InxC = LB + 1 To UBound(Coords)
SubCoords(InxC) = Coords(InxC)
Next
Call PutElement(MDVA(Coords(LB)), SubCoords, ElementValue)
End If
End Sub
