I refactored my code reading @this post and @jasoninvegas comments. I'll try and elaborate later tonight.
'---------------------------------------------------------------------------------------
' Procedure : SaveAppState
' Author : Thomas Inzina
' Date : 12/29/2017
' Purpose1 : To Save and Restore Excel's Application State one or more times
' Purpose2 : To Log State changes and Execution Time between calls
'-----------------------------------------Parameters------------------------------------
' Restore : Value->False or Missing: Saves the State Value-> True Restores the State
' Label : Adding a Label with Restore->False logs start time and Current State to be
' ' Saved. With Restore->True logs Execution Time, Current State and Restored
' ' State values
'------------------------------------------Example--------------------------------------
' SaveAppState False, "Start Test1" ' Saves the Application State
' Application.ScreenUpdating = False ' Change the state
' 'Code Here ' Do Work
' SaveAppState True, "End Test1" ' Restores then Application State
'---------------------------------------------------------------------------------------
Public Sub SaveAppState(Optional Restore As Boolean, Optional Label As String)
Const TIME = 0, CALC = 1, UPDATING = 2, EVENTS = 3, LINETEMPLATE = "| | | |"
Static settings As Object
Dim values() As Variant
Dim line As String
If settings Is Nothing Then Set settings = CreateObject("System.Collections.Stack")
With Application
If Restore Then
If settings.Count = 0 Then
MsgBoxErr.Raise 5, "Test", "The Stack is Empty"Empty " & vbCrLf & Label,"An vbCriticalApplication State must be Saved first."
Exit Sub
End If
values = settings.Pop
If Len(Label) > 0 Then
Debug.Print Label; "-> Execution Time: "; Format((Timer - values(TIME)) / 86400, "hh:mm:ss")
Debug.Print String(Len(LINETEMPLATE), "-")
line = LINETEMPLATE: Mid(line, 6) = "Settings": Mid(line, 2123) = "Old": Mid(line, 2934) = "New": Debug.Print line
line = LINETEMPLATE: Mid(line, 2) = "Calculation": Mid(line, 1921) = .Calculation: Mid(line, 3133) = values(CALC): Debug.Print line
line = LINETEMPLATE: Mid(line, 2) = "ScreenUpdating": Mid(line, 1921) = .ScreenUpdating: Mid(line, 3133) = values(UPDATING): Debug.Print line
line = LINETEMPLATE: Mid(line, 2) = "EnableEvents": Mid(line, 1921) = .EnableEvents: Mid(line, 3133) = values(EVENTS): Debug.Print line
Debug.Print String(Len(LINETEMPLATE), "-")
End If
.Calculation = values(CALC)
.ScreenUpdating = values(UPDATING)
.EnableEvents = values(EVENTS)
Else
If Len(Label) > 0 Then
Debug.Print Label; ": Started "; Format(Now, "hh:mm:ss")
Debug.Print String(Len(LINETEMPLATE), "-")
line = LINETEMPLATE: Mid(line, 6) = "Settings": Mid(line, 1921) = "Original": Debug.Print line
line = LINETEMPLATE: Mid(line, 2) = "Calculation": Mid(line, 1922) = .Calculation: Debug.Print line
line = LINETEMPLATE: Mid(line, 2) = "ScreenUpdating": Mid(line, 1922) = .ScreenUpdating: Debug.Print line
line = LINETEMPLATE: Mid(line, 2) = "EnableEvents": Mid(line, 1922) = .EnableEvents: Debug.Print line
Debug.Print String(Len(LINETEMPLATE), "-")
End If
settings.Push Array(CDbl(Timer), .Calculation, .ScreenUpdating, .EnableEvents)
End If
End With
End Sub
Usage
Sub Demo1()
With Application
SaveAppState Restore:=False, Label:="Start Demo1"
.ScreenUpdating = False: .Calculation = xlCalculationManual: .EnableEvents = False
.Wait (Now + TimeValue("0:00:02"))
SaveAppState Restore:=True, Label:="End Demo1"
End With
End Sub
Immediate Window Log
Start Demo1: Started 03:18:37
-----------------------------------------
| Settings | Original | |
|Calculation | -4105 | |
|ScreenUpdating | True | |
|EnableEvents | True | |
-----------------------------------------
End Demo1-> Execution Time: 00:00:01
-----------------------------------------
| Settings | Old | New |
|Calculation | -4135 | -4105 |
|ScreenUpdating | False | True |
|EnableEvents | False | True |
-----------------------------------------