Saving the current value of a cell in calc before/when updating it

[josephj11]

I have several cells in a calc spreadsheet (some are data and some are formulas) and I would like to save the current value before/when updating the cell.

I do this manually now using copy and paste special - just value - into another cell.

Is there a more elegant way to do this?

Ideally, I want to “push a button” that says I’m doing an update now, save this (or several) cell(s) to their previous value cell locations.

I have not explored the LibreOffice macro language, so any solution entailing its use would need to be detailed.

[schiavinatto]

@josephj11, turn the macro on button or menu.

'================================================|
Sub CopyPasteValue
'================================================|
Dim document, dispatcher As Object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args21(0) as new com.sun.star.beans.PropertyValue
args21(0).Name = "ToPoint" : args21(0).Value = "Plan2.A5"                    '<=====copy location
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args21())

dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "ToPoint" : args11(0).Value = "Plan2.A7"                  '<======paste location
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args11())

dim args1(5) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Flags"
args1(0).Value = "SVDT"
args1(1).Name = "FormulaCommand"
args1(1).Value = 0
args1(2).Name = "SkipEmptyCells"
args1(2).Value = false
args1(3).Name = "Transpose"
args1(3).Value = false
args1(4).Name = "AsLink"
args1(4).Value = false
args1(5).Name = "MoveMode"
args1(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args1())
End Sub

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

[josephj11]

Sorry I didn’t upvote this long ago. The problem is that, to use it effectively, I still have to learn a bunch of the scripting language which looks like a pretty big learning curve.

[schiavinatto]

Hello, Josephj11, post an example of your file, make the macro in it, it will be easier to study.

Or if you wish, send it directly to gilberto@schiavinatto.com

[karolus]

this macrocode is pointless, simply select the Formula-cells in question and do:
→→ Data →→ Calculate →→ Formula to Value

[LeroyG]

@josephj11

into another cell

So, that is a bit complicated than just Formula to Value.

Are the affected cells contiguous?

[josephj11]

What I am looking for would probably have to be integrated into LibreOffice.

It would look something like this (but it needs to fit into a cell somehow.)

SAVEVALUE(source_range, destination_range, trigger)

source_range - block of values to save - cells can contain anything that is or generates a value
destination_range - block of cells (hopefully with the same dimensions as the source_range)
which will receive the static values of all the cells in source_range
trigger - TRUE → run the function FALSE → do not run the function (single cell reference)
This is necessary, because it only runs once per epoch. Epoch is completely user defined.
Several invocations could use the same trigger, other groups of invocations could use their own triggers. E.g. some things might all update once a day - if the spreadsheet was worked on that day. Other groups might update at different time intervals, or when some arbitrary condition becomes true.

A key point is that this can not autoupdate. It needs to preserve its state until explicitly asked to update.

I need it to update when I’m ready, not on any automatic condition.

Right now, I have a moderately large financial spreadsheet. Each day (but I miss some days), I update it manually and compare the results with the values from “yesterday” - whenever I updated it last. There are a number of values I compare, but they are a small fraction of all the cells in the spreadsheet.

This doesn’t involve any visible macro code.

[josephj11]

I recently found some LibreOffice macros that retrieved external values of assets from APIs. When I moved them from one location in my spreadsheet to another, they broke and I couldn’t figure out how to fix them. I don’t have the time or inclination to learn a complex niche language like this. If I had language learning time, it would be used for learning Python because I already use an application (AutoKey) which is customized with Python scripts.

[LeroyG]

@josephj11

destination_range

This make me think in Pivot Tables.

[josephj11]

I have never looked into those.

My current spreadsheet has totals in three places and some on an external Android app and none of them agree with each other LOL. So, although I’ve been using spreadsheets for a very long time, I’m definitely a beginner and definitely not a financial expert!

[eeigor]

Use callback listener.

save-value.ods (18.9 KB)

The source and target ranges must match in size.

Option Explicit

Function SaveData(Watch, Target$, RightNow As Boolean)
	Dim ac As Object, oCallback As Object
	
	ac = CreateUnoService("com.sun.star.awt.AsyncCallback")
	oCallback = CreateUnoListener( "callback_", "com.sun.star.awt.XCallback")
	ac.addCallback(oCallback, Array(Watch, Target, RightNow))
End Function

Sub callback_notify(aData)
	Dim oSheet As Object, aSourceData, oTarget As Object, bRightNow As Boolean

	bRightNow = aData(2)
	If Not bRightNow Then Exit Sub

	aSourceData = aData(0)
	oSheet = ThisComponent.Sheets.getByName("Sheet1")
	oTarget = oSheet.getCellRangeByName(aData(1))
	oTarget.DataArray = aSourceData	
End Sub

The RightNow variable name can be replaced with Update or Trigger.

[josephj11]

Thank you! At a glance, this appears to be exactly what I want.

I will reply in more detail when I come up for air long enough to examine it in detail and implement it in my spreadsheets.

If it does what I think it does, it should be added into Calc as an enhancement.

[eeigor]

Okay, upvote me and mark the solution. To be honest, I liked it myself. I’m thinking about where it could be applied in my place. Such tasks happen.
Let’s say thanks to @PYS, but I have modified the example specifically for your task.
Link

Link updated

[josephj11]

Been here for a few years, but never got any points, so I can’t upvote you. I will mark the solution after I’ve tried it. Thanks.

[eeigor]

Okay. The solution has one drawback: the second argument of the function (Target Range) is represented by a string. This makes it difficult to edit and update it. The fact is that StarBasic does not allow you to pass a range of cells to the procedure, but only passes its data, unlike Excel. But we have the option to use the same Excel ranges in compatibility mode here. You may not need it.
The first and third arguments can be replaced with named ranges, which will make it easier to edit them without changing the function.

[Butterfly]

Can someone tell me how to get the values from “aSourceData”, for examle in line 1. I want to save them in variables im my code.
thanks for help

[Villeroy]

This office suite comes with a database component and input forms. You enter data in some “cells” (form controls) and they will be stored in a database table before you are going to enter new data.

[Butterfly]

@Villeroy

This office suite comes with a database component and input forms. You enter data in some “cells” (form controls) and they will be stored in a database table before you are going to enter new data.

Ok, thank you. I thougt I can use in in another way.