如何加速 VBA 代码 [英] How To Speed Up VBA Code

问题描述

我有一个带有 VBA 代码的 excel 文件(不是我写的)此代码的工作原理是用户在用户表单中输入一个 6 位数字，然后 VBA 检查另一张工作表，以及工作表上是否存在此 6 位数字.如果是，它会改变阶段，但如果不是，它会将这个 6 位数字添加到工作表中

I have an excel file with a VBA code (Not written by me) How this code works is user enters a 6 digit number in a user form, the VBA then checks another sheet and if this 6 digit number is present on the worksheet. If it does, it changes the stage, but if it doesn't it adds this 6 digit number to the worksheet

它曾经完美地工作，但现在因为excel文件的行数增加了，将近6000行，这段代码变得很慢，更新表格需要20秒

It used to work perfectly, but now because the excel file has grown in the number of rows, almost 6000 rows, this code is become very slow, takes up to 20 seconds to update the sheet

有人可以帮我加快这个代码的速度，或者建议另一种方法来实现它

Can someone please help me speed this code up, or suggest another way to acheive it

代码如下

Private Sub cmdPSDUdate_Click()
Dim x
If (Me.PSDUDateRow = "") + (Me.PSDStageCB.ListIndex = -1) Then Exit Sub
With Sheets("psdata stage cals").ListObjects("PSDataStageCals")
    x = Application.Match(Val(Me.PSDUDateRow), .ListColumns(1).DataBodyRange, 0)
    If IsNumeric(x) Then
        .ListRows(x).Range(2) = Me.PSDStageCB.Value
    Else
        .ListRows.Add.Range = Array(Val(Me.PSDUDateRow), Me.PSDStageCB)
    End If
End With
Me.PSDUDateRow.Value = ""
Me.PSDStageCB.Value = ""
Me.PSDUDateRow.SetFocus
End Sub

提前致谢

拉胡尔

推荐答案

您可以关闭屏幕更新、自动计算等

You could turn off screenupdating, automatic calculations etc

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

‘Place your macro code here

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

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