当通过公式（Excel）更改更大单元格范围内的单元格时的时间戳 [英] Timestamp when a cell in a larger cell range is changed via a formula (Excel)

问题描述

随着这个问题，每当通过一系列单元格的公式更改单元格时，我需要一个相邻单元格中的时间戳。

As a follow up to this question, I need a timestamp in an adjacent cell whenever a cell is changed via a formula for a range of cells.

我知道这意味着构建一个数组将以前的值存储到下面的代码中（这实现了相同但仅适用于单个单元格），并希望能够实现这一点。

I'm aware this means building an array to store the previous values into the code below (which achieves the same but only for a single cell) and would appreciate any help achieving this.

这是代码工作对于单个单元格

Here's the code that works for a single cell...

在Sheet1单元格A1中，将此公式

In Sheet1 Cell A1, put this formula

=Sheet2!A1+1

现在在一个模块中粘贴这段代码

Now In a module paste this code

Public PrevVal As Variant

将其粘贴到工作表代码区域

Paste this in the Sheet Code area

Private Sub Worksheet_Calculate()
    If Range("A1").Value <> PrevVal Then
        Range("B1").Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
        PrevVal = Range("A1").Value
    End If
End Sub

最后在ThisWorkbook代码区粘贴此代码

And lastly in the ThisWorkbook Code area paste this code

Private Sub Workbook_Open()
    PrevVal = Sheet1.Range("A1").Value
End Sub 

推荐答案

您可以将以前的值保存在字典而不是数组。要使用字典，您需要添加对 Microsoft脚本运行时库的引用

You can keep your previous values in a Dictionary rather than an array. To use the dictionary you need to add a reference to Microsoft Scripting Runtime Library

（工具>参考> Microsoft脚本运行时库）

(Tools > References > Microsoft Scripting Runtime Library)

标准模块

Public PrevVal As Dictionary

---

ThisWorkbook模块

Private Sub Workbook_Open()
    Dim r As Range
    Set PrevVal = New Dictionary
    For Each r In Worksheets("Sheet1").Range("A1:A10")
        PrevVal.Add Item:=r.Value, Key:=r.Address
    Next r
End Sub

---

表单模块

Private Sub Worksheet_Calculate()
    Dim v As Variant

    For Each v In PrevVal.Keys()
        If Range(v).Value <> PrevVal(v) Then
            Range(v).Offset(0, 1).Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
            PrevVal(v) = Range(v).Value
        End If
    Next v
End Sub

这篇关于当通过公式（Excel）更改更大单元格范围内的单元格时的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！