Accessing Public variable in different modules

[Denis]

I'm having trouble accessing a public variable in a module when this
public variable is declared in another module. Here's my situation
where I have 2 forms and 2 modules:

form1

form2

module1
Public TWB As Object
load form1 ' works fine

module2
load form2 ' TWB is Nothing

The TWB public variable is Set in the Autpen() (actually in another
module) so it is always set.

Both form1 and form2 reference TWB in their form initialization but
TWB shows up as set to Nothing when form2 is loaded in module2.
However, if I move the Public statement from module1 to module2 then
loading form2 works fine.

So what am I doing wrong? Supposedly public variables are accessible
across all modules in the same project and I'm not using any 'option
private module.'

Denis

 

[Dave Peterson]

Variables can lose their contents (well, what they hold) in a few ways.

Do you have any "End" statements in your code (not "end if", "end sub", "end
function"--just "End" by itself)?

Have you reset the project (clicking the reset button while debugging the code)?

You may want to create a dedicated routine that initializes these global
variables.

Then add one more:

Public VariablesAreInitialized as boolean

Then later you can use:

if variablesareinitialized then
'keep going
else
call dedicatedroutinetoinitializevariables 'not rely on Auto_open
end if

sub dedicatedroutinetoinitializevariables()
set TWB = somethingoranother
...all you need
variablesareinitialized = true
end sub

just in case something unexpected goes wrong.

(or
if twb is nothing then
call dedicatedroutinetoinitializevariables
end if




Another guess.
You have another TWB that's local to that procedure/module that's
uninitialized. And your references aren't to the public TWB, but to that local
variable.

 

[Denis]

Variables can lose their contents (well, what they hold) in a few ways.

Do you have any "End" statements in your code (not "end if", "end sub", "end
function"--just "End" by itself)?

Have you reset the project (clicking the reset button while debugging the code)?

You may want to create a dedicated routine that initializes these global
variables.

Then add one more:

Public VariablesAreInitialized as boolean

Then later you can use:

if variablesareinitialized then
'keep going
else
call dedicatedroutinetoinitializevariables 'not rely on Auto_open
end if

sub dedicatedroutinetoinitializevariables()
set TWB = somethingoranother
...all you need
variablesareinitialized = true
end sub

just in case something unexpected goes wrong.

(or
if twb is nothing then
call dedicatedroutinetoinitializevariables
end if

Another guess.
You have another TWB that's local to that procedure/module that's
uninitialized. And your references aren't to the public TWB, but to that local
variable.

Dave Peterson

I've checked through these suggestions and can't find anything wrong.
In particular I looked at every End occurrence to see if there was a
plain End and I looked for any multiple TWB definitions.

The re-init routine is a reasonable suggestion but I don't know if I
can do that. All my auto_open() does is:
Set TWB = ActiveWorkbook

I do this in auto_open so I can grab this workbook without having to
know its workbook name (eg, abc.xls). I need to reference other
sheets in this particular workbook and by setting this object variable
I can do that without having to know the name of this workbook.
However, it is the active workbook only when it is initially opened.
If there were some other way to "grab" this workbook, then I could
bypass this Public variable entirely.

Denis

 

[Dave Peterson]

Is the code in the workbook that's active to start with?

If yes, then you could drop TWB and just use ThisWorkbook.

If no, then there's still something that's killing that variable.

Maybe you could add a few lines to help you debug the problem.

if twb is nothing then
debug.print "some_indicator_here lost the TWB"
else
debug.print "some_indicator_here and TWB is ok"
end if

Change the indicator so that you can tell where the problem occurs--then try
narrowing it down.

Alternatively, if the code is in another workbook (and addin???):
Store your variables in a worksheet in that addin.

ps. There's no chance that you actually closed the activeworkbook somewhere in
your code is there?

 

[Denis]

Well, the ThisWorkbook is what I really wanted but I didn't know about
it. It is obviously a much cleaner solution. Hopefully, I'll never
have to revisit the Public access again. It is interesting that when
I switched the Public declaration to the other module everything
worked. I had assumed that it would fail someplace else but it
didn't.

I've had this .xls around for a while but had to upgrade it from an
XLS95 project where I used to be able to use a DoMacroOptions to add
menu items under Tools. That no longer works but I just happened to
find a snippet of code you posted in Dec 2005 that works just fine for
adding menu items. So thanks for that help too!

Denis

 

[Dave Peterson]

Glad you got it working. But we(?) still don't know why that object variable
was reset.

Are you worried?

(I'm not if you're not <vbg>.)

 

[Denis]

Glad you got it working. But we(?) still don't know why that object variable
was reset.

Are you worried?

(I'm not if you're not <vbg>.)

I wouldn't say I'm worried since I don't have any other Public
variables so they can't cause me any trouble. Of course, it would be
preferable to know what happened since there's always the chance I
might need a Public variable in the future and could get bit by
whatever happened here.

Again, thanks for the help. I may not have figured out the problem
but I ended up with a better solution.

Denis