CODEWITHSUNDEEP
vbaexcel
Alter
Alter

Reputation: 3464

How does object focus work in VBA?

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

Upvotes: 1

Views: 646

Answers (1)

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

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)

Upvotes: 1

Related Questions