VBA Object Variable or with Block Variable Not Set in Excel

VBA Object variable or With block variable not set is a common error message encountered in VBA programming language. This error often occurs when a reference is made to an object that has not been properly initialized or has been set to “Nothing.” In this article, we will explore the causes of this error and provide some tips and tricks to help you troubleshoot and fix it.

How to Launch VBA Editor in Excel

To open the VBA code editor in Excel you can utilize the keyboard shortcut. Let’s see the process.

Press Alt + F11 to open your Microsoft Visual Basic.

Then press Insert > Module to open a blank module.

VBA Object Variable or with Block Variable Not Set in Excel: 2 Possible Solutions

Errors are an inevitable part of any programming language. The prerequisite of becoming a skillful developer is the ability to identify an error and the ability to fix the error at a time. Like the many other VBA error such as Type Mismatch Error, Syntax Error, Overflow Error, etc, Object Variable or With Block Variable Not Set Error is one of the key errors which can make you stressed enough if you don’t know how to solve it. In this article, we will provide you with two practical examples of our today’s discussion.

1. Initialize Object Before Use

To understand the case better, consider we want to open and activate a workbook by using VBA code. As the attached video suggests, we have encountered Object Variable or with Block Variable Not Set error.

Sub Initialize_Object_Before_Use()
    Dim wb As Workbook
    wb = Workbooks.Open _
    ("C:\Users\User\Documents\Softeko\Update 95\Book1.xlsm")
    wb.Activate
End Sub

Reason:

The reason why we have encountered such an error is that we did not set our Workbook type object variable wb here. Thus we need to modify the code a bit to avoid it.

Solution:

As we have discussed that we need to modify our code a bit to set the object variable wb, we will use a keyword Set for that.

Sub Initialize_Object_Before_Use()
    Dim wb As Workbook
    Set wb = Workbooks.Open _
    ("C:\Users\User\Documents\Softeko\Update 95\Book1.xlsm")
    wb.Activate
End Sub

Code Breakdown

A variable wb is declared as a Workbook object.
The Set keyword assigns the wb variable to the result of the Workbooks.Open method.

The Workbooks.Open method opens the “Book1.xlsm” workbook file.
The Activate method is called on the wb workbook object to make it the active workbook.

2. With Block Variable Not Set

Now consider another easy example where you want to write ExcelDemy in a specific range in your worksheet.

Reason:

Here, We have encountered Object variable or With block variable not set error as we did not set ws variable before executing Set rng = ws.Range(“B4:C10”) line.

Sub Initialize_Object()
    Dim ws As Worksheet
    Dim rng As Range  
    Set rng = ws.Range("B4:C10")
    With rng
        .Value = "ExcelDemy"
        .Font.Bold = True
    End With
End Sub

Solution:

Like previously, you need to set ws variable to fix your code. Follow the image and code given below to have a detailed idea.

Sub Initialize_Object()
    Dim ws As Worksheet
    Dim rng As Range
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("B4:C10"
    With rng
        .Value = "ExcelDemy"
        .Font.Bold = True
    End With
End Sub

Code Breakdown:

We declared two variables: ws as a Worksheet object and rng as a Range object.
We used Set keyword to assign the ws variable to the worksheet named Sheet1 in the current workbook.
Then, we used Set keyword again to assign the rng variable to the range B4:C10 on the ws worksheet.
We used the With statement to apply multiple changes to the rng range object.
Next, we set the .Value property of the rng object to ExcelDemy, which populates all cells in the range with the same value.
The .Font.Bold property of the rng object is set to True, which makes the font bold for all cells in the range.

Thus you will have the output as given below.

Frequently Asked Questions

What is object variable with block variable not set 91?

The error message “Run-time error ’91’: Object variable or With block variable not set” indicates that the code is attempting to use an object variable or With a block that you have not initialized.

To fix this error, you need to make sure that you have properly initialized all object variables before using them. You should also ensure that any object references passed to a function or subroutine are valid and not set to Nothing.

What is error 424 in VBA?

“Error 424: Object Required” is a common run-time error in VBA that occurs when you try to reference an object that does not exist or you have properly initialized it. To fix this error, you need to ensure that you have properly initialized all object variables before using them.

Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.

Object variable or With block variable not set.xlsm

Conclusion

In this article, we have discussed how to fix VBA object variable or with block variable not set in Excel. To fix the issue, all you need to do is ensure that you have properly initialized all object variables before using them. Further, If you have any queries, feel free to comment below and we will get back to you soon.

Get FREE Advanced Excel Exercises with Solutions!