如何在Excel中实现记录器

Question

我正在尝试为Excel实现记录器，它不会记录用户更改单元格值(谁在乎…)。

但是我的应用程序的内部动态。

:

1. 必须是基于表的。(因为用户记不住从奇怪的地方附加一些奇怪的文件…)
2. 它必须很容易被超越。(Excel很擅长简单的分析，对吧?我不想为日志编写任何自定义解析器。只使用内置的Excel的功能)
它必须是有弹性的，当我有时会记录额外的信息，有时是变量，有时是数组、对象等

如何处理3)?ParamArray将把传入的数组视为单个元素。当使用Array/Collection时，需要在登录时将标量值预处理为一项数组/集合。

下面是我的代码:

Public Sub Log(level As LoggerSeverityLevel, functionName As String, message As   String, Optional Arguments As Variant)
Dim sh As Object
Set sh = ActiveSheet
LoggerDB.Activate
''' Find blank row for next entry
Dim firstEmptyRow As LongLong
firstEmptyRow = Range("A" & rows.count).End(xlUp).row + 1
''' Parse level into human redable format
Dim lvlMessage As String
lvlMessage = "Unknown"
If level = lslInfo Then lvlMessage = "Info"
If level = lslWarning Then lvlMessage = "Warning"
If level = lslDebug Then lvlMessage = "Debug"
If level = lslCritical Then lvlMessage = "Critical"
''' Insert data
LoggerDB.Cells(firstEmptyRow, 1) = Now()
LoggerDB.Cells(firstEmptyRow, 2) = lvlMessage
LoggerDB.Cells(firstEmptyRow, 3) = functionName
LoggerDB.Cells(firstEmptyRow, 4) = message
''' And optional arguments, one cell per argument
Dim i As Long
Dim arg As Variant
i = 4
For Each arg In Arguments
    LoggerDB.Cells(firstEmptyRow, i) = CStr(arg)
    i = i + 1
Next arg
sh.Activate
End Sub

Answer 1

Public Sub Log(level As LoggerSeverityLevel, functionName As String, message As String,    Optional Arguments As Variant)
Dim sh As Object
Set sh = ActiveSheet
LoggerDB.Activate
''' Find blank row for next entry
Dim firstEmptyRow As LongLong
firstEmptyRow = Range("A" & rows.count).End(xlUp).row + 1
''' Parse level into human redable format
Dim lvlMessage As String
lvlMessage = "Unknown"
If level = lslInfo Then lvlMessage = "Info"
If level = lslWarning Then lvlMessage = "Warning"
If level = lslDebug Then lvlMessage = "Debug"
If level = lslCritical Then lvlMessage = "Critical"
''' Insert data
LoggerDB.Cells(firstEmptyRow, 1) = Now()
LoggerDB.Cells(firstEmptyRow, 2) = lvlMessage
LoggerDB.Cells(firstEmptyRow, 3) = functionName
LoggerDB.Cells(firstEmptyRow, 4) = message
''' And optional arguments, one cell per argument
Dim i As Long
Dim arg As Variant
i = 5
Dim tmp As Variant
Dim coll As Collection

这部分将标量(number, string, bool，任何单一值)打包到集合中。因此，我总是可以假设，如果传递任何额外的参数，For Each将工作。

它依赖于TypeName()对数组返回带有"()"的字符串。

If Not IsMissing(Arguments) Then如果不能被每个(单个变量)包迭代到集合中。如果不是(InStr(TypeName(Arguments)， "()") <> 0或TypeName(Arguments) = "Collection"或TypeName(Arguments) ="字典")Set coll = New Collection科尔。添加参数设置tmp = coll其他的tmp =参数如果

    For Each arg In tmp
        LoggerDB.Cells(firstEmptyRow, i) = CStr(arg)
        i = i + 1
    Next arg
End If
sh.Activate
End Sub