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?
Here the VBA is formatted as a user defined function. What is this? With this code we can use =WorksheetExists(B3) to test any text string to see if it exists as a sheet name in the current workbook.
Renaming sheets in Excel one can do from the taskbar below the worksheets by double-clicking on them. But in VBA, we use the Sheets or Worksheet property method to rename the sheet. The syntax to rename a sheet is as follows: Sheets(“Old Sheet Name”). Name = “New Sheet name.”
On Error GoTo 0 disables error handling in the current procedure. It doesn't specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0. Without an On Error GoTo 0 statement, an error handler is automatically disabled when a procedure is exited.
To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses.
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
If there's a local variable in that scope named Range
, then that's what Range
refers to.
If there's a member in that module named Range
, then that's what Range
refers to.
If there's a global variable in the current project named Range
, then that's what Range
refers to.
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.
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:
If there's a local variable in that scope named Range
, then that's what Range
refers to.
If there's a member in that module named Range
, then that's what Range
refers to.
If there's a global variable in the current project named Range
, then that's what Range
refers to.
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With