通过创建时间戳跟踪更改 [英] Track changes by creating timestamp

问题描述

我发现原始代码（Excel VBA）可以保持跟踪一列：

The original code (Excel VBA) I found works fine for keeping track of one column:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("P:P"), Target)
xOffsetColumn = 2
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Date
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
        Next
    Application.EnableEvents = True
End If
End Sub

我要跟踪两列。下面你会发现新增的代码。它不起作用，即使我在Dim（通过添加b）之后更改了变量名称。简单地复制粘贴旧代码，然后仅将范围从P：P更改为S：S，xOffsetColumn也不起作用。

I want to track two columns. Below, you will find the newly added code. It does not work, even though I changed variable names after the Dim (by adding a b). Simple copy-pasting the old code and then only change the range from P:P to S:S and the xOffsetColumn also does not work.

Private Sub Worksheet_Change_b(ByVal Target As Range)
'Update 20140722
Dim WorkRngb As Range
Dim Rngb As Range
Dim xOffsetColumnb As Integer
Set WorkRngb = Intersect(Application.ActiveSheet.Range("S:S"), Target)
xOffsetColumnb = 3
If Not WorkRngb Is Nothing Then
    Application.EnableEvents = False
    For Each Rngb In WorkRngb
        If Not VBA.IsEmpty(Rngb.Value) Then
            Rngb.Offset(0, xOffsetColumnb).Value = Date
            Rngb.Offset(0, xOffsetColumnb).NumberFormat = "dd-mm-yyyy"
        Else
            Rngb.Offset(0, xOffsetColumnb).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

推荐答案

Worksheet_Change 事件宏应该处理两列，包括将多个值粘贴到范围包含一列或两列。

This modification to your original Worksheet_Change event macro should take care of both columns including pasting multiple values into a range that encompasses one or both columns.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20150930
    If Not Intersect(Target, Union(Columns("P"), Columns("S"))) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        Dim rng As Range
        For Each rng In Intersect(Target, Union(Columns("P"), Columns("S")))
            If Not VBA.IsEmpty(rng) Then
                rng.Offset(0, 2 - CBool(rng.Column = 19)) = Date
                rng.Offset(0, 2 - CBool(rng.Column = 19)).NumberFormat = "dd-mm-yyyy"
            Else
                rng.Offset(0, 2 - CBool(rng.Column = 19)).ClearContents
            End If
        Next rng
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

，我简单地从列P到列N两列。我不知道为什么第二个事件宏示例只移回到列P;我不认为您打算覆盖P列中的值。

To simply the offset, I simply worked back two columns from column P to column N. I'm not sure why the second event macro sample only moved back to column P; I didn't think it was your intention to overwrite the values in column P.

Application.ActiveSheet.Range（P：P ）列引用是不必要的，如果事件宏由更改其中一个值的代码触发，而另一个工作表持有 ActiveSheet属性。默认情况下，工作表代码页是私有的;默认情况下，模块代码页是公开的。您可以引用单元格和范围，而不必在工作表代码表中明确声明其父代，而在代码表中则是错误的编码习惯。

The Application.ActiveSheet.Range("P:P") column reference was unnecessary and potentially dangerous if the event macro was triggered by code that changed one of the values while another worksheet held the ActiveSheet property. Worksheet code pages are private by default; module code pages are public by default. You can reference cells and ranges without explicitly declaring their parent in a worksheet code sheet while that is bad coding practice on a module code sheet.

我还更改了用于时间戳从 Date 到现在。单元格格式化仍然只显示日期，但如果您需要，您将有时间。

I also changed the value used for the timestamp from Date to Now. The cell formatting will still only display the date but if you ever need it, you will have the time as well.

这篇关于通过创建时间戳跟踪更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！