Public variable (or Public Const?)

[Brettjg]

I have my main workbook called "1. FINANCE" with six sheets in it. About 10
other workbooks run off the main one doing diffrent tasks. I've set up a
Public Variable in a normal module in PERSONAL.xls and set FINbk (whick is 1.
FINANCE) and all of it's sheets such as F_PERS etc. This code works as it
should, and the variables are set when FINbk opens, and are cleared when it
closes.

However, it seems to me that the Public Variables do not retain their values
after a run of a few macros. Does that mean that I should be using a Public
Const instead, and if so won't I get an error msg when I try to set it for
the first time (1. FINANCE is the master name but when I do a run it has the
client's surname attached "1. FINANCE Walkenbach.xls"

Just wrapping my head around the whole Public thing (never did care much for
the General Public . Regards, Brett

 

[Brettjg]

Hi Tim, does that means that if I have a macro go into debug then the values
will be destroyed? If so how can I preserve them please?

 

[Peter T]

Simply going into debug mode, eg set a break and step through, does not
cause variables to lose scope. However if you press the reset button or do
anything to make your code recompile, such as adding declarations or editing
code in certain ways, would indeed destroy your variables.

Regards,
Peter T

 

[Jacob Skaria]

Refer the below URLs on Public/Const statements in VBScript

http://msdn.microsoft.com/en-us/library/72bd95z8(VS.85).aspx
http://msdn.microsoft.com/en-us/library/16twy8ed(VS.85).aspx

If this post helps click Yes

 

[Brettjg]

Thanks Peter. I see, that may well be what's happening here. I'll do some
further testing. It may transpire that I'll use a hidden workbook to write
data to in an error handling routine (I've been thinking about that for a
while anyway). My current handler plays a random crash WAV, generates a
msgbox with the error, Err.clear, before being forced back into the error so
that I can review the values etc in debug mode - so it would only be an
extension of this (I think!). Regards, Brett

 

[Brettjg]

Hi Peter, still having trouble wih this. I have the following:

blah, blah code
Application.Run "FINANCE_NAMES_SET"
FINbk.Activate

It debugs on the last line, saying that it's not set, but as you can see
from the macro below, it's just been set! The msgbox in the macro below
confirms it for me.
BTW there is only ever one instance of a "1. FINANCE xxxxxxxx.xls" open at
one time. All the variables except wb are Public.

Sub FINANCE_NAMES_SET()
Dim wb As Workbook
For Each wb In Workbooks
If Left(wb.Name, 10) = "1. FINANCE" Then
Application.Run "WAV_VARIABLES_SET" 'this just plays a WAV file
Set FINbk = Workbooks(wb.Name)
Set F_LNCH = FINbk.Sheets("LAUNCHPAD")
Set F_NOTES = FINbk.Sheets("NOTES")
Set F_PERS = FINbk.Sheets("PERSONAL")
Set F_ASS = FINbk.Sheets("ASSETS")
Set F_LOANS = FINbk.Sheets("LOANS")
Set F_BUY = FINbk.Sheets("BUY")
Set F_REF = FINbk.Sheets("Refinance")
FINANCE = F_LNCH.Range("Win.FINANCE").Value
F_snm = F_PERS.Range("nm.last.1").Value
F_init = Left(F_PERS.Range("nm.first.1"), 1)
MsgBox FINbk.Name
Exit For
End If
Next wb
End Sub

The really bizarre part is that all the other variables have got the correct
values still in them when it goes to debug (and as you can see, they get
their values from FINbk).

HOWEVER, IF I DO IT LIKE THIS (i.e. just copy the macro into the main code):

For Each wb In Workbooks
If Left(wb.Name, 10) = "1. FINANCE" Then
Application.Run "WAV_VARIABLES_SET" 'this just plays a WAV file
Set FINbk = Workbooks(wb.Name)
Set F_LNCH = FINbk.Sheets("LAUNCHPAD")
Set F_NOTES = FINbk.Sheets("NOTES")
Set F_PERS = FINbk.Sheets("PERSONAL")
Set F_ASS = FINbk.Sheets("ASSETS")
Set F_LOANS = FINbk.Sheets("LOANS")
Set F_BUY = FINbk.Sheets("BUY")
Set F_REF = FINbk.Sheets("Refinance")
FINANCE = F_LNCH.Range("Win.FINANCE").Value
F_snm = F_PERS.Range("nm.last.1").Value
F_init = Left(F_PERS.Range("nm.first.1"), 1)
MsgBox FINbk.Name
Exit For
End If
Next wb
FINbk.Activate

IT WORKS. But I can't really do it like this because there are dozens if
instances, and that would defeat the purpose of using Public variables
completely.

I'm completely stumped. Regards, Brett

 

[Brettjg]

Hi Jacob, I read both of those but unfortunately none the wiser - I think
I've done everything that is suggested there. Is there something that I'm not
catching?

 

[Peter T]

I'm sorry but I haven't a clue about what you are trying to say

It debugs on the last line, saying that it's not set, but as you can see
from the macro below, it's just been set!

what the last line, End Sub ?, in the calling wb or in the macro????
what's not set, how/where does it say "not set"

If you mean FINbk where is FINbk. Is that declared at module level, if so is
it in the workbook that you called the app.run or is it in the same wb as
FINANCE_NAMES_SET, if indeed they are not in the same wb's.
I could go on with loads more q's but it's just a guessing game from this
end trying to imagine what you might have.

OK, a long shot guess. You've got module level variables in one wb (in which
you do app.run) which you attempt to assign from a macro in another wb. That
won't work. You'll get the illusion that you've set say FINbk, but that's
merely created as an undeclared variable in the macro, it'll lose scope as
soon as the macro terminates. Of course you could be trying to do anyone of
several other things.

Regards,
Peter T

 

[Brettjg]

THIS POST NO LONGER REQUIRED - I"M GOING TO REPOST

 

[Brettjg]

FINbk is a Public variable. When I say it debugs on the last line I mean the
line FINbk.Activate (immediately after it has been set by calling the macro
on the line above it).

In other words, FINbk is empty immediately after running the macro
FINANCE_NAMES_SET where I set the Public variable FINbk to a workbook name
(which is verified by the msgbox).

This all happens in the PERSONAL.xls.

However, even though it's empty when I set it by running FINANCE_NAMES_SET ,
if I copy the code of the macro into the calling macro and then have as the
next line FINbk.activate it works. So in other words even though I set a
Public variable successfully after the macro that sets it is finished the
variable is empty.

 

[Peter T]

This all happens in the PERSONAL.xls.

If so why are you using app.run within Personal to call a macro that's also
in Personal. It doesn't make sense. Or do you have code in two workbooks. If
so, which workbook is the public FINbk and which workbook is the macro
FINANCE_NAMES_SET

Regards,
Peter T

 

[Brettjg]

It's all in PERSONAL. I guess I've just gotten into the habit of
Application.Run "FINANCE_NAMES_SET" but maybe I could express that another
way. Is this part of the problem do you think?

 

[Peter T]

app'.run is not the reason for your problem but it completely threw me in
trying to work out what you were doing. The only time you'd want to use
app.run to call a procedure in the same project is if you want to assign the
procedure name to a string variable. Even then there are probably better
ways of doing it. Best get out of that habit!

Again guessing, maybe you declared your module level variables as private
(or simply Dim) in a different module to that of the macro. You would
quickly pick things like that up if you head all your modules Option
Explicit (that would be a good habit to get into)

Personally I wouldn't want all those global object variables hanging around
in my Personal, and probably not anywhere else for that matter. Try and
rearrange so you can work without them where possible, eg pass variables
between procedures.

Regards,
Peter T

 

[Brettjg]

Hi Peter
After recently reading Walkenbach I'm a convert to Option Explicit (all but
two of modules have it). I'm going to repost this question, and therefore
close this post (I think I've confused you enough). Thanks very much for your
input thus far. Brett

 

[Brettjg]

HOLY (work)SHEET BATMAN. I've found it. There was (somehow, after very
careful checking) one last remaining Dim FINbk as Worksheet at module level -
therefore not Public and not retaining it's value. And of course it had to be
the very first module that I was testing. I think I would have spotted this
much sooner if it had been in a module a little bit further down the track in
the run.

Actually I would have thought that compiling would have picked this up as a
duplicate declaration (given that the first dec is Public), but apparently
not. You'd think that the compiler would at the very least force you to
Redim. Thanks for your help Peter - I feel guilty that I've wasted your time
and perhaps frustration at my inept explanations. Regards, Brett