NOT static variable problem

[Neal Zimm]

In a module with some 40 procedures I have dim'd a lot of variables
in the declarations section. I am developing this application on a home PC.
It will execute in a networked environment.

NONE of the vars are static. ajcdone is dim'd as string.

in sub "A" I have: ajcdone="y" based on processing status

in sub "B", called in the same 'overall' macro as A above I have:
if ajcjdone <> "y" then.....

In my testing I executed sub A. It ended with ajcdone = to "y" BUT,
sub B was not executed due to other logic, and the 'overall' macro was
exited.

I diddled with the worksheets involved for a couple of minutes and then
independantly executed sub B, NOT expecting ajcdone to have any value,
but be null. Well, surprise surprise.

ajcdone WAS = to "y". How come?
Is this just bad luck with a memory location? or am I doing something wrong?

I suppose I can stick a value in a cell in one of the involved worksheets
and test it, but would rather not.

Thanks for you help.
Neal

 

[Rowan]

Neal

Variables dim'd in the declarations section retain there value between
macros. So if sub A is setting the variable to "y" the value will stay as "y"
until you set it otherwise of close the file.

For this reason it may be better to declare the variable in macro A and pass
it to macro B eg:

sub A()
dim ajcjdone as string
ajcjdone = "y"
call B(ajcjdone)
end sub

sub B(ajcjdone as string)
'code
end sub

Hope this helps
Rowan

 

[Bob Phillips]

Static variables have procedure level scope, and are a way of retaining
values in a procedure over subsequent calls (they are all initialised on
entry otherwise). Module level variables are implicitly static, even though
not explicitly declared as such, and thus retain their values between calls.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Neal Zimm]

Thanks much Bob, pls see my response to Rowan, (above)
Neal

 

[Neal Zimm]

Thanks Rowan, your solution worked just fine.
I went back to the Excel help re: Static, I read it twice and
NOWHERE does it say anything about 'closing the workbook'.
I had just assumed that when a macro ended, and you're back to
working in the worksheets, that the 'memory' slate is wiped
clean from the macros which ran. Silly me.
Thanks,
N