How to get previous cell value on an event change

[BethanyDillon99]

I have a macro that is triggered on a content change sheet event- you can ignore the if statement, it just ensures its only run on certain cell changes.

Sub ContentChange(oEvent)
	If oEvent.Spreadsheet.Name = "SheetName" AND oEvent.CellAddress.Row = 1 AND (oEvent.CellAddress.Column = 4 OR oEvent.CellAddress.Column = 7) Then 
		currentValue = oEvent.Formula
		previousValue = oEvent. ?

		'Run macro with above parameters 
	EndIf
End Sub

Is there a way to get the previous value of the cell that the change has been made to? I’ve been digging through the documentation but I haven’t had much luck.

Thank you in advance!

[mikekaganski]

@BethanyDillon99

the documentation

Oh, using OpenOffice.org XML File Format 1.0 specification from 2002 is not reasonable, when the current file format is ODF (based on, but not identical to, that old pre-ISO file format). Anyway, the events are not part of the file format, but an API.

Debugging the event handler, you see that the oEvent is simply a cell object (ScCellObj), not some specific “event” object with fields you want.

So a better signature for your handler would be

Sub ContentChange(oCell)

This event occurs after the change, and the previous state is already lost at this point.

See also: XY Problem.

A blind attempt to solve the guessed actual problem could be Data Validity.

[sokol92]

The simplest is to use a global variable to store the previous value (or formula) of the cell.
Additionally, you need to assign a value to this global variable when the document is opened (or at another appropriate moment).

[relaxcalc]

TROUVE CELL.ods (10.1 KB)
test this,

[relaxcalc]

and add it for your macro , you can change the serch value if you like

[Lupp]

(Administrators: Please let me post as I decide it’s reasonable. Don’t gues similarity of an answer to a previously tried -and then removed- comment. It’s annoying. Who had thought the new software the site was moved to would even be worse than the old one. Much too many pseudo-smart features.)

This is not exactly an answer because it isn’t defining/using an event handler, but works with ordinary formulas. The starting function may, however be easily replaced by an event handling Sub introducing some needed adaptions. In addition it’s only made for “values” (whether numeric or string). To memorize formulas you need to make them string results in the watched cells. Anyway I would suggest to forget about event handling in the current context. Too much overhead.

Years ago I wasted a lot of time trying to find ways to get history awareness by standard formulas. I came to the conclusion that it can’t be done in a reliable way.

However, when recently there was another question concerning “previous values” I tried to find a definite solution not needing an asignment to an event handler (onContentChanged), but triggered by an ordinary formula based on a UserDefinedFunction.

The attached .ods has a first sheet basically made (formatted) by the questioner of the mentioned thread. The second sheet -needed as extra memory- is hidden (and should only made visible for studies).
Only the range Sheet1.D5:D11 is watched and memorized in the example. It uses 11 columns of the respective rows for a deep shiftregister cointaining 10 previous values, and you can’t simply add watched ranges without thorough considerations therefore.

shiftRegisterNotListening.ods (21.2 KB)

[eeigor]

@Lupp

for a deep shiftregister cointaining 10 previous values

This solution has a side effect. The data is updated when the file is opened, and a shift occurs.

[Lupp]

Hmmm…
Firstly I posted my suggestion as a solution despite the fact that it disregards the “on an event change” contained in the question (and not understood clearly by myself). I simply cancelled the “event” idea, because a solution based on a onContentChanged handler for the sheet would require a lot of overhead, and I lack experience with all the kinds of listeners that may be usable in the context, and probably can be created for single cells.
Secondly my posted solution contained a third parameter of the relevant function as a hint in what way it might be completed, but without an implementation of its intended functionality.

Now I attach a completed version containing that functionality (in one of the thinkable variants). To avoid unintentional shifting and input to the register the controlling cell (now titled IsActive) simply can be set to FALSE. Doing so before the document is saved will at least avoid registering the result of a recalculation during the process of saving and/or later reloading.
Set that value to TRUE later will, however, act on the register once if no additional precautions are taken.
To also avoid this, you can compare a non-volatile timestamp of the last recalculation of the watched range with the (volatile) current NOW(), and make the register shift depend on the result. This idea is implemented in the new example.
An attempt to automate the de-/re- activation of the shift-register is not included.

I would prefer to include the suggestion above, but to let IsActive unchanged when saving. The example will then add/shift when opening because the watched cells may result in changed values when recalculated.

(To also add date-time-stamps to the register is just another suggestion.)

shiftRegisterConditionally.ods (29.3 KB)