How To Speed Up VBA Code

How To Speed Up VBA Code

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

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

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

1. 사용하지 않는 좋은 코드를 작성합니다.Select,Activate,ActiveCell,Selectionetc - Excel VBA에서 Select를 사용하지 않는 방법

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)

• 그런 것 같습니다.Calculation항상 다음과 같이 설정해야 합니다.xlAutomatic, 필요한 한xlCalculationManual속도를 높이기 위해서는 코드를 리팩터하는 것이 좋습니다.게다가 수동 계산은 너무 위험합니다.
• 마찬가지입니다.Date1904- 항상 다음과 같이 설정됩니다.False.

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.

당신의 코드에는 이상이 없습니다.아마도 워크북 자체가 범인일 것입니다.점점 커져서 문을 여는 속도가 느려지고 있습니까?그렇다면 'cleanup excel file'을 검색해 보십시오.
발견한 몇 가지 결과:

셀을 자주 참조해야 하는 Excel의 대용량 데이터 청크를 처리할 때는 항상 데이터를 어레이에 복사하고(필요한 경우 전체 워크시트를 복사), 어레이 내에서 데이터를 처리한 다음 필요한 경우 워크시트에 다시 쓰는 것이 훨씬 빠릅니다.워크시트에서 배열로 데이터를 복사하는 것은 매우 빠른 한 줄 명령입니다.워크시트에 대한 배열도 마찬가지입니다.상대적으로 셀을 참조하는 것은 배열의 참조 요소에 비해 매우 많은 시간이 소요되는 프로세스입니다.

언급URL : https://stackoverflow.com/questions/47089741/how-to-speed-up-vba-code