Worksheets do not work

Question

I am trying to write an excel visual macro base.

My problem is that this code works:

Dim x As String
x = Worksheets("Abgabe").Cells(20, 3).Value

But this is not so:

Dim y As Worksheet
y = Worksheets("Abgabe")

Also, if I use ActiveWorkbook, the code does not work.

Dim y As Worksheet
y = ActiveWorkbook.Worksheets("Abgabe")

I get this error:

An object variable or with an undefined block variable

What could be the problem?

Answer 1

  

Dim y as worksheet

         

y = Worksheets ("Abgabe")

  

Use this (you must use Set)

Dim y As Worksheet
Set y = Worksheets("Abgabe")

From MSDN ( http://msdn.microsoft.com/en-us/library/aa192490.aspx ):

Set Keyword: In VBA, the Set keyword is necessary to distinguish between 
assignment of an object and assignment of the default property of the object.