RunTime错误91对象变量或未设置块变量 - 使用公共变量 [英] RunTime Error 91 Object variable or With block variable not set - Use of Public Variables

问题描述

我正在研究一个整合多个工作簿信息的项目。虽然应用程序的第一个版本使用从我在本网站上看到的信息广泛地选择和激活应尽可能避免使用这些方法。因此，我目前正在重新修改代码，以尽可能地消除这些代码。

我使用的工作簿具有非常隐藏的参考表。为了参考这张表，我在第一个模块的开头做了一些公开的声明;其中一个是

 公开的refsheet作为工作表
  

请注意，在打开工作簿后，此模块可能不会被使用。

问题1 ：如果在不使用本模块中定义了我的公共变量，是否会导致问题？如果确实造成问题，应该在哪里定义这些公共变量？

在 Workbook_Open（）事件中，我有以下内容：

  Private Sub Workbook_Open（）
 
设置refsheet = Sheets（References ）
设置corang = Sheets（Consolidation）。范围（L2：AI2）
 
 Application.ScreenUpdating = False 
 refsheet.Visible = xlSheetVeryHidden 
应用程序.ScreenUpdating = True 
 
 
 End Sub 
  

问题2 ：如果我在Workbook_Open（）子文件中设置refsheet，它是否可以在所有其他模块中使用，因为它被定义为一个公共变量？

当我使用第一次运行代码后，首先使用refsheet是好的，然后任何时候都使用refsheet，我收到一个运行时错误91 ;对象变量或块变量未设置。

解决方案

我的建议是：删除该公共变量 - Workbook_Open shenanigan。为什么你需要这个 refsheet 变量？你可能不会公共变量永远不是一个坏主意;他们只是通常是一个坏主意。

只需通过名称，或由其 CodeName 。示例：

  Worksheets（References）。Range（A1）。Value =Hello world！ 'name is'引用
 shtReferences.Range（A2）。Value =Hello world！ 'CodeName是shtReferences 
  

第二个假设你将工作表的CodeName更改为在属性窗口的顶部（Name）中的shtReferences ，如下例所示。这是我最喜欢的参考表格的方式，因为它防止用户在表单选项卡中更改工作表的名称的情况。

小心：（名称）代表 CodeName ，这不同于名称，不带括号！有点混乱，我知道。

请注意，默认情况下， CodeName Sheet1 ， Sheet2 ，...添加到混乱是事实，名称的工作表也是默认情况下，Sheet1 ， Sheet2 等。但是名称和 CodeName / （Name）未链接并可独立更改。

  MsgBox shtReferences.Name'返回引用
 MsgBox shtReferences.CodeName返回shtReferences
  

I am currently working on a project that consolidates information from a number of workbooks. While the first version of the application used Select and Activate extensively, 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.

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?

In the Workbook_Open() event, I have the following:

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?

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.

解决方案

My suggestion is: drop that public variable - Workbook_Open shenanigan entirely. Why do you need this refsheet variable at all? You probably don't. Public variables are not always a bad idea; they just usually are a bad idea.

Just refer to the sheet by its Name, or by its CodeName. Example:

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 shtReferences in 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 Name of the sheet in the sheet tab.

Beware: (Name) represents CodeName, and this is different from Name without parentheses! A bit confusing, I know.

Note that the CodeName of sheets are, by default, Sheet1, Sheet2, ... Adding to the confusion is the fact that the Name of sheets are also Sheet1, Sheet2, etc. by default. But Name and CodeName/(Name) are not linked and can be changed independently.

MsgBox shtReferences.Name ' returns "References"
MsgBox shtReferences.CodeName ' returns "shtReferences"

这篇关于RunTime错误91对象变量或未设置块变量 - 使用公共变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！