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
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
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
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;">
and this at the end?
removed 99% .select .copy .paste etc
turned what i can into
Code:
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
Code:
[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]
Code:
[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
Code:
[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;">
Code:
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
</code>
Code:
<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>