Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
NOT static variable problem
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 -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
NOT static variable problem
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 "Neal Zimm" wrote: 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 -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
NOT static variable problem
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" wrote in message ... 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 -- Neal Z |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
NOT static variable problem
Thanks much Bob, pls see my response to Rowan, (above)
Neal "Bob Phillips" wrote: 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" wrote in message ... 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 -- Neal Z |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
NOT static variable problem
Thanks Rowan, your solution worked just fine.
I went back to the Excel help 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 "Rowan" wrote: 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 "Neal Zimm" wrote: 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 -- Neal Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenating two variable fields into a static length text field | Excel Worksheet Functions | |||
Static Date and Time code problem | Excel Discussion (Misc queries) | |||
STATIC VARIABLE NOT AVAILABLE IN ANOTHER MODULE | Excel Discussion (Misc queries) | |||
Excel static current date/time problem | Excel Worksheet Functions | |||
Static Variable | Excel Programming |