1

Worksheet and Workbook objects can have their properties accessed in two ways.

  1. Explicitly ➔ Sheet1.cells(1,2)
  2. Implicitly ➔ Cells(1,2)

What I'm curious about is how does VBA know which object to use when a property is called implicitly? And are there more objects that are compatible with these implicit calls?

Here is a simple experimentation I threw together, it shows some cases where object focus changes and when it doesn't:

'Sheet1 has 2 row in column A
'Sheet2 has 5 rows in column A
Sub test()
    Dim obj As Object
    Set obj = Sheet1

    'focus is set when a sheet is called directly
    row1 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row

    'focus is set when a sheet is called through reference
    row2 = obj.Range("A" & Rows.Count).End(xlUp).Row

    'focus is not set by with
    With Sheet2
        row3 = Range("A" & Rows.Count).End(xlUp).Row
    End With

    'output = 5 : 2 : 2
    MsgBox row1 & " : " & row2 & " : " & row3
End Sub
Alter
  • 3,332
  • 4
  • 31
  • 56

1 Answers1

1

All Excel object references work like this:
Workbook -> Worksheet -> Range

If you leave out the previous node, Excel substitutes the "Active" object for the omitted ones. Examples:
Sheet1.Cells(1,1) is the same as ThisWorkbook.Sheets("Sheet1").Cells(1,1)
Cells(1,1) is the same as ActiveWorksheet.Cells(1,1)

Mr. Mascaro
  • 2,693
  • 1
  • 11
  • 18
  • `Sheet1.Cells(1,1)` is [not the same](http://stackoverflow.com/a/25203416/11683) as `ActiveWorkbook.Sheets("Sheet1").Cells(1,1)`. It is the same as `ThisWorkbook.Sheet1.Cells(1,1)`. As opposed to `Sheets("Sheet1").Cells(1,1)`, which *is* the same as `ActiveWorkbook.Sheets("Sheet1").Cells(1,1)`. – GSerg Oct 15 '14 at 17:56
  • I just did a test, it seems that "ActiveSheet" is always set to the worksheet that is being displayed, even if you just called another sheet in the vba code – Alter Oct 15 '14 at 18:03
  • @Alter, calling a sheet does not make it active. The sheet has to be activated/selected to be "Active" – Mr. Mascaro Oct 15 '14 at 18:19