用于存储单元格值数据并在单元格内容更改时保留该值的宏 [英] Macro to store cell value data and retain the value even when cell content changes

问题描述

我需要永久存储某些单元格B4和B5的瞬时值。

我的问题是当单元格内容更改时，我存储的变量也会更改，但我希望变量保留它们在运行"；record_Instant_Values"；宏时首次拾取的值，即使在将新数据手动输入B4和B5之后也是如此-基本上是为了建立永久记录，记录无论何时调用宏时B4和B5具有什么值。

这是我的资料

' 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

LO中有一个"；undo"；函数，这意味着可以存储特定时刻的单元格内容(假设存储在某个数组中)。虽然我不想深入探讨这一点，但肯定有一些简单的方法可以实现我的需求，但如何实现呢？

推荐答案

以下是将值存储在全局变量中的基本代码。

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

这篇关于用于存储单元格值数据并在单元格内容更改时保留该值的宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！