Simple variable assignment in Excel 2003 VBA

Question

Hi,

I am new to VBA in Excel. I'm setting up a simple macro

Option Explicit

Sub Macro1()
   Dim sheet
   sheet = Worksheets.Item(1)  ' This line has the error
End Sub

On the line with the error, I get "Run-time error '438' Object doesn't support this property or method"

I can use the Watch window to see that "Worksheets.Item(1)" is a valid object. I've tried changing it to "Dim sheet As Worksheet" but same result.

Ok, so what am I missing? Why does this error occur?

Thanks!

-Mike

Answer 1

You need a Set statement (as you are assigning a reference):

Option Explicit

Sub Macro1()
   Dim sheet As Worksheet
   Set sheet = Worksheets.Item(1) 

   '' ... Use sheet

   Set sheet = Nothing
End Sub