How to resolve object variable or with block variable not set error in Excel

Microsoft Excel has a built-in programming language to facilitate the pro-users to develop code using Visual Basic for Applications also known as VBA. These functions can be used to automate tasks or implement user defined functions as formulas. A very common error that can occur while developing VBA codes is the run-time error 91 as shown below;

We can face this error while referencing the variable inside the With block or anywhere in the code when we try to make a reference to the variable without allocating an actual object to the variable.

So let’s see how this error is created first and then we’ll resolve this error in both cases by following these steps.

Object Variable not set

Let’s consider this code and see the error when we run it.

Let’s resolve this error by following these steps.

Step 1 – Change the code to set the reference of variable to an actual Sheet Object

• We are having this problem because we have created a variable named my_worksheet but we haven’t allocated an actual object to it and we tried to change the name of the sheet to “Sales Data”.
• To resolve this problem, we can simply add a line of code to create a reference for the variable my_worksheet as follows

Set my_worksheet = ActiveSheet

• This will allocate the ActiveSheet object to the variable my_worksheet and resolve the problem.

Step 2 – Write code and run again

• After adding this line, run the code again and the sheet name will be renamed to “Sales Data”.

With block variable not set

Now let’s see the scenario when we try to use a variable inside a With block without allocating an object to it.

Step 1 – Modify the code to allocate an Object to the variable

• The reason for this problem is that we have created a variable named my_worksheet but we haven’t allocated an actual object to it. Then we tried to use the variable inside the with block to change the name of the sheet to “Sales Data”.
• To resolve this problem, we can simply add a line of code to create a reference for the variable my_worksheet as follows

Set my_worksheet = ActiveSheet

• The above mentioned line of code will be added before starting the With block so that the variable my_worksheet may have an actual object assigned to it before its use. This will resolve the run-time error that we faced.

Step 2 – Run the modified code

• After adding the above mentioned line, run the code again and the sheet name will be renamed to “Sales Data”.

So this is how we can remove the Run-Time Error 91 or object variable or with block variable not set error from our VBA code.