(Solved) – Variable retaining value between macro calls


new programmer with a question for more experienced heads. I’m using Office 365 ProPlus.

I have a variable that is sometimes retaining its value between calls of the macro. The variable in question is a counter, which I had created to confirm that my for loop was running the number of times I intended. At the end of the macro, the value of the counter would output to a cell in my worksheet. I began to notice that instead of giving me the same answer every time, the cell value was incrementing by the number of iterations in the loop after each call of the macro (e.g. for a loop with five iterations, after the first call my counter would equal 5, after the second call 10, after the third 15, and so on). If I stop the macro in the middle, say to make a change and then rerun, the counter resets to 0.

Here are the relevant sections of code:

Option explicit
Public Counter As Integer
Sub MyMacro()
Call Looper
Sheets("Sheet1").Range("g2") = Counter
End Sub

Sub Looper()
Dim k as Integer
For k = 33 to 4044
Counter = Counter   1
         'Nonrelevant code
Next k
End Sub

The above code has all active uses of the variable not including message boxes.

I put message boxes before and after calling my loop sub in case it had something to do with how the counter was printing to the cell, but the message box before the loop displays the result of the previous call, and the message box after the loop has been incremented by the number of loops. I also added a watch expression for Counter, and unless I have reset counter by interrupting the process as mentioned above, as soon as the sub begins the value of Counter jumps to what it was at the end of the previous call.

I can set the starting value of counter to 0, but why is the counter starting with a value at all?

Leave a Reply

Your email address will not be published. Required fields are marked *