Use Set to create an Object Variable instead of a regular variable when referencing cells


Past Tip of the Day


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

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

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.

As I continue reviewing the tip of the week archives, you will see object variables being used more frequently.

By Bill Jelen on 30-Oct-2001 Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.