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
''''''''''''''''

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
''''''''''''''''