How to create an Array containing all Worksheets

I generally use a 2 dimensional Array to get the content of a worksheets.

Dim arrCheckCountDG
CheckCountDG = WBSProj.Range(WBSProj.Cells(2, 1), WBSProj.Cells(Last(1, WBSProj.Range("A:A")), Last(2, WBSProj.Range("1:1"))))

Unfortunately I am not an expert on Arrays. I tried to add the third dimension to store all worksheets. However I failed completely:( Can somebody help me with....

Thanks.

Solution

Range is a two dimensional array. Simply adding a third dimension won't solve your problem. Best and optimal approach would be to use Dictionary with sheet name as key and data array as value.

For learning point of view, If you want to store sheets and related data in array only then use jagged array. Jagged array is an array whose one or more items are array itself. See the code below, its one of the many approaches that you can take.

Read the code comments for clarifications.

Sub test()
    Dim arrData

    '/ Load an array with all the sheets and the data in it.
    arrData = LoadArray()

    '/ Pringt the data
    PrintArray arrData
End Sub

Function LoadArray() As Variant

    Dim wks     As Worksheet
    Dim lCtr    As Long
    Dim lItr    As Long

    Dim arrData '~~> [,]
    Dim arrSheets() '~~> ["SheetName", Data[]]

    lCtr = ThisWorkbook.Worksheets.Count

    '/ Resize array for each sheet. Tranposing jagged array is not starightforward
    '/ so keep the rows fixed. 1--> Sheet name. 2--> data array.
    ReDim Preserve arrSheets(1 To lCtr, 1 To 2)

    '/ Loop through all sheets
    For Each wks In ThisWorkbook.Worksheets
        '/ Read all the data on the sheet
        arrData = wks.UsedRange

        lItr = lItr + 1
        '/Add data in sheets array
        arrSheets(lItr, 1) = wks.Name '~~> Sheet Name
        arrSheets(lItr, 2) = arrData  '~~> Data array
    Next

    LoadArray = arrSheets

End Function

Sub PrintArray(arrSheets)

    Dim arrData
    Dim lSheetCtr       As Long
    Dim lColCtr         As Long
    Dim lRowCtr         As Long

    '/ Print data from sheets array '~~> ["SheetName", Data[]]
    For lSheetCtr = LBound(arrSheets) To UBound(arrSheets)
        '/ print sheet names
        Debug.Print "~~> Sheet Name :: " & arrSheets(lSheetCtr, 1)

        '/Extract inner array
        arrData = arrSheets(lSheetCtr, 2)

        '/ Check, if there is any data to print?
        If Not IsEmpty(arrData) Then
            Debug.Print "Data:: "

            '/ Iterate data array and print values.
            For lRowCtr = LBound(arrData) To UBound(arrData)
                For lColCtr = LBound(arrData, 2) To UBound(arrData, 2)
                    Debug.Print "Row :" & lRowCtr & " Col :" & lColCtr & " Value :" & arrData(lRowCtr, lColCtr)
                Next
            Next
        Else
            Debug.Print "NO Data"
        End If
    Next

End Sub