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

user_17662496 0
Viewed 408

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?

calc libreoffice cell basic
2 Answers

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
user_51005640

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.

user_176624960
Related