当另一个单元格的值发生变化(按公式计算)时,在单元格中自动更新日期 [英] 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屋!