Solved - Sub Application_Quit() clears the value of defined variable

Question

A stripped down idea of what I'm trying to accomplish would be to have a message box popup when Outlook is closed that tells you what time Outlook was opened and what time it was closed. Here is the code I am using to try to accomplish this:

Private startTime As String
Private endTime As String

Private Sub Application_Startup()
    startTime = CStr(TimeValue(Now))
End Sub

Private Sub Application_Quit()
    endTime = CStr(TimeValue(Now))

    MsgBox _
        "Session started at " + startTime + vbNewLine + _
        "Session ended at " + endTime, _
        vbOkOnly + vbInformation, _
        "Session Information"
End Sub

This is what I expect to get:

However, the issue I'm having is that my variables are cleared when Application_Quit() is triggered. I receive this message box instead:

I know that startTime is being given a value on Application_Startup(). When I add the following line of code to the end of Application_Startup() I receive the below message box.

    MsgBox _
        "Session started at " + startTime, _
        vbOkOnly + vbInformation, _
        "Session Information"

Why does Application_Quit() force startTime = "", and is there any way around it?

Solved - Thanks to Adirmola - Using FSO to create a temporary text file removes the need for Global variables startTime and endTime as their values are stored in the temporary file and recalled during Application_Quit().

Private FSO As Object
Private oFile As Object

Private Sub Application_Startup()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = FSO.CreateTextFile("C:\Users\username\Documents\Outlook Files\temp.txt")
    
    oFile.WriteLine "Session started at " + CStr(TimeValue(Now))
    oFile.Close
    
    Set oFile = Nothing
    Set FSO = Nothing
End Sub

Private Sub Application_Quit()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = FSO.OpenTextFile("C:\Users\username\Documents\Outlook Files\temp.txt")
    
    MsgBox _
        oFile.readline + vbNewLine + _
        "Session ended at " + CStr(TimeValue(Now)), _
        vbOKOnly + vbInformation, _
        "Session Information"
    
    oFile.Close
    Set oFile = Nothing
    FSO.DeleteFile ("C:\Users\username\Documents\Outlook Files\temp.txt")
    Set FSO = Nothing
End Sub

Can you provide a bit more information? Are these running in a module, separate modules, at the application level, etc? — TylerH, Feb 17 '19 at 17:18
The issue is the global variable lifetime - [see here](https://stackoverflow.com/questions/7041138/what-is-the-lifetime-of-a-global-variable-in-excel-vba). One way I have in mind to solve it is by using text file to temporary store the start time. — Adirmola, Feb 17 '19 at 17:25
@TylerH The subs are not explicitly called from anywhere. As far as I am aware 'Application_Startup()' and 'Application_Quit()' are built in functions that are executed when the Outlook application is opened and when it is closed respectively. They are running at the application level, placed in 'ThisOutlookSession'. [Screenshot](https://i.imgur.com/HYKcxI8.png) — Hook Hogan, Feb 17 '19 at 17:28
SO is a question and answer site. If you've discovered your own answer, it should posted as a self-answer instead of being edited into the question. — Comintern, Feb 18 '19 at 00:58

score 0 · Accepted Answer · answered Feb 18 '19 at 17:00

Using FSO to create a temporary text file removes the need for Global variables startTime and endTime as their values are stored in the temporary file and recalled during Application_Quit().

Private FSO As Object
Private oFile As Object

Private Sub Application_Startup()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = FSO.CreateTextFile("C:\Users\username\Documents\Outlook Files\temp.txt")

    oFile.WriteLine "Session started at " + CStr(TimeValue(Now))
    oFile.Close

    Set oFile = Nothing
    Set FSO = Nothing
End Sub

Private Sub Application_Quit()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = FSO.OpenTextFile("C:\Users\username\Documents\Outlook Files\temp.txt")

    MsgBox _
        oFile.readline + vbNewLine + _
        "Session ended at " + CStr(TimeValue(Now)), _
        vbOKOnly + vbInformation, _
        "Session Information"

    oFile.Close
    Set oFile = Nothing
    FSO.DeleteFile ("C:\Users\username\Documents\Outlook Files\temp.txt")
    Set FSO = Nothing
End Sub

Solved - Sub Application_Quit() clears the value of defined variable

1 Answers1