This post relates to xl2000, though I believe it’s relevant for later
versions as well.

A couple of years ago in this forum it was argued that after

Dim MyArray()
MyArray = Array(1,2,3)

MyArray is not a Variant() array but is a Variant variable containing an
array, just as though the snippet were

Dim MyArray
MyArray = Array(1,2,3)

The results of the thread containing the argument were not definitive;
no really convincing demonstration was put forth either way. But in
recently solving what appeared to be an unrelated matter I have had
occasion to focus on a couple of items that resolve the question more
satisfactorily. It’s not the Unified Field Theory, but hey, it’s still
nice to tidy up a loose end. First some background.

The On-line help for the ReDim Statement asserts
<<The ReDim statement is used to size or resize a dynamic array that has
already been formally declared using a Private, Public, or Dim statement
with empty parentheses (without dimension subscripts). . . . If you use
the Preserve keyword, you can resize only the last array dimension . . .
.. Similarly, when you use Preserve, you can change the size of the array
only by changing the upper bound; changing the lower bound causes an
error. . . . You can use the ReDim statement repeatedly to change the
number of elements and dimensions in an array. [Ed. Note: the following
is a key feature] However, you can't declare an array of one data type
and later use ReDim to change the array to another data type, UNLESS THE
ARRAY IS CONTAINED IN A VARIANT [Emphasis added]. If the array is
contained in a Variant, the type of the elements can be changed using an
As type clause . . .

Recently, keepitcool, a contributor to this forum, pointed out to me the
additional possibility, not referred to in the On-line help, of changing
the lower bound of a one-dimensional array with ReDim Preserve (it in
fact works on the last dimension of a multi-dimensional array as well).
He didn’t focus on the distinction between a Variant() array and an
array contained within a Variant variable, but I discovered that the
technique, like that of changing the array’s data type, works only on
the latter and not on a Variant() array. One handy thing the technique
enables is changing a loaded one-dimensional dynamic array from 0-based
to 1-based or vice versa with the ReDim Preserve construct rather than
looping.

So two separate features are described above that distinguish between
Variant() arrays and Variant variables that contain arrays: The ability
to redimension a dynamic array contained within a Variant variable so as
to change the data type of its elements (which is documented), and the
ability to use ReDim Preserve to change the lower bound of the last
dimension of a dynamic array contained in a Variant variable (which is
not documented, but which is readily demonstrable).

The argument a couple of years ago that Dim MyArray1() led to a Variant
variable containing an array was basically:

<<<I have done some studying on this issue and I find that in vb version 6,
Dim MyArray1()
isn't an array of variants. It is a variant at the top level and it
contains an array (last 8 bytes point to an array structure) - same
structure you get by assigning an array to a variant or picking up a
range from a worksheet - it is using a variant at the top level. So
when an array is assigned or a range is picked up and you use
Dim MyArray1(), MyArray2()
MyArray1() = Range("A1:B10")
MyArray2() = MyArray1()

This is technically identical to
Dim MyArray1 as Variant, MyArray2 as Variant
myArray1 = Range("A1:B10")
myArray2 = MyArray1

in the first case the top level variant gets a new array assigned
(pointer assignment) and in the latter case, it gets an initial array
assigned (pointer assignment). Their is no advantage (or difference) in
using the first construct . . . . You really need to examine the
underlying structure of the storage and not rely on empirical testing.
VB/VBA does way too much work behind the scenes to make this reliable
in many cases - this case in particular.

I must say that I never did appreciate the thrust of the argument and
its reference to a “variant at the top level”, “the last 8 bytes
pointing to an array structure”, “pointer assignments”, and “the
underlying structure of the storage”; that’s all somewhat beyond me. But
be that as it may, because of the two distinguishing features described
above, you can run successfully:

Dim MyArray1 As Variant, MyArray2 As Variant
MyArray1 = Range("A1:B10")
MyArray2 = MyArray1
ReDim Preserve MyArray2(1 To 10, 0 To 1)
Debug.Print LBound(MyArray2, 2); UBound(MyArray2, 2) ‘<--Prints 0 1
ReDim MyArray2(1 To 10, 1 To 2) As Integer
Debug.Print TypeName(MyArray2) ‘<--Prints Integer()

Whereas if you try to run

Dim MyArray1(), MyArray2()
MyArray1() = Range("A1:B10")
MyArray2() = MyArray1()
ReDim Preserve MyArray2(1 To 10, 0 To 1)
Debug.Print LBound(MyArray2, 2); UBound(MyArray2, 2)
ReDim MyArray2(1 To 10, 1 To 2) As Integer
Debug.Print TypeName(MyArray2)

you will get a Compile error at the ReDim . . . As Integer line-Can’t
change data types of array elements; and if you comment out that line
you will get a Runtime error at the ReDim Preserve line-Subscript out of
range.

So however nimble the “underlying structure of the storage” analysis may
be, from the errors thrown above it seems abundantly clear that the two
constructs are not technically identical and that MyArray1() and
MyArray2() are true Variant() arrays and not Variant variables
containing arrays (if they were the latter, then, as demonstrated above,
there would be no such errors).

Alan Beban