VBA:要写入日志文件的Sub
我的工作簿中定义了一组宏，我希望为用户提供在日志文件中记录与这些宏相关的事件的选项。
我通过在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.")中不出错(Compile Error: Argument Not Optional.)
有没有可能让这样的Sub()工作，这样我就可以从工作簿中的任何地方调用它(例如，在用户表单中按下按钮之后)，而不必在每个模块中定义一个新的Scripting.FileSystemObject？
回答 3
Stack Overflow用户
发布于 2014-03-26 22:18:31
我认为你可以通过对你的代码做一些小的修改来解决你的问题。我尝试了以下设置：
记录器模块：
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 SubStack Overflow用户
发布于 2014-03-26 04:16:17
我相信你会遇到问题，因为writeLog已经作为布尔值存在了。错误应该是弹出“检测到不明确的名称”
尝试以下操作：
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编辑:在WriteLog (或PrintLog)中将参数设为可选，以便进一步测试
Stack Overflow用户
发布于 2021-01-24 18:42:21
' Write to a log file using Separator and Array of variant Parameters
' Auto generate the file
' USE EndLog to close
'use:
' PrintLog vbtab, "one", 2, 3
' PrintLog vbtab, "Apple","Windows","Linux","Android","Commodore","Amiga","Spectrum"
' EndLog
' Generate a csv file:
' PrintLog ";", rst!ID, rst!Name
Private FileLog As Object
Private fso As Object
Const DEBUG_LOG_FILE = "C:\log.txt"
Public Sub PrintLog(ByVal Separator As String, ParamArray Arguments() As Variant)
Dim ele As Variant
Dim line As String
If FileLog Is Nothing Then
Set fso = CreateObject("Scripting.FileSystemObject")
Set FileLog = fso.CreateTextFile(DEBUG_LOG_FILE, True, True)
End If
line = CStr(Now()) ' Print Timestamp
For Each ele In Arguments
If line > "" Then line = line & Separator
line = line & CStr(ele)
Next
If line > "" Then FileLog.WriteLine line
End Sub
Public Sub EndLog()
On Error Resume Next
FileLog.Close
Set FileLog = Nothing
Set fso = Nothing
On Error GoTo 0
End Subhttps://stackoverflow.com/questions/22645091
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号