vba ScreenUpdating = False 不起作用

ScreenUpdating = False not working

提问by walkens

I know there are lots of threads relating to this topic, like don't use "Select" or "Activate" or set it to false if you need to use it. I set it to False every time I use "select" or "activate", but it still not working, please help!!

我知道有很多与此主题相关的主题，例如不要使用“选择”或“激活”，或者在需要时将其设置为 false。每次使用“选择”或“激活”时，我都将其设置为 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", "$B" & LastRecordRow), _
            sht.Range("$C", "$C" & LastRecordRow), False, True, , sht.Range("$I") _
            , 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

回答by user1857450

I had a similar issue. Workbook which used Application.ScreenUpdating = Falsebecame slow in Excel 2016 and flickered while running VBA that updated worksheets.

我有一个类似的问题。使用的工作簿Application.ScreenUpdating = False在 Excel 2016 中变慢并在运行更新工作表的 VBA 时闪烁。

Using message boxes and Debug.Printstatements I tracked it down to a loop that updated cell contents using code like:

使用消息框和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

If I replaced the following line

如果我替换了以下行

startCell.offset(rowCounter).Value = ""

with either of

与

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

or

或者

startCell.offset(rowCounter).ClearContents

then the flickering stopped and the time to execute the loop went from a few seconds to much less than 1s.

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

I am not sure why this worked.

我不确定为什么这有效。

So if any of your cells in the loop return empty string, you could try using either of these lines.

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