time stamp when calulation is made.

[richramik1]

Hey Gang:

Have a spreadsheet with all kinds of calculations. I run this report in the morning to get the most recent numbers.

What I am looking for is when one of the calcs is done that it time stamps the calc is a neighboring cell. There are other calcs going on that do not impact these particular cells. I can and often do change the other cells. This is so I and the report readers know when the “big numbers” were last updated. For example, A1 + B1 = C1. Regardless of the valve of C1, I need to know when this calc was done.

Thanks,
Rich Ramik

[sokol92]

You can use the following schema to track the change in the numeric result of a formula calculation in cell C1.

Global Old_value

Sub OnSheetCalculate
  Dim v
  v=ThisComponent.Sheets(0).getCellRangeByName("C1").Value
  If v<>Old_value Then
     Old_value=v
     Msgbox "C1 change " & Now  ' here we fix timestamp 
  End If
End Sub

Sub OnDocOpen
  Old_value=ThisComponent.Sheets(0).getCellRangeByName("C1").Value
End Sub

Assign OnSheetCalculate to the “Formulas calculated” sheet event, and OnDocOpen to the “Open document” event.

[Ratslinger]

@sokol92,

While maybe obscure (and remote), it seems possible that C1 remains unchanged when a calculation is actually done. May be needed to actually verify that either A1 or B1 was changed.

Edit:

Carry it further and even that can be clouded. The calculation can be done even when A1 or B1 are changed to the same values.

BTW, unfortunately do not have any resolution to this. It is just an observance.

[sokol92]

Dear Ratslinger! OnSheetCalculate macro will be called every time after the end of recalculation of sheet cells. I hope that the author of the topic will more accurately formulate for himself the conditions for recording Timestamp in the log.

[richramik1]

As to the validity of a change and the resulting time stamp, I can force the change to occur A1 and/or B1. This should be the requirements of the macro.