Excel VBA中侦听单元格更改的最佳方法 [英] Best way in Excel VBA to listen for cell changes

问题描述

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

I'm trying to watch a cell for changes. The cell can change many times a second triggerd by some VBA code from a Add-in. I want to count the number of times the value updates.

到目前为止，我有:

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工作表时，代码立即崩溃，并显示以下错误:堆栈空间不足

The issue is that when I start my Excel sheet the code crashes at once with the error: Out of stack space

我的问题是现在为什么会出现此错误，这是我想要做的斋戒实现吗?

My question is now why am I getting this error and is this the fastes implementation for what I want to do?

推荐答案

有关StackOverflow上的堆栈溢出的问题.

A question about a stack overflow, on StackOverflow.

增加计数器单元格将触发Calculate事件，这将增加计数器，从而触发Calculate事件，等等.请使用另一个静态变量来防止递归.静态变量会在对承载它的过程的调用中保持其值.

Incrementing your counter cell triggers the Calculate event, which increments your counter, which triggers the Calculate event, etc. Use another static variable to prevent recursion. A static variable keeps its value across calls to the procedure that hosts it.

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值的评论.

Also consider @YowE3's comment about why your code sets oldval to C1's value.

编辑:关于问题的性能部分，假设您想将计数器的值实时存储到单元格中，则可以通过重复使用单元格引用来保持边际收益，保持并使用Value2属性在静态变量中计数.

Edit: as for the performance part of your question, assuming you want to store the counter's value into a cell in real-time, you could make marginal gains by re-using cell references, maintaining the count in a static variable, and using the Value2 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

这篇关于Excel VBA中侦听单元格更改的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！