Macro to store cell value data and retain the value even when cell content changes

Question

I need to permanently store the instantaneous value of some cells, B4 and B5.

My problem is when the cell contents change, so do my stored variables, but I want the variables to retain the values they first pick up when the "Record_Instantaneous_Values" macro is run, even after new data is manually entered into B4 and B5 - basically to build up a permanent record of what value B4 and B5 had whenever the macro is called.

Here's what I have

' Global Variables....
Global FirstCell
Global SecondCell
' ...

Sub Record_Instantaneous_Values
FirstCell = ThisComponent.CurrentController.ActiveSheet.getCellByPosition( 1, 3 )
SecondCell = ThisComponent.CurrentController.ActiveSheet.getCellByPosition( 1, 4 )
End Sub

Sub Peek_at_stored_values
Print "FirstCell = "; FirstCell.value; ", "; "SecondCell = ";SecondCell.value
End Sub

The fact there's an "undo" function in LO, means cell contents at a particular instant can be stored (in some array presumably). While not wanting to delve into the depths of that, there must be some simple way to achieve what I need, but how?

This is not entirely clear - "no matter what updates happen in B4 and B5 later". So will they change? Or, when you try to change them, the old, saved value should be restored? In any case, the allocation of additional backup-cells (even hidden from the user) to save something seems to be the easiest way to solve this problem. (It is possible to store data in a named range, but accessing these values is a little more difficult than accessing cells in a spreadsheet) — JohnSUN, Dec 18 '21 at 06:03
Thanks for responding. Yes, B4 and B5 will eventually get changed. I want to be able to dump the old recorded values back into B4 and B5 (and into other cells) if required later. I hadn't thought of Named Ranges so thanks for that opening, but you hit on something that was an issue for me... I wanted it all to be going on in the background without dumping data in other places (would 'focus switching flickering' occur?), that's why macro coded variables were my instant choice) - besides, coding hidden stuff would probably be a greater challenge than the one I am currently facing (? perhaps). — Micklos, Dec 18 '21 at 12:21
Perhaps I was not entirely clear - yes, a macro should be used to solve the problem (both for saving and for restoring). I meant that global variables are not the best place to store values - if the document is closed, the "saved values" will be lost. And yes - a correctly written macro will not flicker, everything will happen instantly and not noticeable to the user. — JohnSUN, Dec 18 '21 at 15:42
The 'saved values' would only need to be stored for run time use only, a bit like the undo history. When the user closes the Spreadsheet document, it will be understood that the saved values will go too. My understanding is that a variable has to be used because if I was to write the values into a temporary storage cell - hidden or not - then because they are in a spreadsheet cell, they will automatically update when B4 and B5 are changed, a bit like the situation I already have in my code above. — Micklos, Dec 18 '21 at 15:45

Jim K · Accepted Answer · 2021-12-18T17:00:50.107

Here is Basic code that stores the values in a global variable.

Type RecordedCellType
  col As Integer
  row As Integer
  val As Single  'floating point value of cell
  init As Boolean  'has the value been initially recorded?
End Type

Const NUM_CELLS_TO_RECORD = 2
Global CellValues

Sub Initialize_Recorded_Values
    Dim CellValuesLocal(NUM_CELLS_TO_RECORD) As New RecordedCellType
    CellValues = CellValuesLocal
    For CellNum = 1 to NUM_CELLS_TO_RECORD
        CellValues(CellNum).init = False
    Next
    CellValues(1).col = 1
    CellValues(1).row = 3
    CellValues(2).col = 1
    CellValues(2).row = 4
    Call Peek_at_stored_values
End Sub

Sub Record_Instantaneous_Values
    oSheet = ThisComponent.CurrentController.ActiveSheet
    For CellNum = 1 to NUM_CELLS_TO_RECORD
        With CellValues(CellNum)
            If .init = False Then
                oCell = oSheet.getCellByPosition(.col, .row)
                .val = oCell.getValue()
                .init = True
            End If
        End With
    Next
    Call Peek_at_stored_values
End Sub

Sub Peek_at_stored_values
    String sDisplay
    For CellNum = 1 to NUM_CELLS_TO_RECORD
        With CellValues(CellNum)
            sDisplay = sDisplay & "Cell(" & .col & _
                "," & .row & ") "
            If .init = True Then
                sDisplay = sDisplay & "= " & .val
            Else
                sDisplay = sDisplay & "not initialized"
            End If
        End With
        If CellNum < NUM_CELLS_TO_RECORD Then
            sDisplay = sDisplay & CHR$(13)  'newline
        End If
    Next
    MsgBox sDisplay
End Sub

One reason for me is that, when a person asks an interesting question as you did, it's a simple puzzle that I can work on for a few minutes and improve my skills by solving it. I've also benefitted a lot from other people's knowledge on the stackexchange network. So please keep asking good questions - that helps everyone. :) By the way, if this answer helped you find a solution then please mark it as accepted. — Jim K, Dec 18 '21 at 18:40

score 0 · Answer 2 · edited Dec 19 '21 at 00:03

Utilizing the superb solution/code Jim K posted, here's a related macro that writes the stored values back into the original cells (on pressing a Form Control button) for anyone that may need it.

Sub Restore_Stored_Values
oSheet = ThisComponent.CurrentController.ActiveSheet
    For CellNum = 1 to NUM_CELLS_TO_RECORD
        With CellValues(CellNum)  
            If .init = True Then
                oCell = oSheet.getCellByPosition(.col, .row)
                oCell.Value=.val
            End If
        End With
     Next       
 End Sub

I already have have many ideas where this technique will be extremely useful.

Macro to store cell value data and retain the value even when cell content changes

2 Answers2

Linked