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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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,969
Messages
6,175,680
Members
452,667
Latest member
vanessavalentino83

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