Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Public variable in different modules
I'm having trouble accessing a public variable in a module when this
public variable is declared in another module. Here's my situation where I have 2 forms and 2 modules: form1 form2 module1 Public TWB As Object load form1 ' works fine module2 load form2 ' TWB is Nothing The TWB public variable is Set in the Auto_Open() (actually in another module) so it is always set. Both form1 and form2 reference TWB in their form initialization but TWB shows up as set to Nothing when form2 is loaded in module2. However, if I move the Public statement from module1 to module2 then loading form2 works fine. So what am I doing wrong? Supposedly public variables are accessible across all modules in the same project and I'm not using any 'option private module.' Denis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Public variable in different modules
Variables can lose their contents (well, what they hold) in a few ways.
Do you have any "End" statements in your code (not "end if", "end sub", "end function"--just "End" by itself)? Have you reset the project (clicking the reset button while debugging the code)? You may want to create a dedicated routine that initializes these global variables. Then add one mo Public VariablesAreInitialized as boolean Then later you can use: if variablesareinitialized then 'keep going else call dedicatedroutinetoinitializevariables 'not rely on Auto_open end if sub dedicatedroutinetoinitializevariables() set TWB = somethingoranother ...all you need variablesareinitialized = true end sub just in case something unexpected goes wrong. (or if twb is nothing then call dedicatedroutinetoinitializevariables end if Another guess. You have another TWB that's local to that procedure/module that's uninitialized. And your references aren't to the public TWB, but to that local variable. Denis wrote: I'm having trouble accessing a public variable in a module when this public variable is declared in another module. Here's my situation where I have 2 forms and 2 modules: form1 form2 module1 Public TWB As Object load form1 ' works fine module2 load form2 ' TWB is Nothing The TWB public variable is Set in the Auto_Open() (actually in another module) so it is always set. Both form1 and form2 reference TWB in their form initialization but TWB shows up as set to Nothing when form2 is loaded in module2. However, if I move the Public statement from module1 to module2 then loading form2 works fine. So what am I doing wrong? Supposedly public variables are accessible across all modules in the same project and I'm not using any 'option private module.' Denis -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Public variable in different modules
On May 16, 2:21 pm, Dave Peterson wrote:
Variables can lose their contents (well, what they hold) in a few ways. Do you have any "End" statements in your code (not "end if", "end sub", "end function"--just "End" by itself)? Have you reset the project (clicking the reset button while debugging the code)? You may want to create a dedicated routine that initializes these global variables. Then add one mo Public VariablesAreInitialized as boolean Then later you can use: if variablesareinitialized then 'keep going else call dedicatedroutinetoinitializevariables 'not rely on Auto_open end if sub dedicatedroutinetoinitializevariables() set TWB = somethingoranother ...all you need variablesareinitialized = true end sub just in case something unexpected goes wrong. (or if twb is nothing then call dedicatedroutinetoinitializevariables end if Another guess. You have another TWB that's local to that procedure/module that's uninitialized. And your references aren't to the public TWB, but to that local variable. Dave Peterson I've checked through these suggestions and can't find anything wrong. In particular I looked at every End occurrence to see if there was a plain End and I looked for any multiple TWB definitions. The re-init routine is a reasonable suggestion but I don't know if I can do that. All my auto_open() does is: Set TWB = ActiveWorkbook I do this in auto_open so I can grab this workbook without having to know its workbook name (eg, abc.xls). I need to reference other sheets in this particular workbook and by setting this object variable I can do that without having to know the name of this workbook. However, it is the active workbook only when it is initially opened. If there were some other way to "grab" this workbook, then I could bypass this Public variable entirely. Denis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Public variable in different modules
Is the code in the workbook that's active to start with?
If yes, then you could drop TWB and just use ThisWorkbook. If no, then there's still something that's killing that variable. Maybe you could add a few lines to help you debug the problem. if twb is nothing then debug.print "some_indicator_here lost the TWB" else debug.print "some_indicator_here and TWB is ok" end if Change the indicator so that you can tell where the problem occurs--then try narrowing it down. Alternatively, if the code is in another workbook (and addin???): Store your variables in a worksheet in that addin. ps. There's no chance that you actually closed the activeworkbook somewhere in your code is there? Denis wrote: On May 16, 2:21 pm, Dave Peterson wrote: Variables can lose their contents (well, what they hold) in a few ways. Do you have any "End" statements in your code (not "end if", "end sub", "end function"--just "End" by itself)? Have you reset the project (clicking the reset button while debugging the code)? You may want to create a dedicated routine that initializes these global variables. Then add one mo Public VariablesAreInitialized as boolean Then later you can use: if variablesareinitialized then 'keep going else call dedicatedroutinetoinitializevariables 'not rely on Auto_open end if sub dedicatedroutinetoinitializevariables() set TWB = somethingoranother ...all you need variablesareinitialized = true end sub just in case something unexpected goes wrong. (or if twb is nothing then call dedicatedroutinetoinitializevariables end if Another guess. You have another TWB that's local to that procedure/module that's uninitialized. And your references aren't to the public TWB, but to that local variable. Dave Peterson I've checked through these suggestions and can't find anything wrong. In particular I looked at every End occurrence to see if there was a plain End and I looked for any multiple TWB definitions. The re-init routine is a reasonable suggestion but I don't know if I can do that. All my auto_open() does is: Set TWB = ActiveWorkbook I do this in auto_open so I can grab this workbook without having to know its workbook name (eg, abc.xls). I need to reference other sheets in this particular workbook and by setting this object variable I can do that without having to know the name of this workbook. However, it is the active workbook only when it is initially opened. If there were some other way to "grab" this workbook, then I could bypass this Public variable entirely. Denis -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Public variable in different modules
Well, the ThisWorkbook is what I really wanted but I didn't know about
it. It is obviously a much cleaner solution. Hopefully, I'll never have to revisit the Public access again. It is interesting that when I switched the Public declaration to the other module everything worked. I had assumed that it would fail someplace else but it didn't. I've had this .xls around for a while but had to upgrade it from an XLS95 project where I used to be able to use a DoMacroOptions to add menu items under Tools. That no longer works but I just happened to find a snippet of code you posted in Dec 2005 that works just fine for adding menu items. So thanks for that help too! Denis |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Public variable in different modules
Glad you got it working. But we(?) still don't know why that object variable
was reset. Are you worried? (I'm not if you're not <vbg.) Denis wrote: Well, the ThisWorkbook is what I really wanted but I didn't know about it. It is obviously a much cleaner solution. Hopefully, I'll never have to revisit the Public access again. It is interesting that when I switched the Public declaration to the other module everything worked. I had assumed that it would fail someplace else but it didn't. I've had this .xls around for a while but had to upgrade it from an XLS95 project where I used to be able to use a DoMacroOptions to add menu items under Tools. That no longer works but I just happened to find a snippet of code you posted in Dec 2005 that works just fine for adding menu items. So thanks for that help too! Denis -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Public variable in different modules
On May 17, 2:28 pm, Dave Peterson wrote:
Glad you got it working. But we(?) still don't know why that object variable was reset. Are you worried? (I'm not if you're not <vbg.) I wouldn't say I'm worried since I don't have any other Public variables so they can't cause me any trouble. Of course, it would be preferable to know what happened since there's always the chance I might need a Public variable in the future and could get bit by whatever happened here. Again, thanks for the help. I may not have figured out the problem but I ended up with a better solution. Denis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public variable | Excel Programming | |||
Public, Private, Event modules, Forms modules,,, | Excel Programming | |||
Problem accessing Oracle Public Synonyms from Microsoft Excel 2002 | Excel Discussion (Misc queries) | |||
Public Variable | Excel Programming | |||
Accessing AddIn Class Modules from Client Worksheet | Excel Programming |