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

Alan,

Ofcourse they are not technically identical, though as demonstrated they
may appear to be practically identical)

the way you type depends on performance vs flexibility.
performance loss can be VERY marginal.


dim x() creates a SAFEARRAY memory structure
dim x creates a VARIANT (which can SUBSEQUENTLY be pointed to a
safearray)

in the call of SafeArrayCreate the VARTYPE of the array must be set,
although it's boundaries can be assigned later with a redim.

Sub foo()
Dim x(), y

ReDim x(3)
y = x
Application.VBE.Windows("Locals").Visible = True
'x's type is Variant(0 to 3)
'y's type is Variant/Variant(0 to 3)
Stop
ReDim y(3) As Long
Stop
End Sub

HTH



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Alan Beban wrote in message
:

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

Thanks a lot for this post. I learned a few things from it :)

(Always listening!)

--
-Mike Mertes
Airtron, Tampa Bay

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

The only thing I see your test illustrates is your declaration causes the
parser to apply different rules in interpreting how it tries to interpret
and implement your commands. It also seems to reinforce my basic point
that the use of

Dim myArray()
myArray() = somearray

is a waste of time and could be counterproductive.

As you said:

We seem to keep going around the barn on this.

--
Regards,
Tom Ogilvy

Tom Ogilvy wrote:
. . .
The only thing I see your test illustrates is your declaration causes the
parser to apply different rules in interpreting how it tries to interpret
and implement your commands.

No; along with illustrating that you can change the lower bound of a
one-dimensional array with ReDim Preserve, it also illustrates that your
assertion that after

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

MyArray is a Variant variable containing an array is erroneous.

It also seems to reinforce my basic point
that the use of

Dim myArray()
myArray() = somearray

is a waste of time and could be counterproductive.

This is a point that arose out of the discussion and was never disputed.
Your basic point was "you can't pick up a worksheet range except using a
variant variable". When I replied that that was inaccurate because you
can pick up a worksheet range with a Variant() array a la

Dim MyArray()
MyArray = Range("A1:B10")

you claimed that MyArray in that case was really not a Variant() array
but was a Variant variable containing an array, and that your statement,
therefore, was not inaccurate. That was wrong; and that, among other
things, is what my test illustrates.

Alan Beban

If you wish to believe I am wrong, continue on.

--
Regards,
Tom Ogilvy


"Alan Beban" wrote in message
...
Tom Ogilvy wrote:
. . .
The only thing I see your test illustrates is your declaration causes
the
parser to apply different rules in interpreting how it tries to
interpret
and implement your commands.

No; along with illustrating that you can change the lower bound of a
one-dimensional array with ReDim Preserve, it also illustrates that your
assertion that after

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

MyArray is a Variant variable containing an array is erroneous.

It also seems to reinforce my basic point
that the use of

Dim myArray()
myArray() = somearray

is a waste of time and could be counterproductive.

This is a point that arose out of the discussion and was never disputed.
Your basic point was "you can't pick up a worksheet range except using a
variant variable". When I replied that that was inaccurate because you
can pick up a worksheet range with a Variant() array a la

Dim MyArray()
MyArray = Range("A1:B10")

you claimed that MyArray in that case was really not a Variant() array
but was a Variant variable containing an array, and that your statement,
therefore, was not inaccurate. That was wrong; and that, among other
things, is what my test illustrates.

Alan Beban

Tom Ogilvy wrote:
. . .
The only thing I see your test . . . seems to reinforce my basic point
that the use of

Dim myArray()
myArray() = somearray

is a waste of time and could be counterproductive.


Particularly with respect to the broad claim

"I have done some studying on this issue and I find that in vb version 6,

Dim MyArray1()

isn't an array of variants."

users should be aware that if the array is to be loaded with looping,
The Variant() array [Dim MyArray()] appears (from limited testing) to
load faster than the array contained within a Variant variable [Dim
MyArray].

Alan Beban

Particularly with respect to the broad claim

"I have done some studying on this issue and I find that in vb version 6,

Dim MyArray1()

isn't an array of variants."

users should be aware that if the array is to be loaded with looping,
The Variant() array [Dim MyArray()] appears (from limited testing) to
load faster than the array contained within a Variant variable [Dim
MyArray].

Alan Beban

Alan,

I appreciate you originally qualified with "This post relates to xl2000",
and in latest you refer to loading by looping. But if it's expected to cater
for Excel 97 and loading by "dumping", Dim MyArray1() fails for me.

Sub test()
Dim p, q()
'all work in xl2000
p = Range("A1:B10") 'ok in xl97
q = Range("A1:B10") 'xl97 error Can't assign to an array
q() = Range("A1:B10") 'xl 97 ditto error
End Sub

Not sure if Tom Ogilvy had something like this in mind when he mentioned
"could be counterproductive".

Don't misconstrue, I've learnt a lot about arrays from your posts!

Regards,
Peter

Peter T wrote:
Particularly with respect to the broad claim

"I have done some studying on this issue and I find that in vb version 6,

Dim MyArray1()

isn't an array of variants."

users should be aware that if the array is to be loaded with looping,
The Variant() array [Dim MyArray()] appears (from limited testing) to
load faster than the array contained within a Variant variable [Dim
MyArray].

Alan Beban


Alan,

I appreciate you originally qualified with "This post relates to xl2000",
and in latest you refer to loading by looping. But if it's expected to cater
for Excel 97 and loading by "dumping", Dim MyArray1() fails for me.

As it does for everyone; that was acknowledged in the original thread
two years ago. The issue arose from Microsoft's change in xl2000, which
allowed direct assignment of a worksheet range to MyArray after Dim
MyArray().

