如何使用Excel VBA制作外部日志？ [英] How to make an external log using Excel VBA?

问题描述

代码已更新，以引用以下更改。

The code has been updated to reference the changes below.

此日志系统为Excel创建名为Log.txt的外部文档，它将在log.txt文件如下所示：

This log system create an external document for Excel called Log.txt, it will create a line in the log.txt file that looks like this:

11:27:20 Matthew Ridge将单元$ N $ 55从ss更改为

11:27:20 AM Matthew Ridge changed cell $N$55 from ss to

这不会告诉你有人是否在表单中输入了一行新的代码，但是如果代码需要一个答案，它会告诉你什么这个代码应该适用于Mac和PC系统的组合。如果有人发现没有请说。

This will not tell you if someone entered a new line of code into the sheet, but if the code demands an answer, it will tell you what cell that answer is in. This codes below should work for both Mac and PC systems combined. If people find it doesn't please say.

这段代码是在这里的人和其他形式的帮助下创建的，所以我不能独自拥有文件，但我可以掌握这个概念。所以感谢那些帮忙的人，如果没有这样的话，我现在不会有一个可行的Excel记录系统;）

This code was created with the help of people here, and other forms, so I can't take sole proprietorship of the document, but I can take ownership of the concept. So thanks to those who helped, without this there now wouldn't be a viable logging system for Excel in my opinion ;)

BTW，在任何人惊奇之前，这个代码去了，一般/新的最终用户不是很明显。您需要转到 开发人员标签 打开它，单击Visual Basic，当新窗口打开时查找Microsoft Excel对象;在该文件夹下应该是你的工作簿。您可以将其放在ThisWorkbook下或任何表格内，双击您想要的代码所在的工作表。

BTW, before anyone freaks out and asks where does this code go, it isn't obvious to the general/new end user. You need to go to the Developer Tab open it up, click on Visual Basic, and when the new window opens look for Microsoft Excel Object; under that folder should be your workbook. You can either put it under ThisWorkbook or inside any of the sheets by double clicking on the sheet you want the code to be in.

一旦表格在右侧打开面板中，您将看到Option Explicit，如果您不是通过确保需求变量声明被选中来激活它，则最好。这可以在Visual Basic窗口中找到，并按照以下路径：

Once the sheet is open on the right panel, you will see Option Explicit, if you don't it is best if you activate it by making sure the Require Variable Declaration is checked. This is found at the Visual Basic window again, and follow this path:

工具 - > 选项 - >的编辑即可。

Tools-> Options -> Editor.

如果被检查，那么你不用担心，如果没有，那么你检查它。 Option Explicit是一个很好的代码，它强制你声明变量，这是一个很好的做法开始。

If it is checked then you have no worry, if not then you check it. Option Explicit is a good thing for you code, it forces you to declare variables, which is a good practice to begin with.

经过验证，您可以复制

Option Explicit
Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sLogFileName As String, nFileNum As Long, sLogMessage As String

    sLogFileName = ThisWorkbook.Path & Application.PathSeparator & "Log.txt"

 On Error Resume Next ' Turn on error handling
    If Target.Value <> PreviousValue Then
        ' Check if we have an error
        If Err.Number = 13 Then
           PreviousValue = 0
        End If
        ' Turn off error handling
        On Error GoTo 0
        sLogMessage = Now & Application.UserName & " changed cell " & Target.Address _
        & " from " & PreviousValue & " to " & Target.Value

        nFileNum = FreeFile                         ' next file number
        Open sLogFileName For Append As #nFileNum   ' create the file if it doesn't exist
        Print #nFileNum, sLogMessage                ' append information
        Close #nFileNum                             ' close the file
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target(1).Value
End Sub

随着时间的推移，我会尝试更新此代码按照我认为合适的方式添加更多功能。

As time goes by, I will attempt to update this code to add more features to it as I deem fit.

再次感谢所有的帮助，非常感谢让这可能。

Again thanks to all that helped, it is greatly appreciated to make this possible.

推荐答案

问题是，当您输入合并的单元格时，放入PreviousValue（在$ code> Worksheet_SelectionChange ）中的值是所有合并的单元格的数组，您无法与新值进行比较。当 Worksheet_Change 被触发编辑时，目标只是合并范围的左上角单元格。所以让我们跟踪这个单元格的合并范围。将 Worksheet_SelectionChange 替换为以下内容：

The problem is that when the you enter the merged cells, the value put into PreviousValue (in Worksheet_SelectionChange) is an array of all of the merged cells, which you can't compare to the the new value. When Worksheet_Change is fired on the edit, the target is only the top-left cell of the merged range. So let's just track that cell for merged ranges. Replace your Worksheet_SelectionChange with the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target(1).Value
End Sub

免责声明：这是在Excel for Mac 2011测试，因为我目前无法访问Excel的Windows，但我很确定它可以在Excel的Windows上工作。

Disclaimer: This was tested on Excel for Mac 2011 as I don't have access to Excel for Windows at the moment, but I'm pretty sure that it will work on Excel for Windows as well.

这篇关于如何使用Excel VBA制作外部日志？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！