Variable Scope and Lifetime - Makes No Sense

Hello All,

I'm using VBA with Excel 2007, and I've run across a variable scope and lifetime issue that makes no sense to me. I was hoping that someone might be able to shed some light.

For testing purposes, I have a blank worksheet with a single command button on it (TestButton).

In the worksheet code I have the following Sub:

Private Sub Workbook_Open()

MyVariable = 1
ThisWorkbook.ActiveSheet.Range("A1") = MyVariable

End Sub

So, as you can see, when the workbook loads, MyVariable is set to '1' and the value is displayed at cell A1. MyVariable is declared as a Public variable at the top of Module1 so that it can be used globally.

Here's what makes no sense. I use the TestButton to display the value of MyVariable in cell A2, whenever I click the button. But, if I place the event handler of the button in a separate module (Module2), MyVariable always retains the value it was given when the workbook opened.

However, if I place the event handler in the same module where the declaration of MyVariable resides (Module1), the value of MyVariable is always reset whenever I click the button:

Public MyVariable As Integer

Sub TestButton_Click()

ThisWorkbook.ActiveSheet.Range("A2") = MyVariable

End Sub

Can anyone explain to me why MyVariable doesn't retain its value in this case?

Thanks for any help you can provide.

Hi,

The bottom line is it should retain its value. Two possibilities to explain your observations immediately spring to my mind:

1. You have multiple variables declared called MyVariable (in different modules), and your procedures are picking up different ones (the one closest in scope to the procedure will be picked up).

2. Where you have been experimenting with your code, you have fallen victim to a state loss event. A state loss event is where something happens which causes all of your static/module level variables to lose their values. Significant changes to code, such as shuffling procedures around, will cause a state loss event. It's possible that you assigned the value to the variable (workbook_open()), tested your code which showed the value was retained, then moved the procedure which caused a state loss, then tested the code again.

Hope that helps...

I think the issue is where you have your Public variable declared and the location of your TestButton_Click event. In your description you say that you have your button on a blank worksheet. Your second code section shows the declaration and the button's click event. That has to mean that you are trying to either 1) declare a public variable within a sheet object's code window or 2) you have the button_click event in a module. Neither one of those two conditions will work.

I believe if a Public variable is declared in some other object besides a module, it acts as if it is a "Module" level variable and is only used within the module in which it is declared. Hence the Workbook_Open event creates and uses a local variable of that name within that procedure.

On the other hand, you do say that you have the variable declared in Module 1 or Module 2. If you have the Sub TestButton_Click() in that same module, how do you activate that procedure with the button on the worksheet? That procedure should be within the Sheet object that contains the button.

So, here is what I would try. 1) Put the TestButton_Click event procedure within the Code area for the Sheet that contains the button. 2) Put the Public variable declaration in the code area for one of your two Modules. I've done this and it works.

I hope this helps.
Nate Brei

Good point in implicit variables, Nate.

I think the issue is where you have your Public variable declared and the location of your TestButton_Click event. In your description you say that you have your button on a blank worksheet. Your second code section shows the declaration and the button's click event. That has to mean that you are trying to either 1) declare a public variable within a sheet object's code window or 2) you have the button_click event in a module. Neither one of those two conditions will work.

My interpretation of when the OP said "button click event handler" is that the button is a forms button rather than an ActiveX button. So the called procedure isn't really a click event handler. But if your idea is right, then it basically 'interlinks' with suggestion #1 in my reply, except that there are implicitly duplicate declarations rather than explicitly. The way to deal with that is to add an Option Explicit statement to all the code modules, which enforces variable declaration. An error will occur if a variable has not been declared or is out of scope of the caller. If the issue persists then I think we come back to either explicit duplicate declaration or state loss.