Excel VBA sheet activation required?

Question

I'm a 3-week neophyte learning VBA in Excel context (after my last programming course in Pascal 30 years ago.) I can't understand why sometimes executing some code which refers to a sheet requires that sheet to be activated. My specific question, via some toy code:

Public Sub Test()
Dim Rng As Range
Set Rng = Worksheets("Sheet2").Range(Cells(1, 1), Cells(5, 1)).Find(What:="Prize",LookIn:=xlValues)
If Rng Is Nothing Then
  MsgBox "Nothing there"
Else
  MsgBox "Found the prize"
End If
End Sub

If I call Test() while Sheet1 is active (say via a UserForm on Sheet1) I get an "Application defined or Object-defined Error". If Sheet2 is active, the code runs properly.

Why does Sheet2 have to be active to execute this code? I could workaround by activating Sheet2 within the sub and then activating Sheet 1 again, but that seems clumsy. Is there a better way?

Thanks for the help! I'm having other activation issues like this throughout my actual code.

Cells() without a worksheet qualifier always refers to the ActiveSheet. You need to use Worksheets("Sheet2").Cells(...) — Tim Williams

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2014-03-29T01:33:00

Since you did not specify the sheet for Cells, that object will refer to the Active Sheet. But your Range is explicitly defined as referring to Sheet2. Since there is a conflict, an error results.

Using that syntax, you could do something like:

With Worksheets("Sheet2")
    Set Rng = range(.Cells(1,1),.Cells(5,1)).Find(...
end with

Excel VBA sheet activation required?

1 Answers