Amnesia of global object variables

[nigelandrewfoster]

Is there any way of declaring and setting the value of a global object variable so it keeps its value when the procedure ends? The system I'm developing runs separate macros according to the user's clicking on clip art, returning the user to Excel level after each one has run. As it stands, the moment the macro finishes, all the global object variables I have declared are cleared, so the next click generates an "Object variable or With block not set" error. I have gotten around this by writing a macro which simply contains SET statements for them all, and each macro then starts with a CALL command to this macro. Is that the correct way to go, or is there a more direct way, please? As ever, I appreciate your time!
Nigel

 

[Norie]

Nigel

Where are you declaring the varible(s) and how do you initially set them?

Is there anything in the code being executed that could cause the reference to be lost?

 

[nigelandrewfoster]

Hi,
Public statement in declarations above regular modules. Originally followed advice to put SET statements in WorkbooksOpen procedure in ThisWorkbook module. Didn't work. So just put a CALL there instead to a Sub called Initialise_Globals which just contains my set statements. That way, when the control is passed back to Excel and the definitions are lost, I can just put a CALL statement at the beginning of each macro I'm running.

 

[nigelandrewfoster]

So module 1 looks like this:

Option Explicit
Public Jimmy As Range, Pog As Range

Sub James()
    Call Initialise_Globals
    Jimmy = "Hi, I'm Jimmy!"
    Pog = "I'm not!"
End Sub

Thisworkbook looks like this:

Option Explicit

Private Sub WorkbookOpen()
    Call Initialise_Globals
End Sub

and module 2 looks like this:

Option Explicit

Sub Initialise_Globals()
    Set Jimmy = Workbooks("Nigel Foster").Worksheets("Nigel").Range("d12")
    Set Pog = Workbooks("Nigel Foster").Worksheets("Nigel").Range("d16")
End Sub

 

[Norie]

Is it definitely WorkbookOpen and not Workbook_Open?

That's the only problem I can see.

 

[nigelandrewfoster]

Have changed it to workbook_open. Thanks. My front end consists of a table of order information. The user selects the ORDER worksheet tab which automatically runs a macro to display a listbox with customer names in. The user selects a name and the front end table displays the relevant customer's order info (which is taken from a hidden, compact master table). So far, so good. The user makes amendments and then clicks "SAVE" wordart to run another macro, updating the master table. There will be numerous other buttons added. Do I have to initialise globals every time control passes back to the user and the user runs a new macro? (i.e. Does the values of global object variables get lost each time a procedure ends? Because they appear to.) Thanks.

 

[RoryA]

No, unless your code does something that resets the project. I note you mention listboxes - are you using an activex listbox on a sheet? Adding one of those will reset the project.

 

[nigelandrewfoster]

Maybe it's when I'm debugging and I have to RESET in order to run the next macro? I've gotten round it by placing CALL initialise_globals command at start of every macro that a user could initiate. I'll do some more experimenting. No, not an active x one. I call it into existence via a .show command.
Thanks
Nigel

 

[RoryA]

Yes - if you reset, all your globals will get reset too.

 

[Norie]

Nigel

Are you just putting the call to create initialise the variables at the start of each module for debugging?