如何创建包含所有工作表的阵列

Question

我通常使用二维数组来获取工作表的内容。

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

不幸的是，我不是阵列方面的专家。我尝试添加第三个维度来存储所有工作表。然而我完全失败了：(有人能帮我吗…

谢谢。

Answer 1

Range是一个二维数组。简单地添加第三个维度并不能解决你的问题。最佳的方法是使用Dictionary，表名为key，数据数组为value。

---

从学习的角度来看，如果只想将工作表和相关数据存储在数组中，请使用锯齿状数组。锯齿数组是一个数组，其一个或多个项是数组本身。请参阅下面的代码，这是您可以采用的众多方法之一。

阅读代码注释以获得澄清。

---

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