加快VBA代码 [英] Speed up the VBA code

问题描述

您知道如何加快以下VBA代码的运行速度吗?我已经尝试过在线浏览，但是这里可能还缺少其他内容.这是一个简单的脚本，令我惊讶的是它花了一些时间来运行.谢谢

Do you know how I could speed up the running of the following VBA code? I've tried browsing online but there might be something else I'm missing here. It is a simple script and I'm surprised it takes a bit of time to run it. Thanks

Sub ExistingRecord()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False

    Dim ButtonName As Variant
    Dim ButtonNames As Variant

    ButtonNames = Array("ButtonUpdateExisting", "ButtonAddNew", "ClearForm")
        Rows("2").Select
        Selection.EntireRow.Hidden = False
        Rows("3:22").Select
        Selection.EntireRow.Hidden = True

        Range("D2").Select
          For Each ButtonName In ButtonNames
          ActiveSheet.Buttons("ButtonUpdateExisting").Visible = True
          ActiveSheet.Buttons("ButtonAddNew").Visible = False
          ActiveSheet.Buttons("ClearForm").Visible = True
        Next ButtonName

    ClearDataEntry

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True

End Sub

推荐答案

如果不查看 ClearDataEntry ，就很难知道它在哪里放慢了速度.但是上面的代码可以简化为以下代码:

Without looking at ClearDataEntry, it's difficult to see where this is slowing down. But the code above can be reduced to the following which should be quicker:

Sub ExistingRecord()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False

    Rows("2").EntireRow.Hidden = False
    Rows("3:22").EntireRow.Hidden = True

    ActiveSheet.Buttons("ButtonUpdateExisting").Visible = True
    ActiveSheet.Buttons("ButtonAddNew").Visible = False
    ActiveSheet.Buttons("ClearForm").Visible = True

    ClearDataEntry

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True

End Sub

这篇关于加快VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！