全局变量会自动重置

Question

我想定义一个全局变量作为打开文件的工作表，所以我使用了以下代码:

在Module1:

Public mySheet As Worksheet

在ThisWorkbook:

Sub Workbook_Open()  
    Set mySheet = Sheet1   
End Sub

然后我想使用mySheet在各种过程中引用这个特定的工作表，其中一些过程引用了打开新文件后的这个工作表。

它最初工作-当我打开文件时，设置了变量，并且涉及mySheet.Unprotect, mySheet.Protect和mySheet.Range("A1")的宏工作。然而，当我尝试再次运行它时，我得到一个错误Object variable or With block variable not set，并且调试将我带到mySheet.Unprotect行，这是第一次引用表。

如何在全局变量中定义此工作表以使定义保持不变?

作为参考，我所指的特定宏如下，尽管我在不同的代码位上遇到了类似的问题:

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

Answer 1

我个人建议您创建一个返回该表的函数。然后，基本上可以将函数名用作工作表变量。(虽然如果你总是想要相同的工作表，你可以使用Sheet1的代号)

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

和使用，例如:

Sub foo()
    MsgBox MySheet.Name
End Sub

Answer 2

正如其他答案中的链接所提到的，您可以理解为什么会发生这种情况。

下面是我的建议来解决这个问题。

在模块中创建一个过程。命名为Init

Public mySheet As Worksheet
Sub Init()
   Set mySheet = Sheet1
End Sub

然后在你的Workbook_Open()中这样做。

Sub Workbook_Open()
    Init
End Sub

现在无论你在哪里使用这个对象只要再添加一行

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

Answer 3

在这里看到一个非常相似的帖子…重申一下这里所说的，这种行为可能由以下因素引起:

1. 使用"End"(不是"End Sub"，只是"End"语句)
2. 未处理的运行时错误(所以如果你的任何代码抛出错误，你失去你的全局变量)
3. 编辑代码(包括按停止键)
4. 关闭包含VB项目的工作簿(您已经在评论部分中排除了某些内容)。

一个解决方案是使用子例程简化公共变量的重新初始化。然后可以在单行中其他子例程的开头调用此子例程，或者根据需要从错误处理中调用。不断地重新初始化变量的危害是，它们在保存可变信息方面失去了可靠性。