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

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

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

Refer the below URLs on Public/Const statements in VBScript

http://msdn.microsoft.com/en-us/libr...z8(VS.85).aspx
http://msdn.microsoft.com/en-us/libr...ed(VS.85).aspx

If this post helps click Yes
---------------
Jacob Skaria


"Brettjg" wrote:

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

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

"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

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

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?

"Jacob Skaria" wrote:

Refer the below URLs on Public/Const statements in VBScript

http://msdn.microsoft.com/en-us/libr...z8(VS.85).aspx
http://msdn.microsoft.com/en-us/libr...ed(VS.85).aspx

If this post helps click Yes
---------------
Jacob Skaria


"Brettjg" wrote:

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

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

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

"Brettjg" wrote:

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?

"Jacob Skaria" wrote:

Refer the below URLs on Public/Const statements in VBScript

http://msdn.microsoft.com/en-us/libr...z8(VS.85).aspx
http://msdn.microsoft.com/en-us/libr...ed(VS.85).aspx

If this post helps click Yes
---------------
Jacob Skaria


"Brettjg" wrote:

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

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

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

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

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

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

"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

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