如何加快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屋！