excel - Application.ScreenUpdating = False 不起作用。是否有可能在我的代码或 MS365 问题的某个地方打开它？

Question

我最近在换公司时下载了 Microsoft 365，但现在我的 application.screenupdating 无法正常工作 - 开启此功能非常缓慢。有潜在的解决方法吗？我在下面添加了我的代码（并为它不是一个片段而道歉），否则我可能会错过它让它重新打开的部分。非常感谢任何建议！它似乎适用于使用相同代码的 mac 用户......？

Sub Disaggregated_Data_Model()

'Alert box before running the model
    Dim Answer As VbMsgBoxResult
        Answer = MsgBox("Do you want to start the model?", vbYesNo + vbQuestion + vbDefaultButton2)
    If Answer = vbYes Then

'Turn off items that slow down the model
    Application.ScreenUpdating = False
    
'Clear prior disaggregated returns data
    With Worksheets("Network Returns")
        'Disag scenario reuslts
        .Range("J11:AF1009").ClearContents
        'Cash flows
        .Range("AI11:AL77").ClearContents
        .Range("AO11:AR77").ClearContents
        .Range("AU11:AX77").ClearContents
    End With

On Error Resume Next

'Run macro through scenario down
    Sheets("SITE Model").Range("C14").Value = "Down"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
        .Range("C11").Value = 0
    End With
    
'Row movement variable
RowNum = 11
    
'Run macro through specified start site index to end site index
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i
        
        'Capture data from SITE Model
            Sheets("SITE Model").Range("C283:C289").Copy
            Sheets("Network Returns").Range("J" & RowNum).PasteSpecial xlPasteValues, Transpose:=True

            'Move paste down 1 row on each cycle
                RowNum = RowNum + 1

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next
    
'Paste downside aggregate returns for DA
    Sheets("NETWORK Model").Range("C65:C72").Copy
    Sheets("Network Returns").Range("C12:C19").PasteSpecial xlPasteValues
    
'Paste all aggrgeated cash flows into Network Returns
    Sheets("NETWORK Model").Range("D76:EF79").Copy
    Sheets("Network Returns").Range("AI11").PasteSpecial xlPasteValues, Transpose:=True
        
'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("C84").PasteSpecial xlPasteValues

'Run macro through scenario base
    Sheets("SITE Model").Range("C14").Value = "Base"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
        .Range("C11").Value = 0
    End With
    
'Row movement variable
RowNum1 = 11

'Run macro through specified start site index to end site index
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i
        
        
        'Capture data from SITE Model
            Sheets("SITE Model").Range("C283:C289").Copy
            Sheets("Network Returns").Range("R" & RowNum1).PasteSpecial xlPasteValues, Transpose:=True

            'Move paste down 1 row on each cycle
                RowNum1 = RowNum1 + 1

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next
    
'Paste base aggregate results for DA
    Sheets("NETWORK Model").Range("C65:C72").Copy
    Sheets("Network Returns").Range("D12:D19").PasteSpecial xlPasteValues
   
'Paste all aggrgeated cash flows into scenario database
    Sheets("NETWORK Model").Range("D76:EF79").Copy
    Sheets("Network Returns").Range("AO11").PasteSpecial xlPasteValues, Transpose:=True
    
'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("D84").PasteSpecial xlPasteValues

'Run macro through scenario up
    Sheets("SITE Model").Range("C14").Value = "Up"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
        .Range("C11").Value = 0
    End With
    
'Row movement variable
RowNum2 = 11
    
'Run macro through specified start site index to end site index
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i
        
        'Capture data from SITE Model
            Sheets("SITE Model").Range("C283:C289").Copy
            Sheets("Network Returns").Range("Z" & RowNum2).PasteSpecial xlPasteValues, Transpose:=True

            'Move paste down 1 row on each cycle
                RowNum2 = RowNum2 + 1

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next
    
'Paste up aggregate results for DA
    Sheets("NETWORK Model").Range("C65: C72 ").Copy
    Sheets("Network Returns").Range("E12:E19").PasteSpecial xlPasteValues
    
