当另一个单元格的值发生变化（按公式计算）时，在单元格中自动更新日期 [英] Automatic date update in a cell when another cell's value changes (as calculated by a formula)

问题描述

我在C2中有一个公式，说 = A2 + B2 。每当C2更改值（实际值，而不是公式）时，我想要在D2中更新当前日期和时间。

我已经尝试了很多VBA代码和技巧，如果在C2中输入公式，它们都不起作用。但是，如果我在C2中手动键入值，则日期和时间将根据需要进行更新。这当然是因为一个真正的价值被输入/改变 - 公式保持不变，可以这么说。

问题：
当C2中公式的结果发生变化时，是否可以创建一个更新D2的VBA代码（或别的）？

如果可能，我需要这样做对于单元格C2：C30（+ D2：D30为日期+时间）为活动

使用Excel 2010。

解决方案

您可以通过将C2-Cell的值作为输入参数的用户定义函数（VBA Macro Function）填充依赖单元（D2），将当前日期返回为ouput。

D2中的UDF的C2作为输入参数告诉Excel，每次C2更改需要重新评估D2（即如果公式的自动计算已打开对于工作簿）。

编辑：

这是一些代码： / p>

对于UDF：

 作为UDF_Date（ByVal数据）作为日期
 
 UDF_Date = Now（）
 
结束函数
  

作为D2中的公式：

  = UDF_Date（C2）
  

您将不得不向D2-Cell提供日期时间格式，或者将显示日期值的数字表示

如果您在D2公式相对方案中保留了C2参考，您可以通过查看该公式来扩展公式。

注意：
这仍然可能不是理想的解决方案，因为每次Excel重新计算工作簿时，D2中的日期将被重置为当前值。
为了使D2只反映最后一次C2更改，将不得不对C2的过去值进行某种跟踪。
这可以例如在UDF中实现，还提供输入参数的值的地址，将输入参数存储在隐藏的表单中，并在每次调用UDF时将它们与先前的值进行比较。 p>

附录：

以下是UDF的示例实现，单元格值并返回检测到最后一次更改的日期时间。
当使用它时，请注意：

• UDF的用法与上述相同。




• UDF只适用于单个单元格输入范围。




• 单元格值通过存储单元格的最后一个值和在
  工作簿的文档属性中检测到更改时的
  日期时间进行跟踪。如果公式在大数据集上使用，则
  文件的大小可能会随着
  跟踪的每个单元格的大小而增加，存储要求增加（单元格的最后一个值+
  date of最后一次更改。）此外，也许Excel不能处理
  非常大量的文档属性，代码可能会在
  a某些点制动。




• 如果工作表的名称已更改，则其中包含单元格的所有跟踪信息都将丢失。




• 代码可能会阻碍单元格值




• 以下代码未经测试，应视为$的证明b $ b概念。

   公共函数UDF_Date（ByVal inData As Range）As Date 
   
   Dim wb As Workbook 
   Dim dProps As DocumentProperties 
   Dim pValue As DocumentProperty 
   Dim pDate As DocumentProperty 
   Dim sName As String 
   Dim sNameDate As String 
   
   Dim bDate As Boolean 
   Dim bValue As Boolean 
   Dim bChanged As Boolean 
   
   bDate = True 
   bValue = True 
   
   bChanged = False 
   
   
   Dim sVal As String 
   Dim dDate As Date 
   
   sName = inData.Address& _& inData.Worksheet.Name 
   sNameDate = sName& _dat
   
   sVal = CStr（inData.Value）
   dDate = Now（）
   
  设置wb = inData.Worksheet.Parent 
   
  设置dProps = wb.CustomDocumentProperties 
   
  错误恢复下一步
   
  设置pValue = dProps.Item（sName）
   
  如果Err.Number< ;> 0然后
   bValue = False 
   Err.Clear 
  如果
   
  发生错误GoTo 0 
   
  如果不是bValue然后
   bChanged = True 
  设置pValue = dProps.Add（sName，False，msoPropertyTypeString，sVal）
   Else 
   bChanged = pValue.Value<> sVal 
  如果bChanged然后
   pValue.Value = sVal 
  结束如果
  结束如果
   
  错误恢复下一步
   
  设置pDate = dProps.Item（sNameDate）
   
  如果Err.Number<> 0然后
   bDate = False 
   Err.Clear 
  如果
   
  出现错误GoTo 0 
   
  如果不是bDate然后
  设置pDate = dProps.Add（sNameDate，False，msoPropertyTypeDate，dDate）
  如果
   
  如果bChanged然后
   pDate.Value = dDate 
   Else 
   dDate = pDate.Value 
  如果
   
   
   UDF_Date = dDate 
  结束函数
    

