Referencing a Worksheet with Object Variable

October 30, 2001 - by Bill Jelen

Joe is trying to puzzle out an error on a new macro that he just wrote.

The line that assigns NextRow is returning an error that says "Object Variable or With Block Variable Not Set".

Object variables are pretty cool. In the old tips at MrExcel, I would use regular variable like these:

OrigName = ActiveSheet.Name
Worksheets(OrigName).Copy

There is a better way. You can define a variable to be any object, such as a worksheet. There are several advantages to this. It is shorter to use the object variable in place of Worksheets(OrigName). Also, as you are typing code, VBA will know that the variable is a worksheet and offer the appropriate properties and methods after you type the dot. Here is the new code:

Dim WSO as Worksheet
Set WSO = Activesheet
WSO.Copy

The trick, though, is that when you have an object variable, it has to be assigned with the Set keyword. If you fail to put the Set in the code, you will get the somewhat non-intuitive "Object Variable or With Block Variable Not Set". Joe simply needs to precede his variable assignment with the word Set.