vba 当另一个单元格的值发生变化时，单元格中的自动日期更新（由公式计算）

Question

提问by AlienHand

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.

我在 C2 中有一个公式，比如说=A2+B2。每当 C2 更改值（实际值，而不是公式）时，我都希望在 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.

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

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

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

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

如果可能，我需要对单元格 C2:C30（+ D2:D30 用于日期+时间）激活此功能

Using Excel 2010.

使用 Excel 2010。

Answer 1

回答by Roman

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.

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

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).

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

EDIT:

编辑：

Here is some code:

这是一些代码：

For the UDF:

对于 UDF：

    Public Function UDF_Date(ByVal data) As Date

        UDF_Date = Now()

    End Function

As Formula in D2:

作为 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.

您必须为 D2-Cell 提供日期时间格式，否则它将显示日期值的数字表示。

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

如果您将 C2 引用保留在 D2 公式相关中，则可以通过拖动公式将公式扩展到所需范围。

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.

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

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:

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

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 testedand 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

UDF 的用法与上述相同。
UDF 仅适用于单个单元格输入范围。
通过存储单元格的最后一个值和在工作簿的文档属性中检测到更改时的日期时间来跟踪单元格值。如果公式用于大型数据集，文件的大小可能会显着增加，因为对于公式跟踪的每个单元格，存储要求会增加（单元格的最后一个值 + 上次更改的日期。）此外，Excel 可能无法处理非常大量的文档属性，代码可能会在某个时刻停止。
如果更改工作表的名称，则其中包含的单元格的所有跟踪信息都将丢失。
对于转换为字符串是不确定的单元格值，代码可能会刹车。

下面的代码未经测试，应仅被视为概念证明。使用它需要您自担风险。

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

Answer 2

回答by datatoo

Make the insertion of the date conditional upon the range.

以范围为条件插入日期。

This has an advantage of not changing the dates unless the content of the cell is changed, and it is in the range C2:C2, even if the sheet is closed and saved, it doesn't recalculate unless the adjacent cell changes.

这有一个优点，除非单元格的内容发生更改，否则不会更改日期，并且它在 C2:C2 范围内，即使工作表关闭并保存，它也不会重新计算，除非相邻单元格发生更改。

Adapted from this tipand @Paul S answer

改编自此提示和@Paul S 的回答

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim R1 As Range
 Dim R2 As Range
 Dim InRange As Boolean
    Set R1 = Range(Target.Address)
    Set R2 = Range("C2:C20")
    Set InterSectRange = Application.Intersect(R1, R2)

  InRange = Not InterSectRange Is Nothing
     Set InterSectRange = Nothing
   If InRange = True Then
     R1.Offset(0, 1).Value = Now()
   End If
     Set R1 = Nothing
     Set R2 = Nothing
 End Sub

Answer 3

回答by Arun Chettoor

The simplest way is to add =IF(B3="","Not Allocated",Now())and change the format of the column to the required date and time format. But here if the B column is edited, the date and time of the respective column that needs the update will gets updated automatically for all the columns since not checking the old value. But if it is fine to get current Time this can be easily used.

最简单的方法是将=IF(B3="","Not Allocated",Now())列的格式添加并更改为所需的日期和时间格式。但是这里如果 B 列被编辑，则需要更新的相应列的日期和时间将自动为所有列更新，因为不检查旧值。但是，如果可以轻松获取当前时间，则可以轻松使用。

Answer 4

回答by Paul S

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$C" Then

        ActiveSheet.Range("D2").Value = Now()

    End If

End Sub

vba 当另一个单元格的值发生变化时，单元格中的自动日期更新（由公式计算）

提问by AlienHand

回答by Roman

回答by datatoo

回答by Arun Chettoor

回答by Paul S

相关推荐

最近更新

标签

vba 当另一个单元格的值发生变化时，单元格中的自动日期更新（由公式计算）

提问by AlienHand

回答by Roman

回答by datatoo

回答by Arun Chettoor

回答by Paul S

相关推荐

强制特定缩放级别的 Excel VBA 代码

vba VBA如何从函数返回null

vba 获取对其他收件箱的引用

使用 vba 获取 activex 组合框的选定值

相关推荐

最近更新

标签