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

Catarinacatarrh

If the code doesn't throw any errors but is simply slow, it doesn't belong here. Please consider moving it to stackexchange.com for review and improvement suggestions. – Nedi

@KostasK. I assume you meant CodeReview.SE? – Monotony

Correct. codereview.stackexchange.com/questions/tagged/vba – Nedi

So PSDataStageCals has just 6000 rows? What is PSDUDateRow? A single number, or an entire row comprising of multiple columns? Are there formulas in the file that reference PSDataStageCals? (If so, these formulas will be recalculated because you are adding a new ListRow, and if they are computationally expensive or are at the start of long calculation chains then this could explain most of your delay). Do you have volatile functions in your file such as OFFSET, NOW, TODAY, or INDIRECT? – Infatuate

Answer 1

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

Reagan

Answer 2

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.

Instigation

Answer 3

In addition to the tweaks suggested by Storax, your code is slow because you are bringing data cell-by-cell over the Excel/VBA divide.

Furthermore, you can radically speed up your MATCH function by using the Binary version of it. Have a read of http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/ and also try to minimise the amount of individual transfers you do across the Excel/VBA divide by either performing the lookups entirely within the Excel sheet (by using VBA to write the formula in the sheet and execute it there) or by bringing all the data into VBA in one go using variant arrays, performing your logic, and then by dumping it back in one go. Google "Efficient way to transfer data between Excel and VBA" or something similar. Also check out any articles from Charles Williams on the subject.

Infatuate

Answer 4

I don't see anything wrong with your code. Perhaps the Workbook itself is the culprit. Is it becoming huge and slow to open ? If yes, try searching for 'cleanup excel file'.
Some results I found:
https://excelfilecleaner.codeplex.com/
https://support.microsoft.com/en-us/help/3070372/how-to-clean-up-an-excel-workbook-so-that-it-uses-less-memory

Tensimeter

Answer 5

When crunching large chunks of data in Excel that requires frequent referencing of cells, it’s always much much faster to copy the data to an array (copy the entire worksheet if necessary), process the data within the array, and then write back to the worksheet if necessary. Copying data from worksheet to array is a one line command that is very very fast. Same with array to worksheet. Relatively speaking, referencing cells is a very time consuming process compared with referencing elements of an array.

Blessington