MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

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.

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.