VBA中如何将工作表动态分配至Sheets集合？

初始可行代码 #

以下代码可以正常创建包含指定工作表的Sheets集合：

Dim shts As Sheets
Set shts = Sheets(Array("Sheet1", "Sheet2"))

需求与问题 #

希望将未来新建的工作表也自动加入shts集合，尝试通过循环拼接字符串的方式实现，但代码报错：

Dim shts As Sheets
Dim wks() As Worksheet
Dim str As String

ReDim wks(0 To Sheets.Count)
Set wks(0) = Sheets(1)

str = wks(0).Name & """"

For i = 1 To UBound(wks)
    Set wks(i) = Sheets(i)
    str = str & ", """ & wks(i).Name & """"
Next i
    
Set shtsToProtect = Sheets(Array(str)) ' 运行时错误 '9': 下标越界

错误原因 #

你错误地将拼接好的单个字符串传入Array()，此时Array(str)生成的是一个仅包含一个元素的数组（这个元素是逗号分隔的所有表名字符串），而Sheets()需要的是多个独立的工作表名称字符串组成的数组，不是一个组合字符串，因此触发下标越界错误。

正确解法 #

方法1：直接引用所有工作表集合 #

如果需要的是包含当前及未来所有工作表的集合，直接引用Sheets对象即可，它会自动包含新建的工作表：

Dim shts As Sheets
Set shts = ThisWorkbook.Sheets ' 或直接Set shts = Sheets

后续新建工作表后，shts会自动包含这些新表，因为它指向的是工作簿的所有工作表集合。

方法2：动态生成工作表名称数组 #

如果你需要手动生成包含所有工作表名称的数组（比如后续要筛选特定表），可以直接遍历工作表，将名称存入数组，再传入Sheets()：

Dim shts As Sheets
Dim sheetNames() As String
Dim i As Integer

' 重新定义数组大小，匹配工作表数量
ReDim sheetNames(1 To ThisWorkbook.Sheets.Count)

' 遍历所有工作表，存入名称数组
For i = 1 To ThisWorkbook.Sheets.Count
    sheetNames(i) = ThisWorkbook.Sheets(i).Name
Next i

' 创建包含所有工作表的集合
Set shts = ThisWorkbook.Sheets(Array(sheetNames))

如果后续新建了工作表，只需重新执行这段代码，就能更新shts集合。

方法3：使用事件自动更新（针对未来新建表） #

如果希望新建工作表时自动将其加入指定集合，可以在工作簿的SheetNew事件中添加逻辑：

1. 打开VBA编辑器，双击ThisWorkbook
2. 在左侧下拉菜单选择Workbook，右侧下拉菜单选择SheetNew
3. 写入以下代码：

Private Sub Workbook_SheetNew(ByVal Sh As Object)
    Dim shts As Sheets
    ' 重新获取所有工作表集合
    Set shts = ThisWorkbook.Sheets
    ' 这里可以添加后续操作，比如保护新加入的工作表等
    Sh.Protect Password:="yourpassword"
End Sub

这样每次新建工作表时，shts都会自动包含新表，同时可以执行你需要的后续操作。

内容的提问来源于stack exchange，提问作者Starnes Student