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.
>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,
Christopher the curious
Anything beyond Dim MyArray(i,j,k) is big for me, this is a nightmare
Hughie
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
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.
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
Say I have vaTest = Array(Array(Banana, Orange, Apple), Array(5, 3, 7))
I want to sort the quantity of the fruits and list out with thew name of the fruit against the quantity like this:
Orange – 3
Banana – 5
Apple – 7
How to sort? Pl. help.
My question is about Dim-ing the Array. I see you’ve created vaTest as Variant. I have 4 ranges called r1,r2,r3 and r4. I used ‘transpose’ to put them into 2D arrays(ie – ar1, ar2, ar3, ar4), and I now want to make an Array of those arrays. How do I create/define/set RngArray()() to consist of RngArray(ar1,ar2,ar3,ar4)?
Do I need to put it as RngArray = Array(Array(ar1), Array(ar2), Array(ar3), Array(ar4))? Do I need quotes?
Also, I’ve found elsewhere using Cells as a Collection within a Range to cycle/loop through and perform a test or filter on cell contents (either value or string), does anyone have a good resource/advice with regard to this? It’s an alternative, possibly simpler solution.
Thanks in advance!
Ross
Ross: You don’t need to put them in their own array variables, unless you need to do it for some other reason. Let’s say you have data in A1:D4 and you want each column to be a separate array inside a larger array.
Dim vaRangeArr As Variant
Dim i As Long
ReDim vaRangeArr(1 To 4)
For i = 1 To 4
vaRangeArr(i) = Sheet1.Range(“A1:A4”).Offset(0, i – 1).Value
Next i
For i = 1 To 4
Debug.Print vaRangeArr(i)(i, 1)
Next i
End Sub
First Redim vaRangeArr so it can hold four elements. Then put the Value of the range into each element. I use a loop because my ranges are all neatly arranged, but you could do it more explicitly like
vaRangeArray(2) = Sheet1.Range(“B1:B4”).Value
vaRangeArray(3) = Sheet1.Range(“C1:C4”)Value
vaRangeArray(4) = Sheet1.Range(“D1:D4”).Value
The next loop just prints one element from each array. Note that the higher level array is one dimension (vaRangeArr(i)), but the arrays inside it are two dimensions.
Hey Dick, thanks for the suggestion, I’m testing your method out and here’s my code. I’m getting an “Application or ‘Object’ definted error and I’m not sure what’s the root cause. I have tried a few permutations of properties and left you with my most recent (failed) attempt of .Cells.Value. Also does ReDim with “(1 To 4)” dimension an array 1×4 (or 4×1) with index values 0,1,2,3?
I’ve begun learning VBA (Excel 2000, VBE v6.3) this summer, diving in pretty deep, but you can you or anyone else tell me more about using a For Each Loop with the Cells (Collection)Object of the Range Object? M$ claims this here http://msdn.microsoft.com/en-us/library/aa221353(office.11).aspx but I couldn’t get it to work. Their ‘c’ variable/counter/object isn’t defined clearly…
______________________________________________________________
Dim r1, r2, r3, r4 As Range
Dim vaRngArray() As Variant
ReDim vaRngArray(1 To 4)
With ActiveSheet
For j = 0 To 3
Select Case j
Case j = 1
vaRngArray(j) = .Range(r1).Cells.Value
Case j = 2
vaRngArray(j) = .Range(r2).Value
Case j = 3
vaRngArray(j) = .Range(r3).Value
Case j = 4
vaRngArray(j) = .Range(r4).Value
End Select
Next j
__________________________________________________________________
Thanks! I really appreciate the help!
~Rawesome
Ross: Redim vaRngArray(1 to 4) creates a one-dimensional array with four empty slots in it. All you’re doing with Redim (or Dim) is allocating memory to the array, you’re not actually filling it with anything. The index values are 1, 2, 3, and 4. You could Redim vaRngArray(0 to 3) to create a one-dimensional array with four empty slots and indexes of 0, 1, 2, and 3. In your example, your For Each loop and your array indexes don’t match. You could change either one. I prefer starting at 1 for arrays, but others prefer 0.
For a best practice, you would code
and it wouldn’t matter what your indexes are. It would always go from the lower bound to the upper bound, whatever they are.
In your example, you Dim r1 through r4, but you don’t assign any ranges to them. If you want to use the r variables, it would look more like this
vaRngArray(1) = r1.Value
The error you’re getting is because the code doesn’t know what r1 refers to. If you tell me what r1 through r4 are supposed to refer to, I can show you the best way to code it.
It’s a shame that MS doesn’t Dim their variables. For Each should look like this
For Each rCell In Worksheets(“Sheet1”).Range(“A1:D10”).Cells
I use rCell as my variable name and they use c. You can use either (or any valid variable name), but I prefer to prefix my range object variables with r and to make the name descriptive. If I were looping through rows, I would use
For Each rRow in Worksheets(“Sheet1”).Range(“A1:D10”).Rows
Both rCell and rRow are Range objects, but in the For Each, they would refer to different things because I’m returning the Cells collection in the first and the Rows collection in the second. Cells and Rows are both Range objects, but return different subsets of A1:D10.
Hey Dick, thanks for all the helpful background info, I’ll be picking it to pieces as I usefully extract it into my program. I do have the ranges (r1,r2,r3,r4) defined dynamically (only the number of rows will change), I neglected to copy that code over before. And, I’m becoming a fan of the Hungarian notation now too.
However, I have given up on the Array of Arrays or trying to over reach my current understanding level of the Cell/Row as an Object of the Range Object (for an implicit array, if you will), and just looped through the cells in each range. It actually gives a nice effect when I turn ScreenUpdating on (=True) to let the user literally see the Macro work for them. Thankfully my ranges aren’t too ridiculously large.
Also, thanks for the LBound & UBound explanation, MS does an atrocious job at explaining Objects/Methods/Properties and their Examples are foolishly worse. So you help clarified that for me.
Onto the next task… till next time, thanks!
Ross
Excellent info, Dick.
But how do you output the entire array of arrays to a range, without looping? (supposedly I manipulated the data or loaded the array from some other source)
Typically, you’d go something like:
But this outputs nothing. However, I can output one line at a time like so:
Thanks in advance,
Victor
Victor: You can’t. Value can take one two-dimensional array, but not an array of arrays. If you had to write a bunch of array within arrays, it might be quicker to combine all the arrays into one, then right that one. It’s still looping, but only one cell-write call.
You could try something like
Dim vaTest As Variant
Dim vaTemp As Variant
Dim i As Long
vaTest = Array(Array(1, 2, 3), Array(4, 5, 6))
For i = LBound(vaTest) To UBound(vaTest)
vaTemp = Application.Index(vaTest, i – LBound(vaTest) + 1, 0)
Cells(1, i – LBound(vaTest) + 1).Resize(UBound(vaTemp) – LBound(vaTemp) + 1).Value = Application.Transpose(vaTemp)
Next i
although I wouldn’t bank on it being faster than Dick’s suggestion.
If you have an 1-D array of 1-D arrays (all the same non-zero length of course) that you need to get into a 2-D array, you can do it without looping. Excel will coerce it if you return it from a UDF. You can also use Application.Index(arr, 0, 0) when you’re not returning from a UDF. See this for examples:
http://stackoverflow.com/questions/3992717/how-can-i-redim-preserve-a-2d-array-in-excel-2007-vba-so-that-i-can-add-rows-n/3993119#3993119
I’m interested in practical applications of arrays within arrays (Jagged Arrays). Not for any reason other than I’m curious. I’ve read Tushar’s great article at http://www.tushar-mehta.com/publish_train/book_vba/08_variants.htm but it would be good to hear about real-world examples of where people have used these, and why. From the net, I see that one advantage is that you can redim the dimensions of any sub array , whereas if using traditional arrays you can only redim the last-listed dimension. And I also understand the concept of how these can be used to store data that isn’t symmetric, like calender dates, or a triangular matrix.
Off the top of my head I think they could be useful for something like pivottables where you might want to keep track of which pivotfields are visible, and which pivotitems for each pivotfield are visible, to say populate a userform. You could hold an array of the visible pivotfields in the first element of the parent array, and arrays containing associated pivotitems for each pivotfield in the other elements of the parent array. I’m not sure if you’d gain anything by taking this approach over others.
Can anyone give examples of how they’ve used these babies?
Cheers
Jeff
Maybe