Excel VBA tip: Why do I get the error "Object variable or With block variable not set"?

A commonly encountered error when developing Excel VBA is the fairly cryptic "Run-time error '91': Object variable or With block variable not set".

Today I'm going to attempt to explain what it means and some common causes.  Click read more...

The most basic way to think about this error is: you have a variable in your VBA code that has become invalid.

So why the cryptic error message?  Well that's because VBA is giving programmers who know what they are talking about more information.  This isn't always helpful when you are new to VBA, but let's try to give you the knowledge you need in this particular case and hopefully it will become clear...

First, we'll strip the message down into its parts.  There are three.

1. "Run-time error '91':" - you can ignore this bit.  This tells you there has been an error (which is why the macro has stopped running!) and gives you an error number.  You almost certainly don't care about the error number.
2. "Object variable or With block variable" - this is telling you what type of variable the error refers to.  Either an "Object" variable or a "With block" variable, whatever those are!
3. "not set" - this is telling you that the variable is not valid.  "Not set" generally means that the variable is set to 'Nothing', which is a bad thing when you want to use the variable to do something!

The key to understanding this error is clearly to understand what part 2 means.

"With block" variables
A With block variable is created when you use With...End With.  The With block variable is set to the bit after the With.  For example in the following code:

With Worksheets("Sheet1").Range("A10")

the With block variable gets set to the Range A10 in Sheet1.

So if your macro has stopped within a With...End With block it could be that the top line of your With block is your problem.

One way to debug this is to create a variable so that you can inspect its value:
Dim theRange As Range
Set theRange = Worksheets("Sheet1").Range("A10")
With theRange

Put a breakpoint on the With line and run the macro.  Look at the locals window to see the value of theRange.  If the value is Nothing you have found your culprit.

Now you just need to figure out why, which is something you will need to do yourself!

"Object" variables
An Object variable is a variable that is not one of VBA's built-in data types.

• Built-in data types are things like 'Long', 'Boolean' and 'Date'.
• 'Worksheet' and 'Range' are examples of Object variables.

There are two steps you must follow when creating an object variable.  The first is to define the variable and the second is to Set it (using the Set keyword).  If you try to use an Object variable that has not been Set to anything you will get the error message we are talking about.

One of the most common ways to fall foul of our error message is to forget to use the Set word.  For example:
Dim theRange As Range
theRange = Worksheets("Sheet1").Range("A10")

What?!  This code looks perfectly valid doesn't it?!  Wrong!

Note that on the second line theRange is not preceded by the word Set.  This means that it is now being used (even though you were probably intending to Set it), but it hasn't been Set yet.  Hopefully you're starting to link this to the error message...

The solution is to insert the word Set as follows:
Dim theRange As Range
Set theRange = Worksheets("Sheet1").Range("A10")

Another way to fall foul of the error is to use a variable that has been set to Nothing.  For example:
Set theRange = Nothing
theRange.Value = 5

The above is a trivial example and hopefully you would be able to instantly see the problem and fix it!  In most cases it will be much more complex than this.  The best way to track the error down is to put a breakpoint in the macro on the first line that uses the variable in question and then step through the code to find out which line sets it to Nothing.  Hopefully this points you in the right direction.

There is a good article about this same topic on MSDN here: http://msdn.microsoft.com/en-us/library/aa264506(v=vs.60).aspx