Debug error | “Object variable or with block variable not set | VBA Excel

object variable not set vba excel

Objective

To debug the error “Object variable or with block variable not set”.

Approach

To understand this error, we have created 2 cases where we have replicated this error. This error appears when we have not set the reference of object variable by using “set” keyword. I have only discussed only 2 cases here. You can refer to link in reference section for all the possible case.

Case 1

In the first case we can see that we had declared the object variable “ws” which has worksheet as its data type. We have not used “set” statement while setting up reference to sheet1.

Cause

Since we have missed the “set” statement while referring to sheet1 and due to this, below shown error has occurred.

Solution

It is mandatory to use “Set” statement when we are referring to an object. “Set” statement assigns the worksheet (i.e. sheet1) reference to variable “ws”. In simple terms we can use “ws” instead of Thisworkbook.sheets(“sheet1”). To resolve this error, we need to add “set” statement while referring to an object. Here Dim statement only declares that “ws” refers to a worksheet object, but “set” statement assigns the reference of “sheet1” to variable “ws”.

Incorrect code

Below code, throws the error as we had not mentioned SET statement here.

Sub ObjectErrorOne ()

Dim ws As Worksheet

'Missed the SET statement here

 ws = ThisWorkbook.Sheets("sheet1")

ws.Range("A1").Value = "Sample Value"

End Sub

 

Correct Code

We have added the SET statement here to resolve error .

Sub ObjectErrorTwo()

Dim ws As Worksheet

'Added the SET statement here

Set ws = ThisWorkbook.Sheets("sheet1")

ws.Range("A1").Value = "Sample Value"

End Sub

 

Case 2

In this case we only declared the variable “ws”, but we did not set (or initialize) it. So, it is important to remember that we need to initialize the with block variable before using it.

Incorrect Code

Sub ObjectError2()

Dim ws As Worksheet

 'Did not initiate the object variable ws

With ws

 .Range("A1").Value = "Sample Value"

End With

End Sub

 

Correct Code

Sub ObjectError2()

Dim ws As Worksheet

'Initiated the object variable ws

Set ws = ThisWorkbook.Sheets("sheet1")

With ws

 .Range("A1").Value = "Sample Value"

End With

End Sub

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/object-variable-not-set-error-91

 

Post you may like

Select and apply formula in named range using VBA Excel