Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm having some trouble with variables in Excel '97...
In the workbook I am currently working on I declare all my public variables
at the module level and set them in the Auto_Open macro, so that Module1 looks like this: Option Explicit Public PSPDI_WS As Worksheet Public PSPWO_WS As Worksheet Public etc, etc... __________________________________ Sub Auto_Open() Set PSPDI_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet1") Set PSPWO_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet2") Set etc, etc... End Sub So that all procedures can call those worksheets at any time. However, I find that after I have run a procedure and it ends, I can no longer use PSPDI_WS to reference my previously set worksheet object when I run another procedure (the same one again, or any other.) Why are my variables being reset, and how do I fix it? I thought a clue might be in the Static statement, but it appears that is only used at procedure level and it's effects are lost when the code stops executing. Thx -Mike-hime |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm having some trouble with variables in Excel '97...
Hi Mike-hime,
I would recommend to run the "another procedure" step by step with F key. At Local window in VBE, please confirm what value the public variabl PSPDI_WS has -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm having some trouble with variables in Excel '97...
Variables are released when macros finish running and need to b
initialised every time a macro is run I suggest you put all your public variables initilation in a su routine called SetPublicVar and call this routine at the start of you macro or use constant -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm having some trouble with variables in Excel '97...
"mudraker " wrote in
message Variables are released when macros finish running and need to be initialised every time a macro is run This is not correct for variables that are declared at the module level, as are those variables in the original post. I suggest you put all your public variables initilation in a sub routine called SetPublicVar and call this routine at the start of your macro or use constants This would prevent any other procedure from accessing the contents of the variables, which would defeat the purpose declaring the variables at the module level. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mudraker " wrote in message ... Variables are released when macros finish running and need to be initialised every time a macro is run I suggest you put all your public variables initilation in a sub routine called SetPublicVar and call this routine at the start of your macro or use constants --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm having some trouble with variables in Excel '97...
With my first reply I meant that the declaration of the variables would
still occur at the module level it is only the setting of the value that would occur in the the sub rotine. Option Explicit Public PSPDI_WS As Worksheet Public PSPWO_WS As Worksheet Public etc, etc... sub SetPublicVar Set PSPDI_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet1") Set PSPWO_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet2") Set etc, etc... end sub sub MainMacro dim variables call SetPublicVar more code here end sub Chip I am under the impression that all module and global variables loose their settings whenever all macro's has finished running which is what would happen in Mikee-hime's case once he exits the the Auto_Open routine If as you have hinted at that my understanding of this is wrong can you please enlighten me & Mike further --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm having some trouble with variables in Excel '97...
The unusual thing is that the variables will retain their value after they
are set until another macro has been triggered and completes it's execution, at which point the setting for all variables is reset to empty. Atleast, it appears this way in XL97. "mudraker " wrote in message ... With my first reply I meant that the declaration of the variables would still occur at the module level it is only the setting of the value that would occur in the the sub rotine. Option Explicit Public PSPDI_WS As Worksheet Public PSPWO_WS As Worksheet Public etc, etc... sub SetPublicVar Set PSPDI_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet1") Set PSPWO_WS = Workbooks("MyWorkbook.xls").Worksheets("MySheet2") Set etc, etc... end sub sub MainMacro dim variables call SetPublicVar more code here end sub Chip I am under the impression that all module and global variables loose their settings whenever all macro's has finished running which is what would happen in Mikee-hime's case once he exits the the Auto_Open routine If as you have hinted at that my understanding of this is wrong can you please enlighten me & Mike further --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not at all clear on use of variables and/or object variables | Excel Discussion (Misc queries) | |||
trouble with excel | Excel Discussion (Misc queries) | |||
Trouble Graphing 1 point w/ 2 variables | Charts and Charting in Excel | |||
Excel trouble | Excel Discussion (Misc queries) | |||
trouble with excel | Excel Discussion (Misc queries) |