Metmorphisis of a Variable Into an Array

[Cyberdude]

I saw in a previous post the following code:

Dim AC
AC = Split(Range("C12:C16").Cells(1).Address(, False), "$")

and the writer (sorry, I forgot who wrote it) said he/she used it to isolate the column in an address. I think this is super slick code, and I basically understand what is happening. What I don't understand is why "AC" didn't have to be dimensioned as an array. In the eventual context of its use, I can see that the compiler might deduce from the presence of the SPLIT function that AC (which is a variant) should be used as an array variable. But If I write:
Msgbox AC(0)
preceding the "SPLIT" statement, then I get a "Type mismatch" error. It would never have occurred to me to dimension AC without writing it as "AC()". Is it true that a variant can suddenly be treated as an array without any forewarning?

[BlueCactus]

Yep, and a very useful feature it is too. Gets you out of all sorts of holes. I think the official terminology is a 'variant that contains an array'.

For example,

Sub Test()
 Dim myVar as Variant
 myVar = FillMyVar()
End Sub

Function FillMyVar() as Variant
 FillMyVar = Array(1,2,3,4)
End Function

actually works, which AFAIK can't be done with a normal array.

The useful function

IsArray(myVar)

will return true/false to keep you informed.

For example, if you use GetOpenFilename(), the result may or may not be a variant containing an array.

It's also how you move cells into/out of arrays in blocks:

Dim mySheetData as Variant
mySheetData = Sheets(1).Cells(1,1).Resize(100,100).Value
Sheets(2).Cells(8,10).Resize(Ubound(mySheetData,1),UBound(mySheetData,2)).Value = mySheetData

<excuse the VBA tag hiccup>

Note that you've probably seen some of this stuff before without realizing what it implied.

[brettdj]

Variant Arrays are a godsend that are unfortunately used less than they should be. With a variant array the time overhead is nowhere near what you would expect with the usual suspect, the For Next loop working on a range.

The code below dimensions a variant array by setting it to a range, it then loops through all the records of the array and fills it with different data, at the end of the code the variant array contents are dumped to the same range.

The code took 0.25 seconds on my pc to fill the 20,000 cells.

Sub TestTime()
Dim X As Variant, i As Long
Dim StartTime As Double
StartTime = Timer()
'set the size of the variant array
X = Range("A1:A20000")
For i = 1 To UBound(X, 1)
X(i, 1) = "I am number " & i
Next
'dump output
Range("A1:A20000") = X
MsgBox "I took " & Timer() - StartTime & " seconds"
End Sub

Cheers

Dave

[Bob Phillips]

What I don't understand is why "AC" didn't have to be dimensioned as an array.

It doesn't have to be dimensioned as an array because the Splt effectively function does that. If you take a look at the Split emulator that I showed for Excel 97, you will see what the built-in Split function is doing (although I make no claims that mine is as efficient ), which includes dimensioning the array.

But If I write:
Msgbox AC(0)
preceding the "SPLIT" statement, then I get a "Type mismatch" error.

That is because at that point, AC is an Variant/Empty, it has no value, it has no dimension, is is effectively nothing more than a memory placeholder.

Is it true that a variant can suddenly be treated as an array without any forewarning?

A variant can be anything, that is the usefulness of same. If you don't know what you are going to store in a variable, type it variant. For instance try this

Dim myVar 
myVar = "This is a string variable"
MsgBox TypeName(myVar) & ", " & IsArray(myVar)
 
myVar = 17
MsgBox TypeName(myVar) & ", " & IsArray(myVar)
 
myVar = Split("a,b,c", ",")
MsgBox TypeName(myVar) & ", " & IsArray(myVar)

[Cyberdude]

Thanx to all ... VERY enlightening. I use variants now and then, but the engineer in me says that it's a very inefficient way to code. I'm having second thoughts about that now. I tend to forget that I "have an Intel inside" that can handle a lot of inefficiency, and RAM is cheap these days. And the array feature is especially attractive. I'm surprised that I haven't run into the SPLIT function before now. That has some nifty possibilities as well. Thanx again!

[Bob Phillips]

... the engineer in me says that it's a very inefficient way to code. I'm having second thoughts about that now.

Believe the engineer in you, it is correct. Variants are inefficient, and should be used appropriately as with all things. Don't confuse convenience of coding with efficiency.

I tend to forget that I "have an Intel inside" that can handle a lot of inefficiency, and RAM is cheap these days.

Not meaning to be rude, but that is sloppy thinking. Think efficiently as best you can at all times, otherwise those inefficiencies will mount up and bite you big one time, and you won't know where to start. If you seek to be as efficient as possible, and keep abreast of other thoughts on efficiency, you are less likley to get bottleneck, and will be better prepared when you do.

[Cyberdude]

I hear ya, XLD. Thanx for the needed words of wisdom.

[brettdj]

Cyberdude,

Its good practice to force variable declaration via
goto the VBE
Tools - Options
pick Editor and check "Require Variable Declaration"

This adds Option Explict to your code which forces the dimension. Dimensioning is more efficient, with the added plus that it also eliminates errors caused by mis yping variable names late on. If you do mistype a variable name, it will have no Dimension, so the code will register a compile error which flags a flaw in the code.

You can still use variants as needed, such as the Variant Array, but you have to explicitly DIM them, as I did to the variable X above.

Cheers

Dave

[Cyberdude]

I'm not sure what triggered you to advise me about Option Explicit, but I set that up long ago. And I agree with you.
Thanks for the advice.

[brettdj]

Good to hear

I read your and xlds comment about variants and thought the automated Option Explicit might be worth mentioning. But you were already on that page

[BlueCactus]

The one catch with Option Explicit (which is obvious if you think about it) is that Redim will not flag a mistyped variable name. There have been a couple of times when I've changed a variable name in a Redim statement and forgotten to go back and change the original Dim to match.

[Bob Phillips]

[QUOTE=BlueCactus]The one catch with Option Explicit (which is obvious if you think about it) is that Redim will not flag a mistyped variable name.[QUOTE]

For anyone that may be wondering, as the VBA help says

... Caution The ReDim statement acts as a declarative statement if the variable it declares doesn't exist at module level or procedure level. If another variable with the same name is created later, even in a wider scope, ReDim will refer to the later variable and won't necessarily cause a compilation error, even if Option Explicit is in effect. To avoid such conflicts, ReDim should not be used as a declarative statement, but simply for redimensioning arrays. ...


which is presumably what you meant when you said that it was obvious when you think about it