Forum Discussion

Hershner04's avatar
Hershner04
Copper Contributor
Sep 22, 2021

Excel Macro Runs *PAINFULLY* Slow

I have an issue running an Excel Macro. I have read a lot of threads and tried all of the suggestions but no change in performance. I have run the same file/macro on 2 machines, details below. No mat...
excel
Macros and VBA
office 365
Juliano-Petrukio's avatar
Juliano-Petrukio
Bronze Contributor
Sep 22, 2021

Hershner04 

There are many approaches to make a VBA code faster.

 

Consider inittially

Sub YourRoutine()
    BeforeYourCodeStarts
    
    '....
    '....
    
    AfterYourCodeFinishes
End Sub

Sub BeforeYourCodeStarts()
    'Turn off Screen Updating
    Application.ScreenUpdating = False
    'Turn off ‘Automatic Calculations’
    Application.Calculation = xlCalculationManual
    'Disable Events
    Application.EnableEvents = False
End Sub

Sub AfterYourCodeFinishes()
    'Turn on Screen Updating
    Application.ScreenUpdating = True
    'Turn on ‘Automatic Calculations’
    Application.Calculation = xlCalculationAutomatic
    'Enable Events
    Application.EnableEvents = True
End Sub

I also recommend you read some https://www.soa.org/news-and-publications/newsletters/compact/2012/january/com-2012-iss42/excel-vba-speed-and-efficiency/

Hershner04's avatar
Hershner04
Copper Contributor
Sep 22, 2021
Thank you for responding to my problem. The code is not the issue. Same file, same code on two different machines. Original machine has been consistently running 11 seconds per iteration. New machine, which by the specs is far superior, runs 24 seconds per iteration. Once I address the performance of the file, I might look to optimize the code based on my needs, but I’m not there quite yet.

Resources