'Paste all aggrgeated cash flows into scenario database
    Sheets("NETWORK Model").Range("D76:EF79").Copy
    Sheets("Network Returns").Range("AU11").PasteSpecial xlPasteValues, Transpose:=True
    
'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("E84").PasteSpecial xlPasteValues

'Time stamp
    Sheets("Network Returns").Range("C3") = Now
    
    Else
    Exit Sub
    End If

'Exit out of copy paste mode
    Application.CutCopyMode = False
    
'Go to results page
    Sheets("NETWORK Model").Activate
    Range("A1").Select
    Sheets("Network Returns").Activate
    Range("A1").Select
    
'Turn screen updates back on
    Application.ScreenUpdating = True
    
'Alert for done
    MsgBox "Scenario testing complete"

End Sub

Sub Returns_Only_Model()

'Alert box before running the model
    Dim Answer As VbMsgBoxResult
        Answer = MsgBox("Do you want to start the model?", vbYesNo + vbQuestion + vbDefaultButton2)
    If Answer = vbYes Then

'Turn off items that slow down the model
    Application.ScreenUpdating = False
    
'Clear prior disaggregated returns data
    With Worksheets("Network Returns")
        'Disag scenario reuslts
        .Range("J11:AF1009").ClearContents
        'Cash flows
        .Range("AI11:AL77").ClearContents
        .Range("AO11:AR77").ClearContents
        .Range("AU11:AX77").ClearContents
    End With

On Error Resume Next

'Run macro through scenario down
    Sheets("SITE Model").Range("C14").Value = "Down"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
    End With
    
'Run macro through specified start site index to end site index
    Dim i As Integer
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next i
    
'Paste downside aggregate returns for DA
    Sheets("NETWORK Model").Range("C65:C72").Copy
    Sheets("Network Returns").Range("C12:C19").PasteSpecial xlPasteValues
    
'Paste all aggrgeated cash flows into scenario database
    Sheets("NETWORK Model").Range("D76:EF79").Copy
    Sheets("Network Returns").Range("AI11").PasteSpecial xlPasteValues, Transpose:=True
    
'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("C84").PasteSpecial xlPasteValues
    
'Run macro through scenario down
    Sheets("SITE Model").Range("C14").Value = "Base"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
        .Range("C11").Value = 1
    End With
    
'Run macro through specified start site index to end site index
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next
    
'Paste base aggregate returns for DA
    Sheets("NETWORK Model").Range("C65:C72").Copy
    Sheets("Network Returns").Range("D12:D19").PasteSpecial xlPasteValues
    
'Paste all aggrgeated cash flows into scenario database
        Sheets("NETWORK Model").Range("D76:EF79").Copy
        Sheets("Network Returns").Range("AO11").PasteSpecial xlPasteValues, Transpose:=True
        
'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("D84").PasteSpecial xlPasteValues
    
'Run macro through scenario down
    Sheets("SITE Model").Range("C14").Value = "Up"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
        .Range("C11").Value = 1
    End With
    
'Run macro through specified start site index to end site index
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next
    
'Paste up aggregate returns for DA
    Sheets("NETWORK Model").Range("C65:C72").Copy
    Sheets("Network Returns").Range("E12:E19").PasteSpecial xlPasteValues
    
'Paste all aggrgeated cash flows into scenario database
        Sheets("NETWORK Model").Range("D76:EF79").Copy
        Sheets("Network Returns").Range("AU11").PasteSpecial xlPasteValues, Transpose:=True

'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("E84").PasteSpecial xlPasteValues

'Time stamp
    Sheets("Network Returns").Range("C3") = Now
    
    Else
    Exit Sub
    End If

'Exit out of copy paste mode
    Application.CutCopyMode = False
    
'Go to results page
    Sheets("NETWORK Model").Activate
    Range("A1").Select
    Sheets("Network Returns").Activate
    Range("A1").Select
    
'Turn screen updates back on
    Application.ScreenUpdating = True
    
'Alert for done
    MsgBox "Scenario testing complete"

End Sub

感谢你的帮助！

标签： excelvbaoffice365