Is it possible to create a array of arrays?

drom

Active Member
Joined
Mar 20, 2005
Messages
443
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
hi and thanks in advance!

is it possible to do something like this:

VBA Code:
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:

VBA Code:
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?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Your macro can be amended as follows...

VBA Code:
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...

VBA Code:
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

Active Member
Joined
Mar 20, 2005
Messages
443
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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.
VBA Code:
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

Active Member
Joined
Mar 20, 2005
Messages
443
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
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!!


VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,265
Messages
5,595,181
Members
413,974
Latest member
LB_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top