While building a moderate sized Excel application where many macros called each other in sequences determined by user actions, I learned that my habit of setting Excel's environment back to its "normal" state before exiting a macro can cause problems to a macro that has called another one. That is if the calling macro has set calculations to manual and the sub it calls returns this to automatic as it finishes, then the calling sub has to set the reset parameters back to the state it needs them to be in for its subsequent execution.
The need to reinitialize the changed parameter settings again in the calling macro frequently was signaled by the code producing unexpected results (sometimes referred to as "errors" by users).
A solution is to capture the current state of commonly modified environmental parameters at the start of each macro and then reset them to these values when the macro finishes. This way a calling macro will no have its settings changed by a macro that it calls.
Since Excel's VBA variables declared inside of a procedure have a scope limited to that procedure, the same code can be used for all macros and Excel will keep track of each set separately.
Inserting the Dim statements adn first five lines of the following code at the start of each macro and the final five lines immediately prior to macro exit should return Excel to the environmental condition it was in when the macro was called. If you never use one of these parameters (e.g, IgnoreRemoteRequests) then simply omit its code. Hope you find it useful.
P.S. As Reafidy noted in his thread of a week ago in this forum, if your macro can exit in multiple ways, make sure the "return to macro entry" code runs prior to any of the macro exit possibilites.
http://www.ozgrid.com/forum/showthread.php?t=92290
Sub RestoreStetEnvironment()
Dim StetCalcs As Long, StetScnUpdate As Boolean, StetEvents As Boolean
Dim StetAlerts As Boolean, StetRemote As Boolean
StetCalcs = Application.Calculation
StetScnUpdate = Application.ScreenUpdating
StetEvents = Application.EnableEvents
StetAlerts = Application.DisplayAlerts
StetRemote = Application.IgnoreRemoteRequests
' **********************
' Your Macro Code
' **********************
Application.Calculation = StetCalcs
Application.ScreenUpdating = StetScnUpdate
Application.EnableEvents = StetEvents
Application.DisplayAlerts = StetAlerts
Application.IgnoreRemoteRequests = StetRemote
End Sub
Display More