State management in Excel

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

About these ads

19 Responses to “State management in Excel”

  1. sam Says:

    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…

  2. Nick Hebb Says:

    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.

  3. Harlan Grove Says:

    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

  4. Peder Schmedling Says:

    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.

  5. Samuel Jack Says:

    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.

  6. Samuel Jack Says:

    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

  7. Peder Schmedling Says:

    Hehe, I beat you to it Samuel Jack :-P

  8. mikewoodhouse Says:

    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…

  9. Simon Says:

    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

  10. Bob Phillips Says:

    See http://msmvps.com/blogs/xldynamic/archive/2009/09/12/what-s-my-type.aspx

  11. Jon Peltier Says:

    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

  12. Michael Says:

    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

  13. Jim Cone Says:

    Not directly related to speed, but using…

    Application.EnableCancelKey = xlErrorHandler

    would be good practice.

  14. Jerry Betz Says:

    Will turning off the display of page breaks via VBA also speed execution?

  15. Michael Says:

    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

  16. Matt Says:

    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

  17. Dick Moffat Says:

    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

  18. Steve Says:

    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

  19. Simon Says:

    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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

Join 64 other followers

%d bloggers like this: