VBA：子写入日志文件

VBA: Sub to Write to a Log File

提问by Corcovado

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:

我通过在 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 objas an argument to the function (since logand logWritewere 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.)

不幸的是，这不起作用，我不知道为什么：即使我不obj作为参数包含在函数中（因为log并且logWrite是作为全局变量创建的），我也无法Call WriteLog("String here.")或Call WriteLog(log, "String here.")没有错误（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.FileSystemObjectin every module?

是否有可能让这样的 aSub()工作，以便我可以从工作簿中的任何地方调用它（例如，在用户窗体中按下按钮之后），而不必Scripting.FileSystemObject在每个模块中定义一个新的？

回答by kb_sou

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

回答by Jimmy Smith

I believe you're having issues as writeLog already exists as a boolean. Error should be popping up "Ambiguous name detected"

我相信您遇到了问题，因为 writeLog 已经作为布尔值存在。应该弹出错误“检测到不明确的名称”

Try the following,

尝试以下，

Public bLog 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
        bLog = True
        Set logWrite = CreateObject("Scripting.FileSystemObject")
        Set log = logWrite.CreateTextFile("C:/TEST.txt", False)
    Else
        bLog = False
    End If
End Sub

Public Sub WriteLog(Optional obj as Object, Optional argument as String)
    If bLog = True Then
        obj.WriteLine argument
    End If
End Sub

Edit: made parameters optional in WriteLog (or PrintLog) for further testing

编辑：在 WriteLog（或 PrintLog）中将参数设为可选以进行进一步测试