unwanted reinitialization of global variables

[mspikowski]

I am having an issue with public global variables that appear to be
reinitialized without execution of a specific line in the code to do
so. The variables are declared in Module1 as public (global)
variables using the following code:

Public alpha As String
Public beta As String
Public delta As String
Public epsilon As String

The values of these variables are set in the Workbook_Open() event of
the ThisWorkbook object using the following code:

alpha = ChrW(945)
beta = ChrW(946)
delta = ChrW(948)
epsilon = ChrW(949)

If I open the workbook and immediately run the Routine2 subroutine
(which uses the variables alpha, beta, delta and epsilon), it works
correctly because the values of the variables are as they were set by
the Workbook_Open event. However, the second time I run Routine2, it
does not function as expected because alpha, beta, delta, and epsilon
have been reinitialized to null values. There is no code to change
the values of these variables other than in the Workbook_Open()
event. I have added a MsgBox to check the value of alpha at the first
line of Routine2 and the last line of Routine2. The first time I
execute Routine2, alpha is equal to the value I intend at the first
line and at the last line of the subroutine. The second time I
execute Routine2, the variable has been reset to null by the time the
first line of Routine2 executes. Routine2 is executed by clicking a
command button. No code that I have written executes between the
first click of the command button and the second click of it. I can
solve the problem by setting the values of alpha, beta, delta, and
epsilon at the beginning of Routine2. However, I find it disturbing
that the global variables are somehow getting reinitialized, and I
would like to understand the underlying cause to avoid this issue in
future programming. The reason why I declared these variables as
global was so that I would only have to set their values one time,
upon first opening the workbook.

 

[Dave Peterson]

Did you hit the Reset button (or Run|Reset) in the VBE?

Does your code have an End statement in it?
Not "End With", "End Sub", "End If"--just a plain old End statement in it? Did
that line of code get run?

=========

Personally, I think adding an initialization routine wouldn't be a bad idea.

In that same module1 module:

Public VarsAreInitialized As Boolean
Public alpha As String
Public beta As String
Public delta As String
Public epsilon As String


Sub InitializeVars()
alpha = ChrW(945)
beta = ChrW(946)
delta = ChrW(948)
epsilon = ChrW(949)
varsareinitialized = true
end sub

Then in your workbook_open event, replace the assignments with a Call statement.

Call InitializeVars

And before you use any of these variables in any routine:

If varsareinitialized = false then
call initializevars
end if

=======
It may save you some problems later.

 

[mspikowski]

Dave,

Thanks for your response. I like the idea of adding the
initializevars routine. I will definitely add that.

As for why they are getting reinitialized to begin with, I'm still not
sure. I checked all my End statements and none of them are just plain
End. All of them are End With, End If, End Sub, etc. Also, I did not
hit the Reset button. Any other ideas?

- Mary

 

[Dave Peterson]

Nothing more comes to mind.

Maybe someone else will chime in.

 

[NickHK]

For testing you can declare these string as constants (with values of "a",
"b" etc) and see where/when the values are being reassigned, as an error
will occur.
Or use the Watch window to see when the values change.

However, I suspect you really do have an End or are resetting, as Dave
suggested, though.

Depending on what you are doing, you could use the Const approach, they
format the cell(s) with the Symbol font.

NickHK

 

[mspikowski]

Dave,

I think I figured it out. I looked at a section of the Visual Basic
help called "Understanding the Lifetime of Variables". Here is an
excerpt:

When a variable loses scope, it no longer has a value [....] If a
procedure-level variable is declared with the Static keyword, the
variable retains its value as long as code is running in any module.
When all code has finished running, the variable loses its scope and
its value. Its lifetime is the same as a module-level variable. A
module-level variable differs from a static variable. In a standard
module or a class module, it retains its value until you stop running
your code.

The code stops running between the first click of the Routine2 command
button and the second click of the button. I guess because the code
is no longer running, the variable loses scope and therefore gets
reinitialized to a null value. So, I guess this is the way it is
designed to work, and there is no way to avoid the variables being
reinitialized. I will use the initializevars subroutine that you
suggested.

Thanks!
- Mary

 

[mspikowski]

But then again... No code is running between the execution of the
Workbook_Open event and the execution of Routine2. Maybe that was not
the answer after all.

 

[NickHK]

If your variables are declared in a standard Module (not a class module nor
a WorkSheet/ThisWorkbook module), then scope is not the answer.
Run this code a few times with Var1 declared in a standard module. Then
uncomment the "End" and see the difference.

Option Explicit

Private Sub CommandButton1_Click()

MsgBox Var1
Var1 = "some text"
MsgBox Var1
'End

End Sub

NickHK

 

[Guest]

It's not about "No code is running", it's "code is NOT running" that resets
the variables. I learned the hard way: I don't bother with initializing much
in the Workbook_Open event anymore. I put my initializations at the
beginning of every event as necessary (and you don't need the "Call"
keyword), e.g.

Private Sub Worksheet_Activate()

InitializeVars
...Do Stuff

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

InitializeVars
...Do Stuff

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

InitializeVars
...etc.

End Sub

 

[Dave Peterson]

You don't need the Call keyword, but it doesn't hurt. I like it.

I'd also add a check to see if re-initializing the variables was required (as
you saw).

But I don't understand your point about why/how the variables are reset. Those
public and static variables don't get reset just because the code isn't running.

 

[Guest]

You're right. I work so much in the IDE that I got tired of finding my
variables reset by my debugging and I got in the habit of initializing them
as necessary.