如何遍历Worksheet对象数组？VBA遍历代码执行异常求助

核心问题 #

你的代码中全局变量命名为sheets，和VBA内置的Sheets工作表集合对象重名，导致For Each sheet In sheets语句被错误解析为遍历内置的Sheets集合，而非你自定义的数组。此外，原错误处理逻辑会在出错时直接跳出流程，无法定位具体问题。

解决方案 #

1. 重命名全局变量，避免命名冲突 #

修改全局变量名，避开VBA内置对象名称：

• Globals模块：

Option Explicit

Public targetWorksheets As Variant  ' 替换原sheets变量名

• ThisWorkbook：

Option Explicit

Private Sub Workbook_Open()
    targetWorksheets = Array(Sheet4, Sheet5, Sheet7)
End Sub

• WorkbookUtils模块：

Option Explicit

Public Sub UpdatePlan(ByVal sourceSheet As Worksheet)
    
    Dim i As Integer
    Dim sheet As Worksheet
    
    On Error GoTo ErrorHandler
    
    Debug.Print "Sheets:"
    For i = LBound(targetWorksheets) To UBound(targetWorksheets)
        Debug.Print targetWorksheets(i).Name
    Next i
    
    If sourceSheet.ProtectContents Then
        
        Application.EnableEvents = False
        
        Debug.Print "Looping through sheets"
        
        For Each sheet In targetWorksheets
            Debug.Print "Sheet name: " & sheet.Name
            CallByName sheet, "SheetMethod", VbMethod
        Next sheet
        
    End If
    
ExitSub:
    Application.EnableEvents = True
    Exit Sub
    
ErrorHandler:
    Debug.Print "错误代码：" & Err.Number & "，错误描述：" & Err.Description  ' 添加错误信息输出，便于定位问题
    Resume ExitSub
    
End Sub

2. 验证SheetMethod方法的存在性 #

确保Sheet4、Sheet5、Sheet7的代码模块中都定义了Public Sub SheetMethod()，否则CallByName会触发错误，导致循环提前终止。

3. 优化错误处理逻辑 #

原错误处理会直接跳过错误，添加错误信息输出后，能快速区分是遍历逻辑问题还是方法调用问题。

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