Excel 2016 VBA：遍历工作簿工作表赋值数组时触发错误91

在Excel 2016 VBA开发中，使用For Each遍历工作簿工作表，将符合条件的工作表赋值到对应数组时触发运行时错误91（Object variable or With block variable not set）。

简化代码如下：

Sub wat()
    Dim EHSBook As Workbook
    Dim Err as Boolean

    Dim sheetRecord As Worksheet
    Dim sheetAmList(7) As Worksheet
    Dim sheetPmList(7) As Worksheet
    Dim sheetAmListCnt As Integer
    Dim sheetPmListCnt As Integer

    sheetAmListCnt = 1
    sheetPmListCnt = 1

    Call OpenEhsFile(EHSBook, Err)
    If Err = True Then Exit Sub

    'get which sheets we need to total up
    For Each sheetRecord In EHSBook.Sheets
        If IsDate(sheetRecord.Cells(1, 3).Value) Then
            If Right(sheetRecord.name, 4) = "(AM)" Then
                ' next line throws error 91
                sheetAmList(sheetAmListCnt) = sheetRecord
                sheetAmListCnt = sheetAmListCnt + 1
            ElseIf Right(sheetRecord.name, 4) = "(PM)" Then
                ' next line throws error 91
                sheetPmList(sheetPmListCnt) = sheetRecord
                sheetPmListCnt = sheetPmListCnt + 1
            End If
        End If
    Next sheetRecord
End Sub

已确认数组声明正确且EHSBook已赋值，但问题仍存在，预期按工作表名称后缀将对应工作表加入数组。

错误原因 #

运行时错误91的核心原因是：对象类型的数组元素赋值时未使用Set关键字。

Worksheet是VBA中的对象类型，对于对象变量（包括对象数组的元素），不能直接用=赋值，必须通过Set关键字来绑定对象引用。直接赋值会导致VBA无法识别对象引用的赋值操作，触发"对象变量或With块变量未设置"错误。

修复方案 #

1. 添加Set关键字：给对象数组元素赋值时，必须用Set绑定对象引用
2. 避免关键字冲突：Err是VBA内置错误对象，自定义变量名不要用它，防止潜在问题
3. 添加边界检查：防止符合条件的工作表数量超过数组容量，引发越界错误

完整修复代码 #

Sub wat()
    Dim EHSBook As Workbook
    Dim isErrorOccurred As Boolean ' 替换Err为自定义变量名，避免冲突

    Dim sheetRecord As Worksheet
    Dim sheetAmList(7) As Worksheet
    Dim sheetPmList(7) As Worksheet
    Dim sheetAmListCnt As Integer
    Dim sheetPmListCnt As Integer

    sheetAmListCnt = 1
    sheetPmListCnt = 1

    Call OpenEhsFile(EHSBook, isErrorOccurred)
    If isErrorOccurred = True Then Exit Sub

    'get which sheets we need to total up
    For Each sheetRecord In EHSBook.Sheets
        If IsDate(sheetRecord.Cells(1, 3).Value) Then
            If Right(sheetRecord.Name, 4) = "(AM)" Then
                ' 检查数组边界，防止越界
                If sheetAmListCnt <= UBound(sheetAmList) Then
                    Set sheetAmList(sheetAmListCnt) = sheetRecord ' 使用Set关键字赋值对象
                    sheetAmListCnt = sheetAmListCnt + 1
                End If
            ElseIf Right(sheetRecord.Name, 4) = "(PM)" Then
                ' 检查数组边界，防止越界
                If sheetPmListCnt <= UBound(sheetPmList) Then
                    Set sheetPmList(sheetPmListCnt) = sheetRecord ' 使用Set关键字赋值对象
                    sheetPmListCnt = sheetPmListCnt + 1
                End If
            End If
        End If
    Next sheetRecord
End Sub

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