VBA:写入日志文件的子程序

Question

我在工作簿中定义了一组宏，我想向用户提供在日志文件中记录与这些宏相关的事件的选项。

我通过在 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

然后，我创建了一个过程，可用于向该对象写入参数，并将其存储在其自己的模块中:

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

不幸的是，这不起作用，我不知道为什么:即使我不包含 obj 作为函数的参数(因为 log和 logWrite 被创建为全局变量)，我无法 Call WriteLog("String here.") 或 Call WriteLog(log, "String here .") 没有错误(编译错误:参数不可选。)

是否可以让这样的 Sub() 工作，以便我可以从工作簿中的任何位置调用它(例如，在用户窗体中按下按钮后)，而无需在每个模块中定义一个新的 Scripting.FileSystemObject 吗？

Answer 1

我认为您可以通过对代码进行一些小的更改来解决您的问题。我尝试了以下设置:

记录器模块:

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 

本工作簿:

Private Sub Workbook_Open()
    initLog
End Sub