I'm having some trouble with variables in Excel '97...

[Mike-hime]

In the workbook I am currently working on I declare all my public variables
at the module level and set them in the Auto_Open macro, so that Module1
looks like this:

Option Explicit

Public PSPDI_WS As Worksheet
Public PSPWO_WS As Worksheet
Public etc, etc...
__________________________________

Sub Auto_Open()

Set PSPDI_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet1")
Set PSPWO_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet2")
Set etc, etc...

End Sub

So that all procedures can call those worksheets at any time. However, I
find that after I have run a procedure and it ends, I can no longer use
PSPDI_WS to reference my previously set worksheet object when I run another
procedure (the same one again, or any other.) Why are my variables being
reset, and how do I fix it?

I thought a clue might be in the Static statement, but it appears that is
only used at procedure level and it's effects are lost when the code stops
executing.

Thx
-Mike-hime

 

[Colo]

Hi Mike-hime,

I would recommend to run the "another procedure" step by step with F
key.
At Local window in VBE, please confirm what value the public variabl
PSPDI_WS has

 

[mudraker]

Variables are released when macros finish running and need to b
initialised every time a macro is run


I suggest you put all your public variables initilation in a su
routine called SetPublicVar and call this routine at the start of you
macro or use constant

 

[Chip Pearson]

Variables are released when macros finish running and need to be
initialised every time a macro is run

This is not correct for variables that are declared at the module
level, as are those variables in the original post.

I suggest you put all your public variables initilation in a sub
routine called SetPublicVar and call this routine at the start of your
macro or use constants

This would prevent any other procedure from accessing the
contents of the variables, which would defeat the purpose
declaring the variables at the module level.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[mudraker]

With my first reply I meant that the declaration of the variables would
still occur at the module level it is only the setting of the value
that would occur in the the sub rotine.

Option Explicit

Public PSPDI_WS As Worksheet
Public PSPWO_WS As Worksheet
Public etc, etc...


sub SetPublicVar

Set PSPDI_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet1")
Set PSPWO_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet2")
Set etc, etc...

end sub

sub MainMacro
dim variables

call SetPublicVar

more code here

end sub





Chip

I am under the impression that all module and global variables loose
their settings whenever all macro's has finished running which is what
would happen in Mikee-hime's case once he exits the the Auto_Open
routine

If as you have hinted at that my understanding of this is wrong can you
please enlighten me & Mike further

 

[Mike-hime]

The unusual thing is that the variables will retain their value after they
are set until another macro has been triggered and completes it's execution,
at which point the setting for all variables is reset to empty.

Atleast, it appears this way in XL97.