Method2 (Inspired by @Lupp). This method is preferable. Sheet2 contains a formula that calls a macro whenever the target range is updated.
Try this.
save-value (3).ods (14.3 KB)
No AsyncCallback. No listener used. A simple macro.
Function DoSaveValue(Watch, Target$)
Dim result As Boolean
result = SetValue(Array(Watch, Target))
DoSaveValue = IIf(result, ":done:", ":fail:")
End Function
Function SetValue(aData) As Boolean
On Local Error GoTo Fail
Dim sheet As Object, cursor As Object
Dim lefttopcell As Object, newcell As Object
Dim nCol&, nEndRow&
Dim value: value = aData(0)
sheet = ThisComponent.CurrentController.ActiveSheet '.Sheets.getByName("Sheet2")
lefttopcell = sheet.getCellRangeByName(aData(1))
nCol = lefttopcell.CellAddress.Column
cursor = sheet.createCursorByRange(lefttopcell)
cursor.collapseToCurrentRegion()
nEndRow = cursor.RangeAddress.EndRow
newcell = sheet.getCellByPosition(nCol, nEndRow + 1)
newcell.Value = value
SetValue = True
Fail:
End Function
Excel has an Application.Volatile method. In Calc, the macro is volatile by default.
NOTE: The algorithm uses the collapseToCurrentRegion() cursor method, so the target range must be surrounded by empty columns on the left and right.
And why did I do it differently before?
This is where it all comes from.
Edit:
Nuance. When you open the file, the ThisComponent.CurrentController.ActiveSheet method is not yet available, so an error occurs. But this is the desired behavior, because in this case the last value is not duplicated. And in cell A1 you see the result :fail:
When using the first method, this harmful effect did not occur, but only connected the listener.
Which method is better?
