Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Restoring application settings at end of sub
Having had an unfortunate experience where I caused a user's worksheet
to calculate against his wishes, I've been a little paranoid about restoring settings when I've finished. Thinking about the incident, it ocurred to me that I could also be turning calculation back on inadvertently when subs call each other in a sequence I had not originally considered when writing them. I've therefore taken recently to saving and restoring in the same way in each procedure, which looks something like this: '''''''''''''''' Sub heavyCalcs() Dim sav As fastStore sav = setFast() ' 'do lots of clever stuff 'and use statusBar occasionally to show something's happening ' resetFast sav End Sub '''''''''''''''' The rest of the code appears later in this post, but basically it saves .screenUpdating, .calculation AND .statusBar to be restored at the end, then turns off screenUpdating and calculation. I'd be interested in any comments. Is there anything else I should be saving? Is there a more sensible way of going about this (am I re-inventing the wheel)? Am I setting traps for myself I haven't thought of? Have I just gone completely over the top after one bad experience? :-) The code from my module looks like this: '''''''''''''''' Option Explicit 'Steve Garman's setFastStuff Public Type fastStore screenUpdating As Boolean calculation As XlCalculation statusBar As Variant End Type Public Function setFast() As fastStore With Application setFast.screenUpdating = .screenUpdating setFast.calculation = .calculation setFast.statusBar = .statusBar .screenUpdating = False .calculation = xlCalculationManual End With End Function Public Sub resetFast(sv As fastStore) With Application .screenUpdating = sv.screenUpdating .calculation = sv.calculation If sv.statusBar = "FALSE" Then .statusBar = False Else .statusBar = sv.statusBar End If End With End Sub '''''''''''''''' |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Restoring application settings at end of sub
Steve,
I would say that there is nothing wrong with the approach, although I would suggest that Status bar and ScreenUpdating are over the top. Users cannot set ScreenUpdating from Excel, so you should always perhaps ensure it is true (ditto DisplayAlerts), and set StatusBar to standard. If you want to go the whole hog, you could look at ToolsOptions and see what other things the user can change (Formula Bar, zero display, gridlines, etc.) and think about these. Personally, I would just save and reset any items that I know I will change, such as calculation mode and commandbars. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve Garman" wrote in message ... Having had an unfortunate experience where I caused a user's worksheet to calculate against his wishes, I've been a little paranoid about restoring settings when I've finished. Thinking about the incident, it ocurred to me that I could also be turning calculation back on inadvertently when subs call each other in a sequence I had not originally considered when writing them. I've therefore taken recently to saving and restoring in the same way in each procedure, which looks something like this: '''''''''''''''' Sub heavyCalcs() Dim sav As fastStore sav = setFast() ' 'do lots of clever stuff 'and use statusBar occasionally to show something's happening ' resetFast sav End Sub '''''''''''''''' The rest of the code appears later in this post, but basically it saves .screenUpdating, .calculation AND .statusBar to be restored at the end, then turns off screenUpdating and calculation. I'd be interested in any comments. Is there anything else I should be saving? Is there a more sensible way of going about this (am I re-inventing the wheel)? Am I setting traps for myself I haven't thought of? Have I just gone completely over the top after one bad experience? :-) The code from my module looks like this: '''''''''''''''' Option Explicit 'Steve Garman's setFastStuff Public Type fastStore screenUpdating As Boolean calculation As XlCalculation statusBar As Variant End Type Public Function setFast() As fastStore With Application setFast.screenUpdating = .screenUpdating setFast.calculation = .calculation setFast.statusBar = .statusBar .screenUpdating = False .calculation = xlCalculationManual End With End Function Public Sub resetFast(sv As fastStore) With Application .screenUpdating = sv.screenUpdating .calculation = sv.calculation If sv.statusBar = "FALSE" Then .statusBar = False Else .statusBar = sv.statusBar End If End With End Sub '''''''''''''''' |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restoring Gridlines | Excel Worksheet Functions | |||
Restoring Excel Fkeys settings to original values? | Excel Discussion (Misc queries) | |||
Restoring | Excel Discussion (Misc queries) | |||
macro to close excel application other than application.quit | Excel Programming | |||
Restoring Excel menu bar settings that were changed by code | Excel Programming |