Is it possible to create a array of arrays?

[drom]

hi and thanks in advance!

is it possible to do something like this:

Sub MrExcelQuestion()
  On Error Resume Next
Dim SHT As Object
Dim X As Single
   dim aSHT ??????

  For Each SHT In ThisWorkbook.Sheets
    X = X + 1
    ReDim aSHT & X()    :     aSHT & X ()=    SHT.Range("A1").UsedRange.Value    
  Next SHT
End Sub

My workbook may contain many sheets from 2 until 50 (every day the number of sheets is different)

I would like to create a dinamic number of arrays
But I do not know how can I do this.

• I guess maeby using class modules and/or...

Any idea ?

So my question :
Is it possible to create a dinamic array of arrays ?

I know I can solve my problem in my first macro with something like:

Sub MrExcelQuestion()
  On Error Resume Next
Dim SHT As Object
  Dim aSHT()
Dim X As Single
Dim wSHT As String
Dim rUsedRange As Range

  For Each SHT In ThisWorkbook.Sheets
    X = X + 1:                      wSHT = SHT.Name
    Set rUsedRange = Nothing:       Set rUsedRange = Sheets(wSHT).Range("A1").UsedRange
    ReDim Preserve aSHT(1 To X):    aSHT (X) =  SHT.Name & "---" & rUsedRange.Address)
  Next SHT
End Sub

And yes for my specific query may work,
But My question is:
Is it possible to create a array of arrays?

 

[Domenic]

Your macro can be amended as follows...

Sub MrExcelQuestion()

    Dim sht As Object
    Dim X As Single
    Dim aSHT() As Variant
   
    For Each sht In ThisWorkbook.Sheets
        X = X + 1
        ReDim Preserve aSHT(1 To X):     aSHT(X) = sht.UsedRange.Value
    Next sht
   
End Sub

However, here's another, more efficient way...

Sub test()

    Dim arr() As Variant
    Dim sht As Object
    Dim cnt As Long
    Dim i As Long
   
    With ThisWorkbook
        cnt = .Sheets.Count
        ReDim arr(1 To cnt)
        i = 0
        For Each sht In .Sheets
            i = i + 1
            arr(i) = sht.UsedRange.Value
        Next sht
    End With
   
End Sub

Hope this helps!

 

[drom]

Hi and Thanks.
Yes of course but I AM trying to know if it is possible to create a dinamic Array of X dinamic array's
If X is 3
I can do:
Dim a1stArray() and populate or create this array
Dim a2ndArray()...
Dim a3rdArray()...
And work with this 3 arrays

But X is gonna be unknown
This is why i was thinking maeby there is a way to create a array of arrays

My macros were, Just a way to explain if this is possible
Thanks again

 

[tusharm]

...
But My question is:
Is it possible to create a array of arrays?

Yes.

Excel FORMULATEXT function

 

[Rick Rothstein]

You can create an array of array, but the syntax is kind of funny (note the double set of parentheses to specify element numbers for master array and its sub-arrays). I'll create the individual arrays using the Split function to keep this example code somewhat compact, but you can create them however you want.

Sub ArrayOfArrays()

  Dim Arr1() As String, Arr2() As String, Arr3() As String, ArrOfArr As Variant
  Dim SecondElementOfArr1 As String, FourthElementOfArr2 As String, SeventhElementOfArr3 As Long

  Arr1 = Split("One Two Three")
  Arr2 = Split("Jan Feb Mar Apr May")
  Arr3 = Split("1 2 3 4 5 6 7 8")

  ArrOfArr = Array(Arr1, Arr2, Arr3)

  ' Remember that Split and Array creates arrays that are zero
  ' based assuming the default Option Base 0 is in effect
  SecondElementOfArr1 = ArrOfArr(0)(1)
  FourthElementOfArr2 = ArrOfArr(1)(3)
  SeventhElementOfArr3 = ArrOfArr(2)(6)

End Sub

 

[Rick Rothstein]

I just noticed that you were referring to arrays formed from ranges. The method I posted above works for them as well. Just remember, the first set of parentheses refers to the array of arrays variable and the second set of parentheses refers to the array contained within it.

Sub ArrayOfArrays()
  
  Dim Arr1 As Variant, Arr2 As Variant, Arr3 As Variant, ArrOfArr As Variant
  Dim EighthRowSecondColumnOfArr1 As Variant, FourthRowOfArr2 As Variant, SeventhRowThirdColumnOfArr3 As Variant
  
  Arr1 = Range("A1:B12").Value
  Arr2 = Range("D17:D20").Value
  Arr3 = Range("F5:H14").Value
  
  ArrOfArr = Array(Arr1, Arr2, Arr3)
  
  ' Remember that Array creates arrays that are zero-based
  ' assuming the default Option Base 0 is in effect while
  ' Range assigned to a Variant variable creates arrays
  ' that are one-based
  EighthRowSecondColumnOfArr1 = ArrOfArr(0)(8, 2)
  FourthRowOfArr2 = ArrOfArr(1)(4, 1)
  SeventhRowThirdColumnOfArr3 = ArrOfArr(2)(7, 3)
  
End Sub

 

[drom]

Thanks to everybody!
Using Array of Arrays for my prior Macros:
I have done done the following macro just to check if works
and YES, WORKS PERFECT!!

Sub MrExcelQuestion()
  On Error Resume Next
Dim SHT As Object
  Dim X As Single
  Dim XX As Single:                 XX = ThisWorkbook.Sheets.Count '=3
    Dim aSHT As Variant
      ReDim aSHT(1 To XX)
Dim wSHT As String
  Dim rUsedRange As Range
Dim xRow As Single
Dim xCol As Single
  Dim dSUM As Double

  For Each SHT In ThisWorkbook.Sheets
    X = X + 1:                      wSHT = SHT.Name
    Set rUsedRange = Nothing:       Set rUsedRange = Sheets(wSHT).Range("A1").CurrentRegion
                                    aSHT(X) = rUsedRange.Value
                                    'Debug.Print UBound(aSHT(X), 1), UBound(aSHT(X), 2)
                                    'X=1 A1:D13       13Rows,  4Cols
                                    'X=2 A1:J25       25Rows, 10Cols
                                    'X=3 A1:H23       20Rows,  8Cols
  Next SHT

  For X = LBound(aSHT) To UBound(aSHT)
    For xRow = LBound(aSHT(X), 1) To UBound(aSHT(X), 1)
      For xCol = LBound(aSHT(X), 2) To UBound(aSHT(X), 2)
        'Debug.Print aSHT(X)(xRow, xCol)
        dSUM = dSUM + (aSHT(X)(xRow, xCol))
      Next xCol
    Next xRow
  Next X

  Debug.Print dSUM
  
End Sub