Global variables reset automatically

Question

I want to define a global variable as a worksheet on opening a file, so I have used the following code:

In Module1:

Public mySheet As Worksheet

In ThisWorkbook:

Sub Workbook_Open()  
    Set mySheet = Sheet1   
End Sub

I want to then use mySheet to refer to this particular worksheet throughout various procedures, some of which refer back to this worksheet having opened a new file.

It works initially - when I open the file the variable is set, and a macro involving mySheet.Unprotect, mySheet.Protect, and mySheet.Range("A1") works. However, when I try to run it again I get an error Object variable or With block variable not set, and the debug takes me to the mySheet.Unprotect line, which is the first time the sheet is referenced.

How can I define this worksheet in a global variable so that the definition sticks?

For reference, the particular macro I am referring to is below, although I have had a similar problem with different bits of code:

Sub mySub()
    mySheet.Unprotect 
    With Application.FileDialog(msoFileDialogOpen)  
        .AllowMultiSelect = False           
        If .Show <> 0 Then
            mySheet.Range("A1") = .SelectedItems(1)
        End If           
    End With

    mySheet.Protect        
End Sub

define `mysheet` as `constant` with `sheet1`, would this work? — psychicebola, Sep 08 '15 at 12:05
@psychicebola VBA doesn't support object constants, they have to be initialized inside of a procedure. — SierraOscar, Sep 08 '15 at 12:12
Try [debugging](http://www.cpearson.com/excel/DebuggingVBA.aspx) your code using a locals window to see what's happening to the variable. That fact that it runs first time tells me there's something wrong with the procedure - not the way you're assigning the variable. — SierraOscar, Sep 08 '15 at 12:13
@aprados the `Global` keyword just allows the variable to be accessed by referencing the project from another project - for the purpose of this question, `Public` and `Global` will do the same thing. — SierraOscar, Sep 08 '15 at 12:15
_"some of which refer back to this worksheet having opened a new file."_ - does this mean that you close the workbook that the variable was set in? This will cause the code to fail because the worksheet object doesn't exist anymore. — SierraOscar, Sep 08 '15 at 12:17
@Macro Man No I don't close it. And at this stage it is failing just on the code I gave. I have tried using the locals window but the variable doesn't seem to change... — ajor, Sep 08 '15 at 12:20
Also, if I define mySheet separately in each sub, I don't seem to have a problem. — ajor, Sep 08 '15 at 12:22
When you open the "new" workbook - do you open it manually, or is it opened by some code in the first workbook? — SierraOscar, Sep 08 '15 at 12:24
It's opened by code in the workbook. However, as I say, that is not causing the problem since I have removed all the code relating to the "new" workbook and I still have the same issue with only the code above in `mySub()` — ajor, Sep 08 '15 at 12:28
It sounds like you're debugging various macros and re-running this as part of that. Am I right? If so, if you have clicked End on any other errors, this will reset your code and clear your variables. You would need to re-run the code in your workbook Open event to repopulate the variable. — Dave, Sep 08 '15 at 12:35
No, I am only running the macro given in the OP. I have tried re-opening the file several times and still encounter the issue. — ajor, Sep 08 '15 at 12:40
Take a look at [this](http://stackoverflow.com/q/31536519/1652222) question. — ManishChristian, Sep 08 '15 at 13:31
@Nelly27281 as I have explained, opening a new workbook is NOT the problem. The problem occurs when all of that part of the code is completely removed, and when I use only the code I have given in the original post. — ajor, Sep 08 '15 at 14:40

mansi rastogi · Answer 1 · 2015-09-08T13:23:26.840

As mentioned in the link from other answer, you can understand why that happens.

Here is what I would recommend to bypass the problem.

Create a procedure in a module. Name it say, Init

Public mySheet As Worksheet

Sub Init()
   Set mySheet = Sheet1
End Sub

And then in your Workbook_Open() do this.

Sub Workbook_Open()
    Init
End Sub

Now where ever you are using that object simply add one more line

Sub mySub()
    If mySheet is Nothing then Call Init '<== Add this

    mySheet.Unprotect

    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        If .Show <> 0 Then
            mySheet.Range("A1") = .SelectedItems(1)
        End If
    End With
    mySheet.Protect
End Sub

++ nice suggestion :) – Siddharth Rout Sep 08 '15 at 13:25 — Siddharth Rout, Sep 08 '15 at 13:25

score 7 · Accepted Answer · answered Sep 08 '15 at 13:45

Personally I would suggest you create a Function that returns that sheet. Then you can basically use the function name as a Worksheet variable. (Although if you always want the same sheet you could just use the Sheet1 codename)

Function MySheet() As Excel.Worksheet
    Set MySheet = Sheet1
End Function

and use, for example:

Sub foo()
    MsgBox MySheet.Name
End Sub

score 3 · Answer 3 · edited May 23 '17 at 10:28

3

See here for a very similar post... To reiterate what's said there, this behavior can be caused by the following:

Using "End" (not "End Sub", just the "End" statement on it's own).
Unhandled runtime error (so if any of your code throws an error, you loose your global variables)
Editing code (includes pressing the stop button)
Closing the workbook containing the VB project (something you've ruled out already in the comments section).

One solution would be to streamline public variable re-initialization with a subroutine. This subroutine could then be called at the beginning of others in a single line, or called from error handling as needed. The detriment to constantly re-initializing variables is that they loose reliability in holding mutable information.

edited May 23 '17 at 10:28

Community

1
1

answered Sep 08 '15 at 13:01

u8it

3,956
1
20
33

This should be a comment. What is your contribution? You simply passed on the link, copied info from there and summaried it! – Siddharth Rout Sep 08 '15 at 13:21
No it should not be a comment because it answers the question as to why this is happening. You don't want to bury an answer in comments. I can independently verify this answer on my own and chose to include a link to substantiate it. This is not simply a copy and paste, information has been added and evaluated per the OP's question at hand. I assumed the solution of creating a separate variable re-initialization subroutine would be apparent given this information. – u8it Sep 08 '15 at 13:36
Your recent edit makes more sense now. Have reversed the down vote :) – Siddharth Rout Sep 08 '15 at 13:52
Hmm, 1,3 and 4 are definitely not causing the issue. It is possible that number 2 is - I wasn't aware that global variables were lost on an error. I'll have a look and see if there is anything that could be triggering this. – ajor Sep 08 '15 at 14:42

Global variables reset automatically

3 Answers3