How To Speed Up VBA Code

Question

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

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

The code is below

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

Thanks in advance

Rahul

Answer 1

In general, there are two ways to speed up VBA code:

1. Write good code, that does not use Select, Activate, ActiveCell, Selection etc - How to avoid using Select in Excel VBA

2. Refer to these routines on the start and on the end of the code:

---

Public Sub OnEnd()

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic
    ThisWorkbook.Date1904 = False        
    Application.StatusBar = False
    
End Sub

Public Sub OnStart()
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    Application.Calculation = xlAutomatic
    ThisWorkbook.Date1904 = False
    ActiveWindow.View = xlNormalView

End Sub

(For improvement ideas, kindly make PullRequest)

• I think that Calculation should be always set to xlAutomatic, as far as if you need xlCalculationManual to speed up, it is a good idea to refactor the code. Furthermore manual calculation is too risky.
• The same goes for Date1904 - it is always set to False.