How to Fix ‘VBA Object Variable or With Block Variable Not Set’ (Excel)

Written by Kasper Langmann

When working with Visual Basic for Applications (VBA) in Excel, you may encounter the error message ‘Object Variable or With Block Variable Not Set’. This error typically occurs when an object that has not been initialized is referenced in your code. It can be frustrating, especially when you’re not sure what’s causing it or how to fix it. But fear not, this guide will provide you with a comprehensive understanding of this error and how to resolve it.

Understanding the Error

The ‘Object Variable or With Block Variable Not Set’ error in VBA is a runtime error. This means it occurs as your program is running, not when it’s being compiled. It’s also known as error 91. It’s one of the most common errors in VBA, and it’s usually caused by a variable that has been referenced before it’s been set.

For instance, if you’re trying to use an object variable that hasn’t been set to an instance of an object, you’ll get this error. Similarly, if you’re trying to use a With block variable that hasn’t been set, you’ll also get this error. The key to understanding this error is knowing that it’s all about initialization. If your variable isn’t initialized properly, you’re going to run into problems.

How to Fix the Error

Now that you understand what causes the ‘Object Variable or With Block Variable Not Set’ error, let’s look at how to fix it. There are a few different methods you can use, and we’ll go through each one in detail.

Method 1: Initialize Your Object Variable

The first and most straightforward method is to make sure your object variable is initialized before you try to use it. This means you need to set your object variable to an instance of an object before you try to reference it in your code. Here’s an example:


Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

In this example, the variable ws is an object variable that represents a worksheet. The Set keyword is used to assign an instance of a worksheet to the ws variable. If you try to use the ws variable before the Set statement, you’ll get the ‘Object Variable or With Block Variable Not Set’ error.

Method 2: Check for Nothing

Another method to fix this error is to check if your object variable is Nothing before you try to use it. This is useful in cases where your object variable might not always be set to an instance of an object. Here’s an example:


Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
If ws Is Nothing Then
    MsgBox "Worksheet not found"
Else
    ' Your code here
End If

In this example, the Is Nothing check is used to determine if the ws variable has been set to an instance of a worksheet. If it hasn’t, a message box is displayed and the rest of the code is skipped.

Method 3: Use Error Handling

The final method to fix this error is to use error handling. This involves using the On Error statement to specify what should happen if an error occurs. Here’s an example:


On Error Resume Next
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
If Err.Number = 91 Then
    MsgBox "Worksheet not found"
End If
On Error GoTo 0

In this example, the On Error Resume Next statement is used to ignore any errors that occur. The Err.Number property is then checked to see if the error number is 91, which is the number for the ‘Object Variable or With Block Variable Not Set’ error. If it is, a message box is displayed.

Preventing the Error

While knowing how to fix the ‘Object Variable or With Block Variable Not Set’ error is important, it’s even better if you can prevent it from happening in the first place. Here are some tips to help you avoid this error:

  • Always initialize your object variables before you use them.
  • Use the Is Nothing check to make sure your object variable has been set to an instance of an object.
  • Use error handling to catch and handle any errors that occur.

By following these tips, you can prevent the ‘Object Variable or With Block Variable Not Set’ error from occurring in your VBA code.

Conclusion

The ‘Object Variable or With Block Variable Not Set’ error is a common issue in VBA programming, but with the right understanding and approach, it can be easily resolved. Remember to initialize your variables, check for Nothing, and use error handling to manage any errors that may occur. With these strategies, you’ll be able to write more robust and error-free VBA code in Excel.