View Full Version : Anyone have any idea on this code to speed up macros

01-05-2019, 06:21 PM
application.Visible = False 'Run Code Application.Visible = True

can this code above be added to the below code

Public CalcState As Long
Public EventState As Boolean
Public PageBreakState As Boolean

Sub OptimizeCode_Begin()

Application.ScreenUpdating = False

EventState = Application.EnableEvents
Application.EnableEvents = False

CalcState = Application.Calculation
Application.Calculation = xlCalculationManual

PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False

End Sub

Second Piece of VBA Code
Sub OptimizeCode_End()

ActiveSheet.DisplayPageBreaks = PageBreakState
Application.Calculation = CalcState
Application.EnableEvents = EventState
Application.ScreenUpdating = True

01-05-2019, 11:13 PM
Your 'code below' already has:
Application.ScreenUpdating = False
in the OptimizeCode_Begin sub, and:

Application.ScreenUpdating = True
in the OptimizeCode_End sub. What more do you expect?

01-06-2019, 10:00 AM
I thought the application.Visible= False was different from the Application.screenUpdating=False and thought by adding this would add extra speed To macros

01-06-2019, 01:18 PM
My bad.

Be careful with Application.Visible= False; if your code crashes or needs you to respond to a dialogue box, you'll be left with a hidden instance of Excel running in the background, with open workbooks and you'll need to use Task Manager to kill it, losing any unsaved work.

In any event, Application.Visible= False is unlikely to make a material difference to the execution speed; it may actually slow it down slightly, as Windows has to hide Excel and repaint the screen with whatever you'd see if you minimised the Excel window,

01-06-2019, 09:07 PM
Thank you, I’ll be sure not to use it!

01-06-2019, 09:18 PM
It has its uses (e.g. when automating one application from another and you want the spawned application to run in the background).