excel - ScreenUpdating = False 不工作

Question

我知道有很多与此主题相关的线程，例如不要使用“选择”或“激活”，或者如果需要使用它，请将其设置为 false。我每次使用“select”或“activate”时都将其设置为False，但仍然不起作用，请帮忙!!

Sub Forecast()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = True
    'Application.StatusBar = "Please be patient..."

    Dim Rating As Variant, sht As Worksheet, LastRecordRow As Long, i As Integer, LastRow As Long

    Rating = InputBox("Please Provide Weather Rating (Any Number Between 1 and 4)", "Input Needed")

    If Rating < 0 Or Rating > 4 Then
        MsgBox "Invalid Value, Please Enter A Valid Number! (1~4)", , "Alert!!!"
        Exit Sub
    Else

        For Each sht In ActiveWorkbook.Worksheets
        If sht.Name = "Forecast" Then
            sht.Range("a1") = ""
        Else
            LastRecordRow = sht.Range("A1").End(xlDown).Row
            sht.Range("I1:O1").EntireColumn.Delete
            Application.Run "ATPVBAEN.XLAM!Regress", sht.Range("$B$1", "$B" & LastRecordRow), _
            sht.Range("$C$1", "$C" & LastRecordRow), False, True, , sht.Range("$I$1") _
            , False, False, False, False, , False
            PVTotal = Application.WorksheetFunction.Sum(Range("B2", "B" & LastRecordRow))
            ImpTotal = Application.WorksheetFunction.Sum(Range("D2", "D" & LastRecordRow))
            sht.Range("B" & LastRecordRow + 1) = PVTotal
            sht.Range("D" & LastRecordRow + 1) = ImpTotal
            sht.Cells.EntireColumn.AutoFit
            sht.Range("A1").Select
        End If
        Next sht

        Worksheets("Forecast").Activate

        i = 1

        ActiveSheet.Range("B" & i + 2, Range("H" & i + 2).End(xlDown)).EntireRow.Delete

        Do While i <= ActiveWorkbook.Worksheets.Count
            RowForSum = Worksheets(i).Range("B1").End(xlDown).Row
            With ActiveSheet
                .Cells(i + 2, 2).Value = Worksheets(i).Name
                .Cells(i + 2, 3).Value = Worksheets(i).Range("J17")
                .Cells(i + 2, 4).Value = Worksheets(i).Range("J18")
                .Cells(i + 2, 5).Value = Rating
                .Cells(i + 2, 6).Value = ActiveSheet.Cells(i + 2, 3).Value + ActiveSheet.Cells(i + 2, 4) * Rating
                If Worksheets(i).Range("B183").Value = 0 Then
                    .Cells(i + 2, 7).Value = 0
                Else
                    .Cells(i + 2, 7).Value = Worksheets(i).Range("D" & RowForSum).Value / Worksheets(i).Range("B" & RowForSum).Value
                End If
                    .Cells(i + 2, 8).Value = ActiveSheet.Cells(i + 2, 6).Value * ActiveSheet.Cells(i + 2, 7)
            End With
            i = i + 1
        Loop

        LastRow = ActiveSheet.Range("B2").End(xlDown).Row

        a = Application.WorksheetFunction.Sum(ActiveSheet.Range("F3", "F" & LastRow))
        b = Application.WorksheetFunction.Sum(ActiveSheet.Range("H3", "H" & LastRow))

        With ActiveSheet
            .Range("F" & LastRow + 1).Value = a
            .Range("F" & LastRow + 1).Offset(0, -4).Value = "Total"
            .Range("H" & LastRow + 1).Value = b
            .Range("A1").Select
            .Cells.EntireColumn.AutoFit
        End With

        Dim rng2 As Range
        For Each rng2 In ActiveSheet.Range("B2", Range("B2").End(xlDown))
            If rng2 = "Forecast" Then
                rng2.EntireRow.Delete
            Else
                If rng2 = "Total" Then rng2.EntireRow.Font.Bold = True
            End If
        Next
    End If

    Application.ScreenUpdating = True
    'Application.StatusBar = False
    Application.DisplayAlerts = True

End Sub

Answer 1

我也有类似的问题。使用过的练习册Application.ScreenUpdating = False在 Excel 2016 中变得缓慢，并且在运行更新工作表的 VBA 时闪烁。

使用消息框和 Debug.Print我将其跟踪到一个循环，该循环使用如下代码更新了单元格内容:

Dim rowCounter As Integer
For rowCounter = 1 To numberOfEmployees
    'Do some work
    If (someCondition) Then
        startCell.offset(rowCounter).Value = ""
    Else 
        startCell.offset(rowCounter).Value = "something else"
    End If
Next rowCounter

如果我替换了以下行

startCell.offset(rowCounter).Value = ""

与其中之一

startCell.offset(rowCounter).Value = "anything except empty"

或

startCell.offset(rowCounter).ClearContents

然后闪烁停止，执行循环的时间从几秒缩短到远小于 1 秒。

我不知道为什么这会起作用。

因此，如果循环中的任何单元格返回空字符串，您可以尝试使用这两行之一。