使用模块运行工作簿中的所有工作表- VBA

Question

对于我一直在做的一个项目，我终于完成了用于分析季度时间表的代码，现在我正试图让我的代码分析工作簿中的所有四个工作表(每个工作表代表一个不同的季度)。

我正在经历的错误是"运行时错误1004对象_global的方法范围失败"

我目前正在使用一个模块来运行所有四个表的代码。我的代码如下:

sub scheduleAnalyzer()
Dim ws_count As Integer
Dim I As Integer
ws_count = ActiveWorkbook.Worksheets.Count
For I = 1 To ws_count 
    'then my code which analyzes the schedule and prints results on a new sheet
Next I
End Sub

这是我第一次使用模块来运行代码。错误发生在以下位置:

Dim month1 As Range
Dim month2 As Range
Dim month3 As Range
Dim month1_start As String
Dim month1_end As String
Dim month2_start As String
Dim month2_end As String
Dim month3_start As String
Dim month3_end As String  
month1_start = "B6"
monthRow = 6
dayRow = 7
For k = 2 To maxCol
    If Cells(monthRow, k).Interior.color = colorArray(10) And Cells(monthRow, k + 1).Interior.color = colorArray(11) Then
        month1_end = Cells(monthRow, k).Address
        month2_start = Cells(monthRow, k + 1).Address
    ElseIf Cells(monthRow, k).Interior.color = colorArray(11) And Cells(monthRow, k + 1).Interior.color = colorArray(10) Then
        month2_end = Cells(monthRow, k).Address
        month3_start = Cells(monthRow, k + 1).Address
    ElseIf Cells(monthRow, k).Interior.color = colorArray(10) And Cells(monthRow, k + 1).Interior.color = colorArray(9) Then
        month3_end = Cells(monthRow, k).Address
    End If
Next k
month1 = Range(month1_start, month1_end) '<--------------- run-time error 1004 method range of object _global failed
month2 = Range(month2_start, month2_end)
month3 = Range(month3_start, month3_end)

我知道这段代码工作时，我只是运行一个特定的表。这是在整个工作簿中运行它的最好方法吗?如果是这样，我该如何修复这个错误?我尝试使用带有"ActiveWorkbook.Worksheets()"的With语句，但我无法命名一个单数ws放入括号中，因为我希望它在所有四个中运行。

Answer 1

据我所知，你肯定会得到一个错误，由于没有使用Set设置你的Range变量，但这将是一个"对象变量或块变量未设置"的错误。

您获得的"对象_global的方法范围失败"错误可能是因为month1_end没有设置，这可能是因为您的代码从未设置变量maxCol(因此您的For k =循环将永远不会执行)，或者您可能已经在代码中设置了maxCol，在这种情况下，month1_end可能没有被正确设置，因为它没有处理您打算的工作表。

下面的代码应该可以避免这些错误，并为您提供一个可以构建循环的结构:

sub scheduleAnalyzer()
    Dim ws As Worksheet
    Dim month1 As Range
    Dim month2 As Range
    Dim month3 As Range
    Dim month1_start As String
    Dim month1_end As String
    Dim month2_start As String
    Dim month2_end As String
    Dim month3_start As String
    Dim month3_end As String  
    'monthRow is not defined in original code
    Dim monthRow As Long
    'dayRow is not defined in original code
    Dim dayRow As Long
    'k is not defined in original code
    Dim k As Long
    'maxCol isn't defined or assigned a value in original code
    Dim maxCol As Long
    maxCol = 36 ' <-- purely random number - please assign it how you were assigning it when code was working
    For Each ws in ActiveWorkbook.Worksheets
        With ws
            month1_start = "B6"
            monthRow = 6
            dayRow = 7
            For k = 2 To maxCol
                If .Cells(monthRow, k).Interior.color = colorArray(10) And .Cells(monthRow, k + 1).Interior.color = colorArray(11) Then
                    month1_end = .Cells(monthRow, k).Address
                    month2_start = .Cells(monthRow, k + 1).Address
                ElseIf .Cells(monthRow, k).Interior.color = colorArray(11) And .Cells(monthRow, k + 1).Interior.color = colorArray(10) Then
                    month2_end = .Cells(monthRow, k).Address
                    month3_start = .Cells(monthRow, k + 1).Address
                ElseIf .Cells(monthRow, k).Interior.color = colorArray(10) And .Cells(monthRow, k + 1).Interior.color = colorArray(9) Then
                    month3_end = .Cells(monthRow, k).Address
                End If
            Next k
            Set month1 = .Range(month1_start, month1_end)
            Set month2 = .Range(month2_start, month2_end)
            Set month3 = .Range(month3_start, month3_end)
        End With
    Next
End Sub

---

编辑以下注释:

使一个名为scheduleAnalyzer的现有子(假定当前正在为单个活动的工作表工作)处理工作簿中的每个工作表的一种极简方法是:

Sub RunAllScheduleAnalyzer
    Dim ws As Worksheet
    For Each ws in Worksheets
        ws.Activate
        scheduleAnalyzer
    Next
End Sub

我不建议这是一个短期的解决方案-使用Activate(和Select)来确定哪些单元格将被VBA应用程序处理是应该避免的，如果可能的话，因为它会导致太多的"但是这个代码通常工作!? ?!?"类型的问题。