“此工作表"相等的? [英] "ThisWorksheet" equivalent?
问题描述
在Excel中，我有一些宏是 Worksheet 模块的一部分.
In Excel, I have some macros that are part of a Worksheet module.
在代码中，我要确保所引用的范围是该工作表的一部分.
In the code, I want to make sure that the ranges referred to are part of that worksheet.
例如，在我的Main Sheet工作表模块中，说我有:
For example, in my Main Sheet worksheet module, say I have:
Sub test()
Dim rng as Range
Set rng = Range("A1")
End Sub
当然，我想明确地给工作表指定范围.通常，我会
Of course, I want to explicitly give the worksheet the range is on. Typically, I'd do
Set rng = Worksheets("Main Sheet").Range("A1")
但是我很好奇，因为我的代码位于工作表模块中，我该如何引用该工作表?我在想类似ThisWorksheet的方法，但这不是VBA中的一种方法(但是ThisWorkbook是，这就是为什么我想尝试一下的原因).
But I'm curious, since I have my code in a worksheet module, how can I refer to that worksheet? I was thinking something like ThisWorksheet but that's not a method in VBA (but ThisWorkbook is, which is why I thought to try it).
我唯一的其他想法是，由于我的宏位于工作表模块中，因此未分配"范围变量隐式地引用了Main Sheet，而不能引用了任何其他工作表的单元格.
My only other thoughts are that since my macro is inside a worksheet module, the "unassigned" range variable implicitly refers to the Main Sheet and can not refer to any other worksheet's cells.
我是否理解正确，还是有某种方式可以引用运行代码的工作表?
Do I understand that correctly, or is there some way to refer to the worksheet the code running is stored in?
推荐答案
工作表模块是文档模块，该类与其他模块一样，除了继承(是，继承，就像ThisWorkbook.Worksheets.Add本质上是工厂方法).
A worksheet module is a document module, which is class just like any other, except it inherits (yes, inherits, as in class inheritance!) members from the Worksheet interface, and being a document module the only way to create an instance of it is through the host application's object model (i.e. ThisWorkbook.Worksheets.Add is essentially a factory method).
作为类模块，该模块的工作表对象是实例的 ，例如Sheet1类，其中包含您放入其中的所有成员 plus 从Worksheet界面继承的每个成员... 包括一个Range属性.
Being a class module, the worksheet object for that module is an instance of, say, the Sheet1 class, which contains whatever members you put into it, plus every member inherited from the Worksheet interface... including a Range property.
所以在工作表模块中不合格的Range调用引用那个工作表的原因仅仅是因为VBA语言的范围规则-给定以下代码:
So the reason why an unqualified Range call in a worksheet module refers to that sheet, is simply because of the VBA language's scoping rules - given this code:
foo = Range("B12").Value2
-
如果该作用域中有一个名为
Range的局部变量，那么Range就是该变量.
If there's a local variable in that scope named
Range, then that's whatRangerefers to.
如果该模块中有一个名为Range的成员，那么Range就是该成员.
If there's a member in that module named Range, then that's what Range refers to.
如果当前项目中有一个名为Range的全局变量，那么Range就是该变量.
If there's a global variable in the current project named Range, then that's what Range refers to.
如果在引用的项目或类型库Range中存在全局范围的标识符，则Range所引用的内容.
If there's a globally-scoped identifier in a referenced project or type library named Range, then that's what Range refers to.
您可以通过使用Me关键字限定Range调用的歧义，在本例中是通过Sheet1界面返回Me关键字的引用(在当前情况下，该代码仍返回您对当前对象的引用) Sheet1):
You can disambiguate the Range call by qualifying it with the Me keyword, which returns a reference to the current object, in this case through the Sheet1 interface (still assuming you're in the code-behind of Sheet1):
foo = Me.Range("B12").Value2
如果您位于Sheet1的代码后面，则该代码将对Sheet1起作用，如果您位于Sheet2的代码后面，则该代码将对Sheet2起作用，并且...将失败可以在标准模块中进行编译.
That code will work against Sheet1 if you're in the code-behind of Sheet1, and against Sheet2 if you're in the code-behind of Sheet2, ...and will fail to compile in a standard module.
但是Me的性质和含义值得更多关注.
But the nature and implications of Me deserve more attention.
Me是保留的名称(您不能使用该名称命名变量)，它引用只能在运行时在过程范围内存在的内容:当前对象.在幕后，当您针对Class1对象对DoSomething进行成员调用时，调用基本上是这样的:
Me is a reserved name (you can't have a variable by that name) that refers to something that can only exist at run-time in a procedure's scope: the current object. Under the hood, when you make a member call to DoSomething against a Class1 object, the call goes essentially like this:
Set obj = New Class1
Class1.DoSomething obj
这意味着DoSomething在VBA中如下所示:
This means DoSomething looks like this in VBA:
Public Sub DoSomething()
End Sub
但是VBA这样看:
Public Sub DoSomething(ByVal Me As Class1)
End Sub
这使Me成为类型为Class1的隐式本地范围内的ByVal参数，并且在DoSomething范围内，它包含对调用方当前正在使用的任何对象的引用.
That makes Me an implicit locally-scoped ByVal parameter of type Class1, and inside the DoSomething scope it holds a reference to whatever object the caller is currently using.
这基本上是我了解的关键我"(没有花，没有蜜蜂) article =)
That's basically the crux of my Understanding 'Me' (no flowers, no bees) article =)
(相关语言规范)
在标准模块中时，不合格的Range调用遵循完全相同的作用域规则:
When you're in a standard module, an unqualified Range call obeys the exact same scoping rules:
-
如果该作用域中有一个名为
Range的局部变量，那么Range就是该变量.
If there's a local variable in that scope named
Range, then that's whatRangerefers to.
如果该模块中有一个名为Range的成员，那么Range就是该成员.
If there's a member in that module named Range, then that's what Range refers to.
如果当前项目中有一个名为Range的全局变量，那么Range就是该变量.
If there's a global variable in the current project named Range, then that's what Range refers to.
如果在引用的项目或类型库Range中存在全局范围的标识符，则Range所引用的内容.
If there's a globally-scoped identifier in a referenced project or type library named Range, then that's what Range refers to.
(假设在该模块/项目中没有Range标识符的 shadowing )
(assuming no shadowing of the Range identifier is occurring in that module/project)
在这种情况下，可以在隐藏的Global模块中找到全局范围的标识符:
The globally-scoped identifier in this case can be found in the hidden Global module:
这篇关于“此工作表"相等的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
