Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Globals Go "Poof!": Workarounds?
In Workbook_Open() I'm capturing a couple of global variable values:
the workbook's name, and it's full path. Naturally, when code is interrupted, the globals go "Poof!". This is expected.... but I'm unable to make Workbook_Open a public routine and call it as needed. Seems like I need an alternative to global variables. First thing that comes to mind is an invisible "System" or "Application" worksheet that gets populated by Workbook_Open. Then I write a couple of functions to grab the desired values from that invisible sheet. Does this sound like good practice? Alternatives? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Globals Go "Poof!": Workarounds?
First, if all you're doing is saving the workbook's name and its path, you could
use something like: Thisworkbook.name Thisworkbook.fullname Thisworkbook.path ThisWorkbook is the workbook that owns the code. And you can use that in any of your routines. But for other stuff, you may want to set up an initialization routine. Then you can run that initialization routine whenever you need to: Sub Workbook_Open() Call InitialVars End sub ======== In a general module: Sub InitializeVars() public VarsAreInitialized as boolean public var1 as long public var2 as string public var3 as worksheet varsareintialized = true var1 = 323 var2 = "Ok" set var3 = thisworkbook.worksheets("sheet1") End sub Then in any routine, you can check to see if your variables are ok: Sub anyroutine() if varsareintialized then 'perfect, do nothing! else call initializevars end if 'stuff that does some real work end sub ========== If those variables change in other procedures, then this wouldn't be the way to go--since it's just setting them back to their initial state. PeteCresswell wrote: In Workbook_Open() I'm capturing a couple of global variable values: the workbook's name, and it's full path. Naturally, when code is interrupted, the globals go "Poof!". This is expected.... but I'm unable to make Workbook_Open a public routine and call it as needed. Seems like I need an alternative to global variables. First thing that comes to mind is an invisible "System" or "Application" worksheet that gets populated by Workbook_Open. Then I write a couple of functions to grab the desired values from that invisible sheet. Does this sound like good practice? Alternatives? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Globals Go "Poof!": Workarounds?
On May 22, 10:04 pm, Dave Peterson wrote:
First, if all you're doing is saving the workbook's name and its path, you could use something like: Thisworkbook.name Thisworkbook.fullname Thisworkbook.path I should have mentioned this in the OP: problem is that when a user double-clicks a .XLS, Windows has this nasty habit of opening up the .XLS in whatever instance of Excel already happens tb running. Hence, at any given time "ThisWorkBook" could be something besides the one I'm working in. Also, some of my routines open up "Temp" spreadsheets and copy worksheets from them into my "Real" workbook. Bottom line is that I want (need?) to make all my object references explicit - by name - instead of by number. e.g. ".WorkBooks("Whatever") instead of ".WorkBooks(2)". I'm pretty sure the business of opening into an existing Excel instance could be altered via some registry entry or another.... but I want this thing tb portable without any OS customization. So I'm back to the question of whether squirreling away those intially- acquired values in an invisible "System" worksheet is good practice. Haven't seen anything to the contrary. Once I get on top of some other issues I'll go that route if nobody gives a reason not to. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Globals Go "Poof!": Workarounds?
Dave's solution works:
Thisworkbook ALWAYS refers to the workbook that contains the VBA statement: it does not matter how many workbooks are open or which workbooks are active. The usual way of handling all this is to use Workbook object variables: Set oRealBook=Thisworkbook set oTempBook=Workbooks("Temp.xls") regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "PeteCresswell" wrote in message ... On May 22, 10:04 pm, Dave Peterson wrote: First, if all you're doing is saving the workbook's name and its path, you could use something like: Thisworkbook.name Thisworkbook.fullname Thisworkbook.path I should have mentioned this in the OP: problem is that when a user double-clicks a .XLS, Windows has this nasty habit of opening up the .XLS in whatever instance of Excel already happens tb running. Hence, at any given time "ThisWorkBook" could be something besides the one I'm working in. Also, some of my routines open up "Temp" spreadsheets and copy worksheets from them into my "Real" workbook. Bottom line is that I want (need?) to make all my object references explicit - by name - instead of by number. e.g. ".WorkBooks("Whatever") instead of ".WorkBooks(2)". I'm pretty sure the business of opening into an existing Excel instance could be altered via some registry entry or another.... but I want this thing tb portable without any OS customization. So I'm back to the question of whether squirreling away those intially- acquired values in an invisible "System" worksheet is good practice. Haven't seen anything to the contrary. Once I get on top of some other issues I'll go that route if nobody gives a reason not to. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming |