Arrays in Arrays
The Variant data type, while inefficient, is necessary in many situations. You can create a Variant Array using the Array function like this
Dim vaTest As Variant
vaTest = Array(1, 2, 3)
You can also make the elements of your array arrays themselves, nested arrays if you will.
Dim vaTest As Variant
vaTest = Array(Array(1, 2, 3), Array(4, 5, 6))
Debug.Print vaTest(0)(1)
This bit of code will produce 2 in the Immediate Window. The syntax for accessing the nested array reads “Get the second element of the array that is the first element of vaTest. vaTest(0) accesses the first element of vaTest which is an array and (1) accesses the second element of that array.
Christopher:
>The Variant data type, while inefficient,
I’ve always wondered how inefficient?
For most of the purposes for which I code, the machine will have ample memory. There are usually around 20 variables, or which 2 or 3 will be relatively large arrays (with rows of order in the thousands, and maybe 20 columns).
Almost all my arrays will be variants, whilst scalars are usually defined as a type.
cheers,
29 November 2004, 5:09 pmChristopher the curious
Hugh Lerwill:
Anything beyond Dim MyArray(i,j,k) is big for me, this is a nightmare
Hughie
29 November 2004, 6:20 pmJon Peltier:
Christopher -
I’m with you. I type my variables as best I can, but I never shy away from variants. They’re the only way to move a large array of data from the worksheet to VBA, and sometimes you need to allow for more possibilities than a single data type.
I’ve heard countless times about how many bytes each data type requires, blah blah. Don’t we all have late generation pentiums or athlons? The smallest hard drive in my house is 20GB. The oldest version of Office is 97. Most are running Windows XP.
I’m sure there’s less inefficiency in variants tahn in other coding habits I don’t even realize I have.
- Jon
29 November 2004, 8:00 pmDick:
Christopher: Inefficiency is pretty relative. If you have a userform that takes five seconds to load, every cycle that you save counts. For smaller apps, you probably don’t notice.
I don’t know the inner workings of variant arrays, but I do know they’re sub-typed. I guess that means that the compiler doesn’t have to figure out their type every time they’re accessed, but it does have to read the subtype and that’s at least one extra step.
If it works for you, I see no need to change it. I try to code as efficiently as possible, even for trivial applications just to develop the habit. To be sure, I get lazy sometimes.
29 November 2004, 8:02 pmross:
This is a top site, half way down this page is a sheet about data types, i don’t think it looks at the whole arry issues, i’m not sure, anyway take a look, i’ve got to get on (fixing a workbook that has…errm “got ill” ;((((, PITA!)
http://www.webace.com.au/~balson/InsaneExcel/Other.htm
30 November 2004, 2:38 am