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


"Peter T" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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




"Peter T" wrote:

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

"Brettjg" wrote in message
...
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?

"Tim Williams" wrote:

A variable, once assigned a value, should keep that value as
long
as
it's
in
scope.
Provided of course you aren't doing anything like calling
"End"...

If your variables are losing their values then something else is
going
on,
which is difficult to diagnose without your code.

Tim



"Brettjg" wrote in message
...
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