How to create a counter inside an event?

Question

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?

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

score 5 · Answer 1 · answered Mar 27 '15 at 02:18

5

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.

answered Mar 27 '15 at 02:18

L42

19,427
11
44
68

Thank You L42 for the quick response. It works! Much appreciated. – B Cheung Mar 27 '15 at 02:29

score 3 · Answer 2 · edited May 23 '17 at 12:22

3

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.

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

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.

edited May 23 '17 at 12:22

Community

1
1

answered Mar 27 '15 at 02:20

Mathieu Guindon

69,817
8
107
235

Thank You Mat. Your solution worked.... much appreciated... would you recommend any good VBA books to study for a newbie? – B Cheung Mar 27 '15 at 02:32
Thanks Mat for the links. I'll look into it. – B Cheung Mar 27 '15 at 05:16

score 1 · Answer 3 · answered Mar 27 '15 at 02:23

1

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.

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.

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

answered Mar 27 '15 at 02:23

Saagar Elias Jacky

2,684
2
14
28

Sure, except globals stink... it can be scoped at module level without being `Public`. Variables should always be declared at the smallest needed scope. – Mathieu Guindon Mar 27 '15 at 02:28
Yeah.. But I don't think I mention to use the 'Public' qualifier anywhere. I was referring to the scope of the variable. When you take it out of the event and declare it in a Module, it's Global for that module - irrespective of you use Private or Public qualifier. Nevertheless, 'Static' is a good option of its used only inside that event. – Saagar Elias Jacky Mar 27 '15 at 02:48
Hi Saagar, thanks for letting me know about the Local/Global variables. There is a lot I don't know, but enjoy learning. Thanks for the link! – B Cheung Mar 27 '15 at 05:19

How to create a counter inside an event?

3 Answers3