如何在活动中创建一个柜台？

In Cell A1, I have formula that recalculates automatically every one to two seconds. When this cell recalculate, the old values from A1 is copied to cell B1. Every time it recalculates, I just want to get a running total. So far, what works is when there is a change in A1, the old value is copied to B1, but unable to get a running total every time it does this. The problem is i is always equal to 1. Any ideas?

在Cell A1中，我有一个每隔一到两秒自动重新计算的公式。当此单元格重新计算时，A1中的旧值将复制到单元格B1。每次重新计算时，我只想获得一个总计。到目前为止，有效的是当A1发生变化时，旧值被复制到B1，但每次执行此操作时都无法获得运行总计。问题是我总是等于1.任何想法？

Private Sub Worksheet_Calculate()
    Dim dProfit As Double
    Dim i As Integer

    dProfit = Me.Range("A1").Value
    Application.EnableEvents = False

    If dProfit >= 1 Then
        i = i + 1
        MsgBox "calculation detected is " & dProfit & " i= " & i
        Range("$B1") = dProfit
        Application.EnableEvents = True
    End If
End Sub

3 个解决方案

#1

Try declaring i outside your sub.

尝试在你的子外面宣布我。

Dim i As Integer

Private Sub Worksheet_Calculate()
' Rest of your code here
End Sub

This way variable i will persist after the sub terminates.
It will persist as long as VBA don't stop or encounters error.

这种方式变量i将在子终止后持续存在。只要VBA不停止或遇到错误，它就会持续存在。

#2

Either declare i outside the procedure, or keep it local but declare it as a Static variable, so that its value is remembered between method calls.

要么在程序之外声明我，要么将其保持为本地但是将其声明为静态变量，以便在方法调用之间记住它的值。

Static i As Integer

Note that when i reaches 32,768 your code will blow up, since Integer is stored over 16 bits - perhaps declare it as a Long (32 bits) if that's a problem.

请注意，当我达到32,768时，您的代码将会爆炸，因为Integer存储在16位以上 - 如果这是一个问题，可能会将其声明为Long（32位）。

A better solution would be to keep i as a local variable, without making it Static - I don't like Static variables ;)

一个更好的解决方案是将i保持为局部变量，而不使其成为静态 - 我不喜欢静态变量;）

To achieve this, you could retrieve the value from a cell on the worksheet, increment the value, and then store the incremented value where the next iteration is going to read it.

为此，您可以从工作表上的单元格中检索值，递增值，然后将递增的值存储在下一次迭代将要读取的位置。

#3

You need to declare the counter variable as a Public / Global variable so that the value persists even though you exits out of the event which actually uses the counter.

您需要将计数器变量声明为Public / Global变量，以便即使退出实际使用计数器的事件，该值仍然存在。

The problem with your code is that it is declared inside the event and so its scope is Local - meaning it's initialized when the event is triggered and is removed form memory once the control gets out of the event / function.

你的代码的问题是它在事件中被声明，因此它的范围是Local - 意味着它在事件被触发时被初始化，并且一旦控件离开事件/函数就被从内存中删除。

To know more about Local / Global variables, please refer to this

要了解有关本地/全局变量的更多信息，请参阅此内容

#1