Clarification on Array Type

[Cyberdude]

When I use the statement:

Dim Ary As Variant

then I have created a variant variable named "Ary".

If I then use a statement like:

Ary = Split(ActiveCell.Address,":")

the variable "Ary" becames a variant dynamic array.
Am I correct so far?

Then at the end of the macro I should use the statement

Erase Ary

to release the memory used by the dynamic array. Is that correct?

In the "Dim" statement, if I had written it as:

Dim Ary() As Variant

is all of the stuff above still true, except that "Ary" immediately becomes a dynamic array?

[Bob Phillips]

When I use the statement:
Dim Ary As Variant
then I have created a variant variable named "Ary".

Yes, at this point you have an empty variant variable

If I then use a statement like:
Ary = Split(ActiveCell.Address,":")
the variable "Ary" becames a variant dynamic array.
Am I correct so far?

So far! It now becomes a variant variable, sub-type string.

BTW, I like splitting a single element value

Then at the end of the macro I should use the statement
Erase Ary
to release the memory used by the dynamic array. Is that correct?

Yes and it is still a string sub-type.

In the "Dim" statement, if I had written it as:
Dim Ary() As Variant
is all of the stuff above still true, except that "Ary" immediately becomes a dynamic array?

No!

It is a dynamic array, but by doing that you have sub-typed it. Split wants to create a variant/string(0 to n) , and can't.

Try, you get a type mis-match.

Interestingly, these both work

Dim ary As Variant
ary = "a"
ary = Split(ActiveCell.Address, "$")
Erase ary

and

Dim ary As Variant
ary = 1
ary = Split(ActiveCell.Address, "$")
Erase ary

[Cyberdude]

Hey, xld, thanx for the confirmation (imprimatur??).

Your avatar has me concerned. When I glance at it I usually see what seems to be a man playing a saxaphone.
After that I see the lady. I wonder what that says about me??

[Bob Phillips]

After that I see the lady. I wonder what that says about me??

It says that you are properly observant, even though you have your priorities wrong . Me, I always see the lady first.

[BlueCactus]

In the "Dim" statement, if I had written it as:

Dim Ary() As Variant

is all of the stuff above still true, except that "Ary" immediately becomes a dynamic array?

This is a subtle point in VBA (which can drive you nuts if you don't know it).

Dim Ary() as Variant immediately creates a dynamic array of type Variant.

Dim Ary as Variant just creates a variable of type Variant. It can BE anything, and it can CONTAIN anything. When you then use:

Ary = Split(ActiveCell.Address,":")

variable Ary is set to CONTAIN an array, but Ary is not actually an array itself! Yet you can access the elements of the array contained in Ary as if Ary is actually the array. Confused yet? BTW, a variant variable containing an array is the only array that can be returned as the result of a function, so it is extremely useful.

The short of it is this: If you want to put an array in a variant, or set a variant to an array, your code will probably work fine if you Dim the variant without parantheses.

[Cyberdude]

Hey, BC, thanx. Good info for my personal KB.