Excel Macro loading Arrays

Question

I am not sure how to phrase the question, but I have come up against an issue where I need to load a list of names from a group of sheets into seperate arrays.

For example, Sheet 1, Column A has 10 names to be loaded into array1, Sheet 2, Column A has 14 names to be loaded into array2.

I know this could be hard coded into a macro to load them all but the list of names is not a fixed length and shy of a lot of code I was wondering if this could be shortened.

The way that my mind would tell me to do this is below.

Dim tarray1 As Variant
Dim tarray2 As Variant
Dim tarray3 As Variant

For f = 1 To 3

    Sheets("Region " & f).Select
    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        tarray& f = ActiveSheet.Range("A2:A" & lastrow)
    End With


Next

The actual sheet in question has 10 seperate sheets to have the names read in going from Region 1 to 10. So there will be 10 seperate tarrays. This is done for reporting purposes and will be passed back and forth around the sheets.

However tarray & f is obviously not valid and will throw up compile errors.

Can anyone think of a way to get around this?

Hope someone can help.

Tony Dallimore · Accepted Answer · 2012-01-05T09:27:44.393

The following routine shows you how to use a jagged array of variants which I believe is the technique you need.

I have loaded different numbers of cells from the the first ten rows of a worksheet to TArray. I use Debug.Print to show how to access the elements.

Sub Test()

  Dim InxCol As Integer
  Dim InxRow As Integer
  Dim InxTA As Integer
  Dim TArray() As Variant

  ReDim TArray(1 To 10)

  With Sheets("xxxxx")

  For InxTA = 1 To 10
    ' There must be at least two cells in each range if the result is to be an
    ' array.  The access code relies on TArray being a true array of arrays.
    ' If this is not possible, you can test for TArray(N) being an array
    ' or a variable using VarType
    TArray(InxTA) = .Range(.Cells(InxTA, 1), .Cells(InxTA, 12 - InxTA)).Value
  Next

  End With

  For InxTA = 1 To 10
    For InxRow = LBound(TArray(InxTA), 1) To UBound(TArray(InxTA), 1)
      For InxCol = LBound(TArray(InxTA), 2) To UBound(TArray(InxTA), 2)
        Debug.Print TArray(InxTA)(InxRow, InxCol) & " ";
      Next
    Next
    Debug.Print
  Next

End Sub

New material

I have decided to add an explanation so my answer is complete.

You can set a variant to anything and then set it to something else. The following may be stupid but it works:

Dim V As Variant

V = 5
Debug.Print V
V = "Today"
Debug.Print V
V = Array(1, 2, 3)
Debug.Print V(0) & " " & V(1) & " " & V(2)

Consider: Range( xxx ).Value

If xxx is a single cell, it returns a single variable. In all other cases it returns a two dimensional array. The first dimension is for the rows and the second for the columns. This is the opposite of normal practice but it matches Cells(R, C).Value.

I have declared:

Dim TArray() As Variant
Redim TArray(1 to 10)

So TArray is an array of variants

I then set TArray(N) = Range( xxx ).Value

If xxx is a single cell, TArray(N) will be a single value.

But if xxx is two or more cells, TArray(N) is a two dimensional array. To access a single cell within TArray, I must specify a element of TArray and then a row and column of the range. Thus:

TArray(Element)(Row, Column)

Each element of TArray can have a different size from every other element. One can be a single variable, another a single row range, another a single column range and yet another a rectangle.

This is difficult to get your head around at first. No - delete "at first". Play with this functionality. Do what I did and load rows, columns and rectangles from one of your worksheets.

score 2 · Answer 2 · answered Jan 05 '12 at 11:51

All credit to @Tony Dallimore for Array or Array's idea. This is offered only as a supliment to his answer.

To load all sheets data into an array use

Dim TArray() as Variant
Dim sh as Worksheet

ReDim TArray(1 To ActiveWorkbook.Worksheets.Count)

For each sh in ActiveWorkbook.Worksheets
    TArray(sh.Index) = sh.UsedRange ' or to get just Column A: sh.UsedRange.Columns(1)
Next

Or to load a subset of sheets whose names match a pattern

i = 1
For each sh in ActiveWorkbook.Worksheets
    If sh.Name Like "Region *" Then
         TArray(i) = sh.UsedRange ' or to get just Column A: sh.UsedRange.Columns(1)
         i = i + 1
    End If
Next

Again, credit to Tony: if OP feels inclined to accept this, please accept Tony's answer instead

Useful addition to my answer. My normal practice is to overload the OP with explanations. I think this is the first time I have ever said: "Here is a technique. Use it as you wish." — Tony Dallimore, Jan 06 '12 at 11:41

Excel Macro loading Arrays

2 Answers2

Linked