Speed Up Macro's

[GreenWizard]

From time to time my macro's run relatively slow compared to normal. I realize the speed of an application is dependent on hardware, however are there any settings that can be modified to improve the speed of a macro or a spreadsheet in general? Thanks.

I'm running MS Excel 2013 on a Windows 7 machine.

Thanks!

 

[hiker95]

GreenWizard,

In order to assist you it would help us if you posted all of your macro code (for one of your macros) using code tags.

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[Vantom]

Sub...
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

Your code...

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
End Sub

https://www.soa.org/News-and-Publications/Newsletters/Compact/2012/january/com-2012-iss42-roper.aspx
http://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/
http://chandoo.org/wp/2012/03/27/75-excel-speeding-up-tips/

 

[hiker95]

Vantom,

When I clicked on your three links, I received the following File Not Found.

 

[Vantom]

https://www.soa.org/News-and-Publications/Newsletters/Compact/2012/january/com-2012-iss42-roper.aspx
Excel VBA Performance Coding Best Practices - Office Blogs
75 Excel Speeding up Tips - How to speed-up & optimize slow Excel workbooks? | Chandoo.org - Learn Microsoft Excel Online

 

[Rick Rothstein]

Sub...
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

Your code...

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
End Sub

You should not assume you know the values the user has for those settings (the current routine may be run as part of a series of macros in which the other macros assume a pre-set state different from what you assumed above). Safer might be to do what you posted this way...

[COLOR="#008000"]'  Declarations for variables used below[/COLOR]
[COLOR="#008000"]'  Add these to your own variable declarations[/COLOR]
Dim ScreenUpdateState As Boolean
Dim StatusBarState As Boolean
Dim CalcState As Long
Dim EventsState As Boolean
Dim DisplayPageBreakState As Boolean

[COLOR="#008000"]'  Save the current state of Excel settings.[/COLOR]
ScreenUpdateState = Application.ScreenUpdating
StatusBarState = Application.DisplayStatusBar
CalcState = Application.Calculation
EventsState = Application.EnableEvents
[COLOR="#008000"]'  Note: this is a sheet-level setting.[/COLOR]
DisplayPageBreakState = ActiveSheet.DisplayPageBreaks 

[COLOR="#008000"]'  Turn off Excel functionality to improve performance.[/COLOR]
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' Note: this is a sheet-level setting.
ActiveSheet.DisplayPageBreaks = False

[B][COLOR="#0000FF"]'  Insert your code here.[/COLOR][/B]

[COLOR="#008000"]'  Restore Excel settings to original state.[/COLOR]
Application.ScreenUpdating = ScreenUpdateState
Application.DisplayStatusBar = StatusBarState
Application.Calculation = CalcState
Application.EnableEvents = EventsState
[COLOR="#008000"]'  Note: this is a sheet-level setting[/COLOR]
ActiveSheet.DisplayPageBreaks = DisplayPageBreaksState