excel - Excel VBA中监听单元格更改的最佳方法

Question

我正在尝试观察一个单元格的变化。由加载项中的某些 VBA 代码触发的单元格每秒可以更改多次。我想计算值更新的次数。

到目前为止，我所拥有的是:

Private Sub Worksheet_Calculate()
    Static oldval
    If Range("C3").Value <> oldval Then
        oldval = Range("C1").Value
        Blad1.Range("N18").Value = Blad1.Range("N18").Value + 1
    End If
End Sub

问题是当我启动我的 Excel 工作表时，代码会立即崩溃并出现以下错误: Out of stack space
我现在的问题是为什么我会收到这个错误，这是我想要做的最快的实现吗？

Answer 1

关于堆栈溢出的问题，在 StackOverflow 上。

增加您的计数器单元格会触发计算事件，这会增加您的计数器，这会触发计算事件等。使用另一个静态变量来防止递归。静态变量在调用托管它的过程中保持其值。

Private Sub Worksheet_Calculate()
    Static bWorking as Boolean
    Static oldval As Variant

    If Not bWorking Then
        If Range("C3").Value <> oldval Then
            oldval = Range("C1").Value

            bWorking = True
            Blad1.Range("N18").Value = Blad1.Range("N18").Value + 1
            bWorking = False
        End If
    End If
End Sub

还要考虑@YowE3 关于为什么您的代码将 oldval 设置为 C1 的值的评论。

编辑 :至于问题的性能部分，假设您想将计数器的值实时存储到单元格中，您可以通过重新使用单元格引用、将计数保持在静态变量中并使用 Value2 来获得边际 yield 属性(property)。

Private Sub Worksheet_Calculate()
    Static monitoredCell As Excel.Range
    Static counterCell As Excel.Range
    Static counter As Long
    Static bWorking As Boolean
    Static oldVal As Variant

    If Not bWorking Then
        If monitoredCell Is Nothing Then
            'Initialize the ranges.
            Set monitoredCell = Me.Range("C3")
            Set counterCell = Blad1.Range("N18")
            'Comment out the line below if you don't wish to keep counting from the last saved value.
            counter = counterCell.Value2
        End If

        If monitoredCell.Value2 <> oldVal Then
            oldVal = monitoredCell.Value2
            counter = counter + 1

            bWorking = True
            counterCell.Value2 = counter
            bWorking = False
        End If
    End If
End Sub