vba - "ThisWorksheet" equivalent?

Question

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.

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")

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).

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?

Answer 1

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).

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.

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

1. If there's a local variable in that scope named Range, then that's what Range refers to.

2. If there's a member in that module named Range, then that's what Range refers to.

3. If there's a global variable in the current project named Range, then that's what Range refers to.

4. If there's a globally-scoped identifier in a referenced project or type library named Range, then that's what Range refers to.

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

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.

But the nature and implications of Me deserve more attention.

---

About 'Me'

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

This means DoSomething looks like this in VBA:

Public Sub DoSomething()
End Sub

But VBA sees it like this:

Public Sub DoSomething(ByVal Me As Class1)
End Sub

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.

That's basically the crux of my Understanding 'Me' (no flowers, no bees) article =)

_{(relevant language spec)}

---

When you're in a standard module, an unqualified Range call obeys the exact same scoping rules:

1. If there's a local variable in that scope named Range, then that's what Range refers to.

2. If there's a member in that module named Range, then that's what Range refers to.

3. If there's a global variable in the current project named Range, then that's what Range refers to.

4. If there's a globally-scoped identifier in a referenced project or type library named Range, then that's what Range refers to.

(assuming no shadowing of the Range identifier is occurring in that module/project)

The globally-scoped identifier in this case can be found in the hidden Global module: