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/

Resources