Problems with global variables in Excel VBA

[Geoff65]

I am having a problem with the behaviour of global variables and I am hoping someone can help me.

Without dumping lots of code, the structure is as in the image with a description of the behaviour I am seeing after that.

The function of the code is to display a bunch of data and wait while the user decides what actions to take to change how it is displayed or to edit the data in different ways by using the command buttons.

1) sub_in_main_ONE: Sets the values of the public variables and dynamically creates the command buttons

2) When button 1 is clicked: VARIABLE1 is accessible and has the correct value in the click module. It also is accessible and has the correct value in sub_in_main_TWO. Based on everything I have read about variable scope, this is what I expected and designed for.

2) When button 2 is clicked:VARIABLE2 is NOT accessible in the click module. It is also NOT accessible in sub_in_main_THREE. This is not what I was expecting AND it is inconsistent with behaviour for BUTTON 1.

4) When sub_in_main_THREE is called from sub_in_main_ONE: VARIABLE 2 is accessible and has the correct value. This is what I expect from my understanding of variable scope.

I don't understand what I am doing wrong for BUTTON 2 because everything is fine for BUTTON 1 and it seems be just the same. Am I declaring the global variables in the wrong place or in the wrong way?

I did not expect to find that access to public variables within sub_in_main_THREE would depend on where it was called from.

Can someone guide me on why I see the problem I am encountering and how to fix it

Thanks in advance.

Geoff

 

[jasonb75]

VARIABLE2 is NOT accessible in the click module.

Are you seeing a runtime error message or is the variable simply returning an incorrect / empty value?
If it is empty then it would suggest that either the variable has not been initialised, or that an action has occurred that would dump the value of the variable and revert to the default.

 

[Geoff65]

No compilation or runtime errors. Just incorrect / empty

The value of the problem variable is initialised and it can be shown as correct and accessed through all the subs in the Module 1

 

[Geoff65]

I should add that I am using the OPTION EXPLICIT facility.

 

[Geoff65]

or that an action has occurred that would dump the value of the variable and revert to the default.

What kind of action ?

 

[jasonb75]

What kind of action ?

Using the End command to terminate a procedure will reset all variables.

Based on how you describe your code there is no reason why it should fail. Without seeing how the values are assigned to the variables in the proper code, we can do nothing more than guess at things that may or may not be the cause.

 

[Geoff65]

Hi JasonB

Actually the code is as in the original image. There was no more. It was just a much simplified version of my main project to test and understand the behaviour I was seeing.

Anyway, I resolved the problem and the solution I came up with fits better with what I really wanted to achieve.

So, all good now, but thank you for taking the trouble to respond

Geoff

 

[jasonb75]

Actually the code is as in the original image.

It doesn't look like any valid code I've seen with the additional symbols that are in it, I thought that it was just psuedo code that you have typed up quickly as an example.

In your image you have assigned a value to 'var1' but queried 'variable1' in your procedures, with different names they will not match so the variable that was queried should be empty.