可以在 Excel VBA 中全局声明工作表对象吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1176743/
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
Can a worksheet object be declared globally in Excel VBA?
提问by Alistair Knock
I'm refactoring a number of modules in an Excel 2003 workbook and the same set of worksheets are declared in each procedure in each module; I'd like to just declare them once globally. I can set the worksheet name as a literal, e.g.:
我正在重构 Excel 2003 工作簿中的多个模块，并且在每个模块的每个过程中都声明了相同的工作表集；我只想在全球范围内声明一次。我可以将工作表名称设置为文字，例如：
Public Const xlwkGSModel = "gs_model" As String
And then in the procedure use:
然后在程序中使用：
...ActiveWorkbook.Worksheets(xlwkGSModel).Cells(1,1)
But is there a way to declare the worksheet object so that the code in the procedure could be:
但是有没有办法声明工作表对象，以便程序中的代码可以是：
...xlwkGSModel.Cells(1,1)
回答by
'1. Insert a module
'1。插入模块
'2. Declare worksheet public variable in the module as follows
'2。在模块中声明工作表公共变量如下
Public xlwkGSModel As Worksheet
'3. Instantiate this public variable in the application load event
'3。在应用程序加载事件中实例化这个公共变量
Sub Workbook_Open()
Set xlwkGSModel = ActiveWorkbook.Worksheets("gs_model")
End Sub
'Now you can refer the gs_model worksheet with the xlwkGSModel variable
'现在您可以使用 xlwkGSModel 变量引用 gs_model 工作表
'For example
'例如
dim x as string
x = xlwkGSModel.Cells(1,1)
回答by Wysmaster
It's a long time ago, but better late than never ;-) I don't know if that works in Excel 2003 (tested it with 2007):
这是很久以前的事了，但迟到总比不到好 ;-) 我不知道这是否适用于 Excel 2003（用 2007 测试）：
You don't even need to declare worksheets in the code, because they are already declared. I'm working with a German version of Excel and I can access a worksheet by typing "Tabelle1.Cells(...) ...". I assume in the English version it is something like "Table1" or "Sheet1".
您甚至不需要在代码中声明工作表，因为它们已经被声明了。我正在使用德语版本的 Excel，我可以通过键入“Tabelle1.Cells(...) ...”来访问工作表。我假设在英文版本中它类似于“Table1”或“Sheet1”。
You can also change these names. In the Visual Basic Editor have a look at the Microsoft Excel Objects of your VBA-Project. (They are right above your Modules and UserForms in the VBA-Project view). There are the Worksheet-Objects of your workbook. Select a sheet and activate the Properties Toolwindow. There you can edit the name of the sheet and access it by that name in your code.
您也可以更改这些名称。在 Visual Basic 编辑器中查看 VBA 项目的 Microsoft Excel 对象。（它们位于 VBA 项目视图中模块和用户窗体的正上方）。有工作簿的工作表对象。选择一个工作表并激活属性工具窗口。在那里您可以编辑工作表的名称并在代码中通过该名称访问它。
回答by Gary McGill
You could, but do you really want more global variables? Why not create (within a standard module) a public property ModelWorksheet, like this:
你可以，但你真的想要更多的全局变量吗？为什么不（在标准模块中）创建一个公共属性 ModelWorksheet，如下所示：
Public Property Get ModelWorksheet As Worksheet
Const ModelWorksheetName As String = "gs_model"
Set ModelWorksheet = ActiveWorkbook.Worksheets(ModelWorksheetName)
End Property
...then your code can do:
...那么您的代码可以执行以下操作：
With ModelWorksheet
.Cells(1,1).Value = "foo"
.Font.Bold = True
End With
Note also that you can refer to any of the worksheets in the current workbookdirectly, so if there's a sheet called Sheet1 you can do:
另请注意，您可以直接引用当前工作簿中的任何工作表，因此如果有一个名为 Sheet1的工作表，您可以执行以下操作：
With Sheet1
.Cells(1,1).Value = "foo"
.Font.Bold = True
End With
回答by shijie
Or if you do not want to use the Excel Event, you can also declare your worksheet publicly, then create a sub to set value for it. Call that sub at the beginning of each sub to initialize the value. (I did this from orwell's answer.)
或者，如果您不想使用 Excel 事件，您也可以公开声明您的工作表，然后创建一个子项来为其设置值。在每个 sub 的开头调用该 sub 以初始化该值。（我从奥威尔的回答中做到了这一点。）
Public WSRawData As Worksheet
Public Sub RawDataWSInit()
Set WSRawData = Worksheets(RawData)
End Sub
Sub Main()
Call RawDataWSInit
End Sub
回答by felvhage
Your Worksheets are "Microsoft Excel Objects" defined in your VBA-Project-Explorer:
您的工作表是在您的 VBA-Project-Explorer 中定义的“Microsoft Excel 对象”：
In The Screen above i have named my Worksheet DataSheet, so now i can access it directly from within the code:
在上面的屏幕中，我已经命名了我的工作表数据表，所以现在我可以直接从代码中访问它：
Set SomeRange = DataSheet.Range("A3:B6")
By Default your Worksheets will be named "Sheet1", "Sheet2", aso... depending on your language.
默认情况下，您的工作表将被命名为“Sheet1”、“Sheet2”等...取决于您的语言。
回答by Treb
Edit:The comment by Alistair Knock is correct, I should have read the question thoroughly - of course my answer is not valid for objects, only for types like string or integer. For objects you need a function or sub that creates an instance.
编辑：Alistair Knock 的评论是正确的，我应该彻底阅读这个问题 - 当然我的答案对对象无效，仅对字符串或整数等类型有效。对于对象，您需要一个创建实例的函数或子。
Yes, you can, I recently did it. If you define your definitions as Publicyou can use them directly in your other modules (within the same workbook, of course).
是的，你可以，我最近做到了。如果您定义您的定义，因为Public您可以直接在其他模块中使用它们（当然，在同一个工作簿中）。
Maybe the best approach is to have a seperate module Globalsand put them there.
也许最好的方法是有一个单独的模块Globals并将它们放在那里。
