speed up vba macro

[B-Man]

im trying to speed up my vba macro. i optimized my code as best i can.
removed 99% .select .copy .paste etc
turned what i can into

with ws1
.bla
.bla
.bla
end with

code is 1000 times neater but if anything runs slower...

i then found this code below

https://www.thespreadsheetguru.com/...o-performance-and-prevent-slow-code-execution

[COLOR=#00007F][FONT='inherit']Public[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] CalcState [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']As[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Long[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']Public[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] EventState [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']As[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Boolean[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']Public[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] PageBreakState [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']As[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Boolean[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']Sub[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] OptimizeCode_Begin()[/FONT][/COLOR]

[COLOR=#403F41][FONT='inherit']Application.ScreenUpdating = [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']False[/FONT][/COLOR]

[COLOR=#403F41][FONT='inherit']EventState = Application.EnableEvents[/FONT][/COLOR]
[COLOR=#403F41][FONT='inherit']Application.EnableEvents = [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']False[/FONT][/COLOR]

[COLOR=#403F41][FONT='inherit']CalcState = Application.Calculation[/FONT][/COLOR]
[COLOR=#403F41][FONT='inherit']Application.Calculation = xlCalculationManual[/FONT][/COLOR]

[COLOR=#403F41][FONT='inherit']PageBreakState = ActiveSheet.DisplayPageBreaks[/FONT][/COLOR]
[COLOR=#403F41][FONT='inherit']ActiveSheet.DisplayPageBreaks = [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']False[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']End[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Sub[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']Sub[/FONT][/COLOR][COLOR=#403F41][FONT='inherit'] OptimizeCode_End()[/FONT][/COLOR]

[COLOR=#403F41][FONT='inherit']ActiveSheet.DisplayPageBreaks = PageBreakState[/FONT][/COLOR]
[COLOR=#403F41][FONT='inherit']Application.Calculation = CalcState[/FONT][/COLOR]
[COLOR=#403F41][FONT='inherit']Application.EnableEvents = EventState[/FONT][/COLOR]
[COLOR=#403F41][FONT='inherit']Application.ScreenUpdating = [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']True[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']End[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Sub[/FONT][/COLOR]

Turning calculations off does improve the speed quite a bit but the calculations dont turn back on im guessing i have done something wrong with the

[COLOR=#00007F]Public[/COLOR][COLOR=#403F41] CalcState [/COLOR][COLOR=#00007F]As[/COLOR][COLOR=#00007F]Long[/COLOR]
[COLOR=#00007F]Public[/COLOR][COLOR=#403F41] EventState [/COLOR][COLOR=#00007F]As[/COLOR][COLOR=#00007F]Boolean[/COLOR]
[COLOR=#00007F]Public[/COLOR][COLOR=#403F41] PageBreakState [/COLOR][COLOR=#00007F]As[/COLOR][COLOR=#00007F]Boolean[/COLOR]

my question is whats the difference running the code above and just straight out using

<code style="box-sizing: border-box; margin: 0px; padding: 0px; border: 0px; font-size: 12px; line-height: 1; font-family: monaco, menlo, monospace;">

Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
</code>

and this at the end?

<code style="box-sizing: border-box; margin: 0px; padding: 0px; border: 0px; font-size: 12px; line-height: 1; font-family: monaco, menlo, monospace;">Application.ScreenUpdating = True
ActiveSheet.DisplayPageBreaks = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic</code>

 

[jmacleary]

Hi. The main difference (which might be significant for you) is that the code you found will reinstate the previous setting, whereas your code explicitly sets things to True/Automatic.