excel - 当我从另一个宏 "Call"时宏不起作用，但是当我单独选择它时它确实起作用

Question

我在下面有一个格式化宏:

Sub Colour_whole_sheet()

Dim lastRow As Long
Dim lastColumn As Long

lastRow = Range("A1").End(xlDown).Row
lastColumn = Range("A3").End(xlToRight).Column

'Colour alternate rows purple / white
For Each cell In Range(Cells(1, 1), Cells(lastRow, lastColumn))
    If cell.Row Mod 2 = 1 Then
        cell.Interior.Color = RGB(242, 230, 255)
    Else
        cell.Interior.Color = RGB(255, 255, 255)
    End If
Next cell

End Sub

当我从另一个宏调用它时它不会运行，这只是:

Sub Run_macros()

[A bunch of other subs]
Call Colour_whole_sheet
[A bunch of other subs]

End Sub

它不会出现错误 - 它只是不做任何事情。但是，当我从“ View ”>“宏”>“ View 宏”>“运行”中单独选择它时，它可以正常工作。

你知道为什么会这样吗？

编辑:

Sub Colour_whole_sheet()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Calendar")

Dim lastRow As Long
Dim lastColumn As Long

lastRow = ws.Range("A1").End(xlDown).Row
lastColumn = ws.Range("A3").End(xlToRight).Column

'Colour alternate rows purple / white
For Each cell In ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn))
    If cell.Row Mod 2 = 1 Then
        cell.Interior.Color = RGB(242, 230, 255)
    Else
        cell.Interior.Color = RGB(255, 255, 255)
    End If
Next cell

End Sub

Answer 1

您可能会在此版本的代码之后

Sub Colour_whole_sheet(Optional sht As Variant)

    If IsMissing(sht) Then Set sht = ActiveSheet ' if no argument is passed assume ActiveSheet

    Dim lastRow As Long
    Dim lastColumn As Long
    Dim i As Long

    With sht ' reference passed/assumed sheet object
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row ' best way to get a column last used cell row index
        lastColumn = .Cells(3, .Columns.Count).End(xlToLeft).Column ' best way to get a row last used cell column index

        'Colour alternate rows purple / white
        With .Range("A1", Cells(lastRow, lastColumn)) ' reference all your range
            .Interior.Color = vbWhite ' color it white
            For i = 1 To .Rows.Count Step 2 ' loop through referenced range uneven rows
                .Rows(i).Interior.Color = RGB(242, 230, 255) ' color them with purple
            Next
        End With
    End With

End Sub

如你看到的:

它总是引用一些工作表(无论是通过子参数还是事件表)

它不会遍历所有单元格，而是遍历不均匀的行