The Range property of a Worksheet object returns a Range object that represents a single cell or a range of cells in the worksheet with the following two syntaxes:
- Range(Cell1) ‘Cell1 is a range reference
- Without any operator:
- Range(“C2”); Range(“VatRate”), where VatRate is a range name.
- With one or more range operators (colons):
- Range(“B2:C5”); Range(“B:B”); Range(“2:5”); Range(“B2:C5:D1”)
- With one or more intersection operators (spaces):
- Range(“B2:C5 B5:D6”); Range(“B2:C5 B5:D6 C:D”)
- With one or more union operators (commas): Range(“B2, C3, D4:E5”)
- Without any operator:
- Range(Cell1, Cell2) ‘Cell1 and Cell2 are range references
- Without a range operator (a colon): Range(“B5”, “C2”)
- With range operators: Range(“C2:B5”, “C1:D2”)
Cell1 and Cell2 are string arguments that represent range references.
The returned Range object is with the following hierarchy:
Application object > Workbook object > Worksheet object > Range object
The Application object is the entire Microsoft Excel application. It encompasses all objects in Excel. When you refer to a range such as Range(“B2”) without explicitly stating its parent (the Worksheet object), Excel assumes that the Worksheet object is the active sheet. Similarly, without explicitly stating the parent of a Worksheet object, Excel assumes that its parent (the Workbook object) is the active workbook.
In other words, Excel assumes an unqualified range, such as Range(“B2”), is equivalent to one of the following lines of code.
ActiveSheet.Range("B2")
ActiveWorkbook.ActiveSheet.Range("B2")
Application.ActiveWorkbook.ActiveSheet.Range("B2")
If you are referring to a range in the active sheet of the active workbook, stating the range without the worksheet reference and the workbook reference is much preferable. It makes the code cleaner and easier to read.
However, you must always be extra careful to qualify correctly the worksheet and workbook references if the executions of your VBA code can possible activate other worksheet and other workbook, and yet you need to access the range again. For example, a newly added worksheet becomes the active sheet, and a newly added workbook becomes the active workbook.
An alternative to refer correctly to a range again in some other points in your VBA code is to assign the range reference at the beginning to an object variable using the Set statement. The following code fragment illustrates the idea.
Dim Rng As Range
Set Rng = Range("B2:C5")
'Other VBA code that can possibly change
'the active sheet and/or the active workbook
'...
'An example of accessing the range again
Rng.Cells(2).Activate
All the discussions on the Range property of a Worksheet object are also applicable to the Range property of a Range object, which also returns a Range object that represents a single cell or a range of cells with the same syntaxes.
The Range property of a Worksheet object
Range(Cell1, Cell2): Range(“B5”, “C2”); Range(“C2:B5”, “C1:D2”)
Dissecting Excel VBA 