speed up vba macro

B-Man

Board Regular
Joined
Dec 29, 2012
Messages
185
Office Version
  1. 2019
Platform
  1. Windows
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:
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>
and this at the end?
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>
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top