In various programming languages, including VBA, arrays inhabit a twilight zone between the basic types and the classes.
For example, there is usually a syntax for literal arrays. In VBA this is the function Array(), which returns an array of Variant (since the elements could be of mixed types):
TypeName(Array(1,2,3)) –> Variant()
However, you have to pass this round as a Variant:
Sub TestLiteral() Dim i As Integer i = GetNth(2, Array(1, 2, 3, 4, 5)) MsgBox i End Sub Function GetNth(n As Integer, arr As Variant) As Variant GetNth = arr(n - 1) '-1 Because Base 0 End Function
Writing ‘arr() As Variant’ in the function header throws a compiler error.
Here’s another curious feature. Suppose we have a 2-D array: this is effectively an array of 1-D arrays. However, you can’t assign a 1-D array to one of the elements of the 2-D array: compiler error again. Here’s some code:
Sub Test2() Dim arr2d(1 To 2, 1 To 3) As Integer Dim arr1d(1 To 3) As Integer Dim i As Integer For i = 1 To 3 'stuff some values into arr1d arr1d(i) = i * 2 Next ' arr2d(1) = arr1d 'throws a compiler error End Sub
Here’s the error:
However, the left-hand and right-hand sides of the assignment have exactly the same type, Integer(1 To 3), as can be seen in the Locals window:
This type of assignment works only for dynamic arrays of the same type. The two arrays must both be dynamic arrays, and they must be declared as the exact same type
So I suppose we must conclude that the type of the second level of a 2-D array is not considered to be the same type as an apparently equivalent 1-D array, even though this seems both reasonable and useful. Anyone with knowledge of the language implementation, please comment. Time for a cup of tea, methinks.