VBA：Sub写入日志文件 [英] VBA: Sub to Write to a Log File

问题描述

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

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.

我通过在ThisWorkbook中创建以下内容来启动日志：

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

不幸的是，这不行，我不知道为什么：即使我不包括 obj 作为参数函数（因为 log 和 logWrite 被创建为全局变量），我不能调用WriteLog（String here）或调用WriteLog（log，String here。）没有错误（编译错误：参数不可选。）

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.)

是否可以获得这样一个 Sub（） / code>工作，所以我可以从工作簿中的任何地方（例如在用户形式按下按钮之后）调用它，而无需定义新的 Scripting.FileSystemObject 在每个模块中？

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?

推荐答案

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

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

记录器模块：

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：

ThisWorkbook:

Private Sub Workbook_Open()
    initLog
End Sub

这篇关于VBA：Sub写入日志文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！