I’ve just posted the code I use to speed up VBA execution on the codematic site here.
its just a pair of routines to turn events, screenupdating and calc off. The only thing it does which is vaguely interesting is try to maintain the calc settings as they were prior to code execution.
Public Sub speed()
On Error Resume Next
If Not mbInSpeed Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
mlCalcStatus = Application.Calculation
Application.Calculation = xlCalculationManual
mbInSpeed = True
Else
‘we are already in speed – don’t do the settings again
End If
End Sub
I was ignoring remote requests too but that is bit of a pain if Excel crashes.
Is there anything you would add or remove?
cheers
Simon
Smurf on Spreadsheets 
Thursday, 24th September, 2009 at 8:20 am |
On the Sub unspeed()
make Screen updating = True after you turn the Calc back to automatic…
On calculation intensive files I have noticed this to be slightly faster…
Thursday, 24th September, 2009 at 8:28 am |
I can’t think of any other general purpose speed-ups. As for Excel state management, I’ve written several subs, and I usually end up making them toggle operations, such as:
Public Sub ToggleSpeed(bFast as Boolean)
That way, my error handlers can toggle everything back to its original state as a fail safe.
Thursday, 24th September, 2009 at 9:05 am |
I prefer using just one routine with convoluted logic.
Sub foo(Optional oparg As Variant)
Static op As Boolean
If IsMissing(oparg) Then
op = Not op
ElseIf VarType(oparg) vbBoolean Then
‘throw a runtime error? or . . .
Exit Sub
ElseIf op oparg Then _
op = oparg
Else
Exit Sub
End If
Debug.Print IIf(op, “on”, “off”)
End Sub
Sub bar()
Debug.Print “1 on”,: foo
Debug.Print “2 off”,: foo
Debug.Print “3 on”,: foo True
Debug.Print “4 off”,: foo False
Debug.Print “5 on”,: foo
Debug.Print “6”: foo True
Debug.Print “7 off”,: foo
Debug.Print “8”: foo False
Debug.Print “9 on”,: foo
End
End Sub
Thursday, 24th September, 2009 at 9:24 am |
I prefer using a custom class for this stuff. The big advantage taking this approach is if my solution encounters and error or for some other reason fails to execute the code, the Class_Terminate code will always run when my instance of that class goes out of scope..
I’ve also implemented some methods for this class, so I have code like this:
Private Sub Class_Initialize()
Me.Save
Me.SpeedUp
End Sub
Private Sub Class_Terminate()
Me.Restore
End Sub
What I love most about this approach is the reuse of code. I hardly never write stuff like “Application.Bla = False” anymore. All I have to do is (for example):
Dim objAppCfg as clsAppSettings
set objAppCfg = new clsAppSettings
‘My code goes here, previous settings are now saved
‘..more code
set objAppCfg = Nothing
‘Settings are now restored. No biggie if I forget this line of code
‘the settings are automatically restored when the variable
‘objAppCfg goes out of scope :-)
Over time the class has been expanded to support changing DecimalSeparators, set default settings etc.
I do not have to care about checking if “we are in speedup” or not, instantiating new objects of this class in subroutines doesn’t hurt.
What do you guys think? From my perspective this is pretty elegant.
Thursday, 24th September, 2009 at 9:31 am |
I’ve wrapped up something similar in a little class called ScreenUpdateDisabler:
Private blnScreenUpdatingStatus As Boolean
Private m_CalculationStatus As XlCalculation
Private Sub Class_Initialize()
‘ Capture the current status of the screen updating so that we can return to this state later
blnScreenUpdatingStatus = Application.ScreenUpdating
m_CalculationStatus = Application.Calculation
Disable
End Sub
Public Sub Disable()
‘ Turn off the screen updating
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
End Sub
Public Sub Restore()
‘ Restore the screen updating to the original status
Application.ScreenUpdating = blnScreenUpdatingStatus
Application.Calculation = m_CalculationStatus
End Sub
Private Sub Class_Terminate()
‘ restore the screen updating if the user forgets to
Call Restore
End Sub
Then I can use it like this:
Sub NeedsToGoFast()
Dim disabler as ScreenUpdateDisabler
set disabler = new ScreenUpdateDisabler()
‘ stuff that needs to go fast
set disabler = nothing
End Sub
This way every sub that needs to can make sure the “turbo button” is pressed, and makes sure that the state of the “button” at the end is the same as it was at the start so that nothing else needs to be affected.
Thursday, 24th September, 2009 at 9:32 am |
Thinking about it, I seem to remember that I had to call the Disable and Restore methods explicitly (it was a long time ago that I last used this).
Something like this:
Sub NeedsToGoFast()
Dim disabler as ScreenUpdateDisabler
set disabler = new ScreenUpdateDisabler()
disabler.Disable
‘ stuff that needs to go fast
disabler.Restore
set disabler = nothing
End Sub
Thursday, 24th September, 2009 at 9:33 am |
Hehe, I beat you to it Samuel Jack :-P
Thursday, 24th September, 2009 at 9:35 am |
Do you see much benefit from EnableEvents = False? I’ve never bothered with it.
I really like the class idea and plan to investigate/steal it. I’ve also used a single Sub to date so I can say “Screen OFF” and “Screen ONN”, with OFF and ONN defined as Boolean constants. Then when the (seemingly inevitable) crash happens I just Screen ONN* in the Immediate window. Once I’ve remembered why the worksheet isn’t updating, that is.
* With a mental image of Johnny Storm while doing so…
Thursday, 24th September, 2009 at 10:15 am |
Mike
Enable events is handy in the wild to stop other peoples kippery code triggering their ‘cup of tea’ routine everytime you try and insert a value in some unrelated cell.
Yep I get caught out with the non update thing too, but sadly I rarely have such exciting images in mind, more like paint drying on the projects I do.
I like the clear up/terminate aspect of using a class but I think they are unwieldy to use in VBA, and the fairly rare crashes that I have (excel classique) seem take everything straight down so the terminate would not fire.
All this stuff seems to have a much bigger impact in 2007
Thursday, 24th September, 2009 at 11:28 am |
See http://msmvps.com/blogs/xldynamic/archive/2009/09/12/what-s-my-type.aspx
Thursday, 24th September, 2009 at 12:09 pm |
Since I have a lot of routines that call each other at various places, I have this kind of construction, which makes sure screen updating (say) is off, but doesn’t turn it back on unless it was on to start with.
Sub FOO()
Dim bScreenUpdating As Boolean
bScreenUpdating = Application.ScreenUpdating
If bScreenUpdating Then Application.ScreenUpdating = False
‘ rest of my code
If bScreenUpdating Then Application.ScreenUpdating = True
End Sub
Thursday, 24th September, 2009 at 12:28 pm |
Here’s another list of time-eaters:
http://blogs.msdn.com/excel/archive/2009/03/12/excel-vba-performance-coding-best-practices.aspx
Doesn’t follow Sam’s advice though.
…mrt
Thursday, 24th September, 2009 at 2:43 pm |
Not directly related to speed, but using…
Application.EnableCancelKey = xlErrorHandler
would be good practice.
Thursday, 24th September, 2009 at 3:56 pm |
Will turning off the display of page breaks via VBA also speed execution?
Thursday, 24th September, 2009 at 5:54 pm |
Jerry –
Per the link above, it does:
‘turn off some Excel functionality so your code runs faster
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False ‘note this is a sheet-level setting
‘>>your code goes here<<
…mrt
Friday, 25th September, 2009 at 12:37 am |
I never have it tiurned on so never noticed until using someone elses machine but background error checking will also slow down VBA routines:
Application.ErrorCheckingOptions.BackgroundChecking = False
Friday, 25th September, 2009 at 1:47 am |
Application.Screenupdating = False and Application.Calculation = xlCalculationManual are major heroes in my work.
As far as implementing them in “Classes” or other slick processes I personally just turn them on and off when I need them – simple enough if not real “sexy” :-).
Biggus
Tuesday, 29th September, 2009 at 6:55 pm |
Hi S….
Been a while
Saw this on the net recently, made me laugh
The only way to accelerate a Mac is at 9.8 msec2
get it!! haha, so the only way to accelerate xl is ….
seriously thou, similar to yours, one diff, just store the USERS current settings (like calc mode, etc) in local, module or class level variables, do the work and then switch back – that way diff users don’t whinge (sic?) maybe even one method that is passed a bool to toggle between the run time settings and the users interface settings.
on some side notes seeing as its been a while
1. living in old london town now
2. won’t be doing much VBA long term
3. lots of C# now, using VS2008 but VS 2010 looks very good for XL re interop (its almost exactly like VBA syntax but in C#)
4. completlely binned VB.Net, won’t even touch it
been doing C# threading for that last 2 weeks, haha, what a trip man – I love it. Brains been single threaded for that last 10 years, now its multi-threaded.
cheers man
Tuesday, 29th September, 2009 at 7:42 pm |
Well done for getting out of it Steve.
I was just discussing a job in Ed today – they were paying max 200 per day – yeah right!
I agree VS is the way to go