PDA

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



estatefinds
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

macropod
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?

estatefinds
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

macropod
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,

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

macropod
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).