How to Fix If VBA 'Object Variable' or the 'With Block Variable' Not Set in Excel (2 Solutions)

How to Launch VBA Editor in Excel

Steps:

Press Alt + F11 to open your Microsoft Visual Basic.

Press Insert > Module to open a blank module.

Solution 1 – Initialize Object Before Use

To understand the case better, consider that we want to open and activate a workbook using VBA code. As the attached video suggests, we have encountered an Object Variable or 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:

We encountered this error because we did not set our Workbook type object variable wb here. We need to modify the code.

Solution:

We will use a keyword Set to solve the problem.

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.

Solution 2 – With Block Variable Not Set

Reason:

We have encountered the Object variable or the 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:

You need to set a ws variable to fix your code. Follow the image and code given below.

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 the Set keyword to assign the ws variable to the worksheet named Sheet1 in the current workbook.
Then, we used the 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 .The value property of the rng object to ExcelDemy 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.