Simple variable assignment in Excel 2003 VBA

Question

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

Not 100% sure, but I think the object it is talking about is "Worksheets", and it is trying to tell you that it does not support the "Item" property/method. Is this based on some sample code you found? — FrustratedWithFormsDesigner
– FrustratedWithFormsDesigner, Commented Mar 14, 2010 at 2:42
Nope, code I'm writing from scratch. If I type in "Worksheets." I get the intellisense for "Item(" — Mike
– Mike, Commented Mar 14, 2010 at 2:48
great question. The reason you need Set is because sheet is an object and not a simple type (such as integer, string, etc.). You can also reference worksheets by name, such as Set sheet = Worksheets("Sheet1"). — Ben McCormack
– Ben McCormack, Commented Mar 14, 2010 at 22:06

Mitch Wheat · Accepted Answer · 2010-03-14 03:00:41Z

3

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

answered Mar 14, 2010 at 3:00

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1