Excel Macro loading Arrays

excel, vba

Solution

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.

Problem

Original source