Quick Question on Variable Scope - "Public" is misbehaving...

[Randall00]

I'm having a hard time understanding how Public variables declared in an Excel workbook retain their values.

Every source I can find suggests that all variables declared as "Public" store values that are available to all procedures across all the workbook modules. These Public variables are supposed to retain their values until the workbook is closed or "End Statement" is used.

I have a procedure sitting in a single module that is set to run when the workbook opens. All it does is dimension two Public variables at the top of the module to refer to the workbook itself and one particular worksheet like this:

Public wb As Workbook
Public wsDATA As Worksheet

Sub WorkbookVars()
    Set wb = ThisWorkbook
    Set wsDATA = wb.Sheets("DATA")
End Sub

There are other procedures in the module that use those variables and when I run them individually, I don't seem to have a problem. However, if I call those procedures from within a UserForm, the Public variables no longer seem to be associated with the workbook and worksheet that I set them to.

In the past, I've worked around this lack of understanding with respect to variable scope by dimensioning the same variables at the top of the form code as well as each and every module in the workbook, but this gets very sloppy and redundant. By the time I've added a handful more procedures into the project, I realize very quickly that I don't have a clue which worksheet is dimensioned to which variable in which module and it seems silly that I should have to continually tell this one workbook that "wsDATA" refers to the DATA sheet!

So anyway, am I missing something about these "Public" variables? All I've been told is that they retain their values until I close the workbook or use "End Statement", yet neither of those things occur in my procedures and the values are clearly not retained....?

 

[Andrew Poulsom]

Your Public variables need to be in a General module, not in the ThisWorkbook or a Worksheet module.

 

[Randall00]

That's the thing, they are in a general module. That is....assuming that when you "Insert->Module" those are considered general modules...I thought calling the procedures from the form might've had something to do with it, but I just can't wrap my head around why I'm losing those values.


EDIT: To clarify, even though I said I dimension these variables when the workbook opens, I don't have them declard at the top of ThisWorkbook's module, rather the "Workbook_Open" event references a procedure in the general module where the public variables are declared.

 

[shg]

Are you sure that they are not also declared elsewhere?

 

[Randall00]

I'll check the form code again tomorrow when I have some time--I guess it's possible. But is that to say that if they are declared somewhere else, the Public scope is lost?

 

[Andrew Poulsom]

To demonstrate that the assignments are retained:

'Module1
 
Public wb As Workbook
 
Sub RunFirst()
    Set wb = ThisWorkbook
End Sub
 
'Module2
 
Sub RunSecond()
    MsgBox wb.Name
End Sub

The variable must only be declared once. If declared in another module it won't be the same variable.