vba 运行时错误 91 对象变量或未设置块变量 - 公共变量的使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24183189/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
RunTime Error 91 Object variable or With block variable not set - Use of Public Variables
提问by bluehills
I am currently working on a project that consolidates information from a number of workbooks. While the first version of the application used Selectand Activateextensively, from the information I have seen on this site, the use of these methods should be avoided where possible. As such, I am currently re-vamping the code to eliminate these where possible.
我目前正在开展一个项目，该项目整合了许多工作簿中的信息。尽管所使用的应用程序的第一个版本Select，并Activate广泛，从信息我已经看到在这个网站中，使用这些方法，应尽量避免使用。因此，我目前正在重新修改代码以尽可能消除这些。
The workbook I am using has a reference sheet which is VeryHidden. In order to reference this sheet, I have made a number of public declarations at the start of the first module; one of which is
我正在使用的工作簿有一个非常隐藏的参考表。为了参考这张表，我在第一个模块开始时做了一些公开声明；其中之一是
Public refsheet as Worksheet
Please note that this Module may not be used after the workbook has been opened.
请注意，在打开工作簿后可能无法使用此模块。
QUESTION 1: Does this cause an issue if I have my public variables defined in this module when not used? If it does cause an issue, where should these public variables be defined?
问题 1：如果我在不使用时在此模块中定义了公共变量，这是否会导致问题？如果确实引起了问题，应该在哪里定义这些公共变量？
In the Workbook_Open()event, I have the following:
在这种情况Workbook_Open()下，我有以下几点：
Private Sub Workbook_Open()
Set refsheet = Sheets("References")
Set corang = Sheets("Consolidation").Range("L2:AI2")
Application.ScreenUpdating = False
refsheet.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub
QUESTION 2: If I set refsheet in the Workbook_Open() sub, will it be available for use in all other modules given that it has been defined as a public variable?
问题 2：如果我在 Workbook_Open() 子中设置 refsheet，鉴于它已被定义为公共变量，它是否可用于所有其他模块？
When I run the code for the first time after using, the first use of refsheet is fine and then anytime refsheet is used after that, I am receiving a Runtime Error 91; Object Variable or With block variable not set.
当我在使用后第一次运行代码时，第一次使用 refsheet 很好，然后任何时候使用 refsheet，我都会收到运行时错误 91；未设置对象变量或带块变量。
回答by Jean-Fran?ois Corbett
My suggestion is: drop that public variable - Workbook_Open shenanigan entirely. Why do you need this refsheetvariable at all? You probably don't. Public variables are not alwaysa bad idea; they just usuallyare a bad idea.
我的建议是：完全删除该公共变量 - Workbook_Open 恶作剧。为什么你需要这个refsheet变量？你可能没有。公共变量并不总是一个坏主意；他们只是通常是一个坏主意。
Just refer to the sheet by its Name, or by its CodeName. Example:
只需按其Name或按其引用工作表CodeName。例子：
Worksheets("References").Range("A1").Value = "Hello world!" ' Name is "References"
shtReferences.Range("A2").Value = "Hello world!" ' CodeName is shtReferences
The second one assumes that you changed the sheet's CodeName to shtReferencesin the Properties window, top line (Name), as in the example below. This is my favourite way of referencing sheets, because it guards against cases where the user changes the Nameof the sheet in the sheet tab.
第二个假设您shtReferences在属性窗口中将工作表的 CodeName 更改为top line (Name)，如下例所示。这是我最喜欢的引用工作表的方式，因为它可以防止用户Name在工作表选项卡中更改工作表的情况。
Beware: (Name)represents CodeName, and this is different from Namewithout parentheses! A bit confusing, I know.
当心：(Name)代表CodeName，这与Name没有括号不同！有点混乱，我知道。
Note that the CodeNameof sheets are, by default, Sheet1, Sheet2, ... Adding to the confusion is the fact that the Nameof sheets are also Sheet1, Sheet2, etc. by default. But Nameand CodeName/(Name)are not linked and can be changed independently.
请注意，CodeName床单是，默认情况下Sheet1，Sheet2...添加到混乱的事实，Name张也Sheet1，Sheet2等在默认情况下。但是Name和CodeName/(Name)没有链接，可以独立更改。
MsgBox shtReferences.Name ' returns "References"
MsgBox shtReferences.CodeName ' returns "shtReferences"
回答by Gary's Student
I think something else is going on. I put this in the workbook code area:
我认为还有其他事情正在发生。我把它放在工作簿代码区：
Private Sub Workbook_Open()
Set MySheet = Sheets("xxx")
MySheet.Visible = xlSheetVeryHidden
End Sub
and I put this in a standard module:
我把它放在一个标准模块中：
Public MySheet As Worksheet
Sub MAIN()
MsgBox MySheet.Name
End Sub
After I re-open the file, I get the expected result by running MAIN.
重新打开文件后，我通过运行MAIN获得了预期的结果。
Try this in a new workbook to see if you replicate what I am seeing.
在新的工作簿中试试这个，看看你是否复制了我所看到的。
