VBA智能记录指定列更新时间戳

VBA智能记录指定列更新时间戳

引用

CSDN

1.

https://blog.csdn.net/taller_2000/article/details/137742291

在Excel中，我们常常需要记录某些关键数据的修改时间，以便追溯变更历史。本文将介绍如何使用VBA实现这一功能，并支持用户在表格中插入/删除列等操作。

实际应用场景

某公司产品统计表如下所示，为了便于追溯变更，如果用户修改了“厂家指导价”或者“供货价”，那么“修改时间列”需要记录当前的时间。

功能需求

为了满足各种使用场景，此功能需要支持以下情况：

• 用户可以在任意位置插入/删除列
• 用户可以在表格之上或左侧插入任意多行

实现代码

Private Sub Worksheet_Change(ByVal Target As Range)
    Const KEY_COL = "厂家指导价|供货价"
    Const UPDATE_COL = "修改时间"
    Const TAB_NAME = "Tab_Product"
    With Target
        If .CountLarge > 1 Then Exit Sub
        Dim oTab As ListObject: Set oTab = .ListObject
        If oTab Is Nothing Then Exit Sub
        If UCase(oTab.Name) <> UCase(TAB_NAME) Then Exit Sub
        Dim r As Range: Set r = Application.Intersect(oTab.HeaderRowRange, Target)
        If Not r Is Nothing Then Exit Sub
        Set r = Application.Intersect(oTab.HeaderRowRange, .EntireColumn)
        If InStr(1, KEY_COL, r.Value) > 0 Then
            Dim updateCell As Range, iCol
            iCol = Application.Match(UPDATE_COL, oTab.HeaderRowRange, 0)
            If IsError(iCol) Then Exit Sub
            Set updateCell = Application.Intersect(oTab.ListColumns(iCol).Range, .EntireRow)
            Application.EnableEvents = False
            updateCell.Value = Format(Now(), "yyyy/MM/dd hh:mm:ss")
            Application.EnableEvents = True
        End If
    End With
End Sub

代码解析

1. 第2行代码定义常量为监控列的标题行名称，以竖线分隔。
2. 第3行代码定义常量为时间戳所在列标题名称。
3. 第4行代码定义常量为表格（ListObject对象）名称。
4. 第6行代码如果多个单元格发生变化，则终止代码执行。
5. 第7行代码获取Target单元格所在的表格，如果对象不存在，说明Target不在表格中，则第8行代码终止代码执行。
6. 如果Target在表格中，第9行代码判断表格名称是否为指导名称，如果不满足条件，则终止代码执行。
7. 第10行代码获取Target和表格标题行的交叉区域，即判断Target是位于标题行中。
8. 如果Target位于标题行中，第11行代码终止代码执行，即无需更新时间戳。
9. 第12行代码获取Target所在列的标题行名称。
10. 第13行代码判断标题行名称存在与监控列表中。
11. 第14行代码调用工作表函数Match在表格标题行中查找时间戳列名称。
12. 第15行代码如果返回结果为错误值（说明表格中不存在时间戳列），则终止代码执行。
13. 第17行代码获取Target所在行和时间戳列的交叉位置单元格。
14. 第18行代码禁用系统事件事件处理功能。
15. 第19行代码更新时间戳。
16. 第20行代码恢复系统事件处理功能。