I believe that that indeed was what Tom Ogilvy referred to as
counterproductive with respect to the use of Dim MyArray().

Thanks for your interest and input.

Alan Beban

Alan..



defining a VARIANT has obvious advantages over defining an ARRAY.
unless you prove to me that is has SIGNIFICANT performance
implications..

let's get into the "underlying structure", and the research...

you claim that in VB6 your variables are identical.
below I'll PROVE there are not.

Private Const VT_BYREF = &H4000&
Private Declare Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (dest As Any, source As Any, _
ByVal bytes As Long)

Sub ArrayTest()
Dim arr(), var
var = Range("a1:b20") 'Can be redimmed with preserve
'AND can be used in xl97

arr = Range("a1:b20") 'Cannot be redimmed with preserve
'and wont work in xl97

DumpVtype var
DumpVtype arr

End Sub

Sub DumpVtype(ByRef vArg As Variant)
Dim vType As Integer, v As Variant
If Not IsArray(vArg) Then Exit Sub
CopyMemory vType, vArg, 2
'The ByRef flag in the VARTYPE of the VARIANTARG
'indicates if vARG is a Pointer to an ARRAY or a pointer to a POINTER
Debug.Print vType, (vType And VT_BYREF) 0
End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Alan Beban wrote :

Tom Ogilvy wrote:
. . .
The only thing I see your test . . . seems to reinforce my basic
point that the use of

Dim myArray()
myArray() = somearray

is a waste of time and could be counterproductive.


Particularly with respect to the broad claim

"I have done some studying on this issue and I find that in vb
version 6,

Dim MyArray1()

isn't an array of variants."

users should be aware that if the array is to be loaded with looping,
The Variant() array [Dim MyArray()] appears (from limited testing) to
load faster than the array contained within a Variant variable [Dim
MyArray].

Alan Beban

I believe Alan's point is that they are different (it was my point that they
are the same). Back in the original thread, I stated to a poster that
worksheet range could only be picked up by using a variant. Alan argued
that that was not true and it could be picked up by a variant array. I said
the storage structure was the same - that the top level was a variant based
on what I had read in Curand's book. As I recall, I stated that, in my
opinion, the structure of the array storage was the same in VBA5 and VBA6
and the change was that the processing rules allowed/incorporated processing
logic to hangle the assignment of an array to a "variant" declared as
Dim A()

I still believe that at the top of the storage structure, the variable is a
variant. The difference you seem to point out is whether the variant points
to an array or points to a pointer/variable that points to an array. So my
point was that my original statement, that a variant is required to pick up
range, was not incorrect. However, I also felt that the A() above was
redefined as a variant when the range was assigned, but that apparently is
not the case and that assumption was incorrect on my part. However, it
still seems to me to be a waste of time (and as has been show, to be
counterproductive) to declare it as
A()
if it is to be used to pick up a range.

Correct me if I have misinterpreted what you are saying.

--
Regards,
Tom Ogilvy



"keepITcool" wrote in message
ft.com...
Alan..



defining a VARIANT has obvious advantages over defining an ARRAY.
unless you prove to me that is has SIGNIFICANT performance
implications..

let's get into the "underlying structure", and the research...

you claim that in VB6 your variables are identical.
below I'll PROVE there are not.

Private Const VT_BYREF = &H4000&
Private Declare Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (dest As Any, source As Any, _
ByVal bytes As Long)

Sub ArrayTest()
Dim arr(), var
var = Range("a1:b20") 'Can be redimmed with preserve
'AND can be used in xl97

arr = Range("a1:b20") 'Cannot be redimmed with preserve
'and wont work in xl97

DumpVtype var
DumpVtype arr

End Sub

Sub DumpVtype(ByRef vArg As Variant)
Dim vType As Integer, v As Variant
If Not IsArray(vArg) Then Exit Sub
CopyMemory vType, vArg, 2
'The ByRef flag in the VARTYPE of the VARIANTARG
'indicates if vARG is a Pointer to an ARRAY or a pointer to a POINTER
Debug.Print vType, (vType And VT_BYREF) 0
End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Alan Beban wrote :

Tom Ogilvy wrote:
. . .
The only thing I see your test . . . seems to reinforce my basic
point that the use of

Dim myArray()
myArray() = somearray

is a waste of time and could be counterproductive.


Particularly with respect to the broad claim

"I have done some studying on this issue and I find that in vb
version 6,

Dim MyArray1()

isn't an array of variants."

users should be aware that if the array is to be loaded with looping,
The Variant() array [Dim MyArray()] appears (from limited testing) to
load faster than the array contained within a Variant variable [Dim
MyArray].

Alan Beban

keepITcool wrote:
Alan..



defining a VARIANT has obvious advantages over defining an ARRAY.
unless you prove to me that is has SIGNIFICANT performance
implications..

A bit too paternalistic for my taste.

Whether there are advantages depends on the user's application. For
example, if the user is the only person using the application, and uses
it only in his xl2000 version, it is no advantage for him that Dim arr
works also in xl97.

And whether there are "SIGNIFICANT performance implications" is also for
the user, not you or me, to decide. All I'm stating is that I think,
after minimal testing, that looping to load an array is faster if the
array is a Variant() array than if it is an array contained in a Variant
variable. How much faster in a particular application, and whether the
difference is "significant", are matters I'm prepared to leave to the user.

Alan Beban

keepITcool wrote:
. . .
Sub ArrayTest()
Dim arr(), var
var = Range("a1:b20") 'Can be redimmed with preserve
'AND can be used in xl97

arr = Range("a1:b20") 'Cannot be redimmed with preserve
'and wont work in xl97


???

Clearly you meant something different in your comment about arr;
ReDim Preserve arr(1 to 20, 1 to 4) works fine.

Alan Beban