Excel VBA中全局可以声明工作表对象吗? [英] Can a worksheet object be declared globally in Excel VBA?
问题描述
我正在重构Excel 2003工作簿中的一些模块,同一组工作表在每个模块的每个过程中声明;我想在全球宣布一次。我可以将工作表名称设置为文字,例如:
公共常量xlwkGSModel =gs_modelAs String
然后在程序中使用:
code> ... ActiveWorkbook.Worksheets(xlwkGSModel).Cells(1,1)
但是有没有办法声明工作表对象,以便程序中的代码可以是:
... xlwkGSModel.Cells (1,1)
'1。插入一个模块
'2。在模块中声明工作表公共变量如下
公共xlwkGSModel作为工作表
pre>
'3。在应用程序加载事件中实例化此公共变量
Sub Workbook_Open()
设置xlwkGSModel = ActiveWorkbook。工作表(gs_model)
End Sub
'现在您可以参考具有xlwkGSModel变量的gs_model工作表
'例如
dim x作为字符串
x = xlwkGSModel.Cells(1,1)
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.:
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)
解决方案'1. Insert a module
'2. Declare worksheet public variable in the module as follows
Public xlwkGSModel As Worksheet
'3. Instantiate this public variable in the application load event
Sub Workbook_Open() Set xlwkGSModel = ActiveWorkbook.Worksheets("gs_model") End Sub
'Now you can refer the gs_model worksheet with the xlwkGSModel variable
'For example
dim x as string x = xlwkGSModel.Cells(1,1)
这篇关于Excel VBA中全局可以声明工作表对象吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!