VBA: Sub to Write to a Log File

Question

I have a set of macros defined in my workbook, and I'd like to offer the user the option to log events related to those macros in a log file.

I initiate the log by creating the following in ThisWorkbook:

Public writeLog as Boolean
Public logWrite as Object
Public log as Object
Private Sub Worksheet_Open()
    Dim prompt as Integer
    prompt = MsgBox("Would you like to log events for this session?", vbYesNo, "Log Events?")
    If prompt Then
        writeLog = True
        Set logWrite = CreateObject("Scripting.FileSystemObject")
        Set log = logWrite.CreateTextFile("C:/TEST.txt", False)
    Else
        writeLog = False
    End If
End Sub

I then created a procedure that I can use to write an argument to this object, which I've stored in its own module:

Public Sub PrintLog(obj as Object, argument as String)
    If writeLog = True Then
        obj.WriteLine argument
    End If
End Sub

Unfortunately, this doesn't work, and I'm not sure why: even if I don't include obj as an argument to the function (since log and logWrite were created as global variables), I'm not able to Call WriteLog("String here.") or Call WriteLog(log, "String here.") without an error (Compile Error: Argument Not Optional.)

Is it possible to get such a Sub() to work, so that I can call it from anywhere in the workbook (after a button is pressed in a userform, for example) without having to define a new Scripting.FileSystemObject in every module?

Answer 1

I think that you can solve your problem by making some minor changes to your code. I tried the following setup:

logger module:

Option Explicit

Private log As Object

Public Sub initLog()

    Dim prompt As VbMsgBoxResult
    Dim fso As Object

    prompt = MsgBox("Would you like to log events for this session?", vbYesNo, "Log Events?")

    If prompt = vbYes Then
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set log = fso.CreateTextFile("C:/TEST.txt", False)
    End If

End Sub

Public Sub PrintLog(argument As String)
    If Not log Is Nothing Then
        log.WriteLine argument
    End If
End Sub

Public Sub yadda()
    'test
    PrintLog "yadda"
End Sub 

ThisWorkbook:

Private Sub Workbook_Open()
    initLog
End Sub