Arrays in Arrays

Posted on by Dick Kusleika

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.

Posted in Uncategorized

16 thoughts on “Arrays in Arrays”

  1. >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

  3. 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

  4. 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.

  6. 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.

  7. 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

  8. 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.

    Sub RangeArrays()
       
        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(1) = Sheet1.Range(“A1:A4”).Value
    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.

  9. 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

  10. 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

    For j = LBound(vaRngArray) to UBound(vaRngArray)

    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

    Set r1 = ActiveSheet.Range(“A1:A10”)
    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

    Dim rCell as Range
    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

    Dim rRow as Range
    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.

  11. 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

  12. 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:

    Sheet2.Range(“A1:D4”) = vaRangeArr

    But this outputs nothing. However, I can output one line at a time like so:

    Sheet2.Range(“A1:A4”) = vaRangeArr(1)

    Thanks in advance,
    Victor

  13. 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.

  14. 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.

  16. 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

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.