I have a formula in C2, say =A2+B2. Whenever C2 changes value (actual value, not formula) I want to have the present date and time updated in D2.

I have tried a lot of VBA codes and tricks and none of them works if a formula is entered in C2. BUT if I type a value manually in C2 the date and time is updated as needed. This is of course because a real value is entered/changes - where the formula stays the same, so to speak.

Question: Is it possible to create a VBA code (or something else) that updates D2 when the result of the formula in C2 changes?

If possible, I need this to be active for the cells C2:C30 (+ D2:D30 for the date+time)

Using Excel 2010.

解决方案

You could fill the dependend cell (D2) by a User Defined Function (VBA Macro Function) that takes the value of the C2-Cell as input parameter, returning the current date as ouput.

Having C2 as input parameter for the UDF in D2 tells Excel that it needs to reevaluate D2 everytime C2 changes (that is if auto-calculation of formulas is turned on for the workbook).

EDIT:

Here is some code:

For the UDF:

    Public Function UDF_Date(ByVal data) As Date

        UDF_Date = Now()

    End Function

As Formula in D2:

=UDF_Date(C2)

You will have to give the D2-Cell a Date-Time Format, or it will show a numeric representation of the date-value.

And you can expand the formula over the desired range by draging it if you keep the C2 reference in the D2-formula relative.

Note: This still might not be the ideal solution because every time Excel recalculates the workbook the date in D2 will be reset to the current value. To make D2 only reflect the last time C2 was changed there would have to be some kind of tracking of the past value(s) of C2. This could for example be implemented in the UDF by providing also the address alonside the value of the input parameter, storing the input parameters in a hidden sheet, and comparing them with the previous values everytime the UDF gets called.

Addendum:

Here is a sample implementation of an UDF that tracks the changes of the cell values and returns the date-time when the last changes was detected. When using it, please be aware that:

• The usage of the UDF is the same as described above.

• The UDF works only for single cell input ranges.

• The cell values are tracked by storing the last value of cell and the date-time when the change was detected in the document properties of the workbook. If the formula is used over large datasets the size of the file might increase considerably as for every cell that is tracked by the formula the storage requirements increase (last value of cell + date of last change.) Also, maybe Excel is not capable of handling very large amounts of document properties and the code might brake at a certain point.

• If the name of a worksheet is changed all the tracking information of the therein contained cells is lost.

• The code might brake for cell-values for which conversion to string is non-deterministic.

• The code below is not tested and should be regarded only as proof of concept. Use it at your own risk.

  Public Function UDF_Date(ByVal inData As Range) As Date
  
      Dim wb As Workbook
      Dim dProps As DocumentProperties
      Dim pValue As DocumentProperty
      Dim pDate As DocumentProperty
      Dim sName As String
      Dim sNameDate As String
  
      Dim bDate As Boolean
      Dim bValue As Boolean
      Dim bChanged As Boolean
  
      bDate = True
      bValue = True
  
      bChanged = False
  
  
      Dim sVal As String
      Dim dDate As Date
  
      sName = inData.Address & "_" & inData.Worksheet.Name
      sNameDate = sName & "_dat"
  
      sVal = CStr(inData.Value)
      dDate = Now()
  
      Set wb = inData.Worksheet.Parent
  
      Set dProps = wb.CustomDocumentProperties
  
  On Error Resume Next
  
      Set pValue = dProps.Item(sName)
  
      If Err.Number <> 0 Then
          bValue = False
          Err.Clear
      End If
  
  On Error GoTo 0
  
      If Not bValue Then
          bChanged = True
          Set pValue = dProps.Add(sName, False, msoPropertyTypeString, sVal)
      Else
          bChanged = pValue.Value <> sVal
          If bChanged Then
              pValue.Value = sVal
          End If
      End If
  
  On Error Resume Next
  
      Set pDate = dProps.Item(sNameDate)
  
      If Err.Number <> 0 Then
          bDate = False
          Err.Clear
      End If
  
  On Error GoTo 0
  
      If Not bDate Then
          Set pDate = dProps.Add(sNameDate, False, msoPropertyTypeDate, dDate)
      End If
  
      If bChanged Then
          pDate.Value = dDate
      Else
          dDate = pDate.Value
      End If
  
  
      UDF_Date = dDate
   End Function
  

这篇关于当另一个单元格的值发生变化（按公式计算）时，在单元格中自动更新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！