To debug the error “Object variable or with block variable not set”.
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.
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.
Since we have missed the “set” statement while referring to sheet1 and due to this, below shown error has occurred.
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”.
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
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
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.
Sub ObjectError2() Dim ws As Worksheet 'Did not initiate the object variable ws With ws .Range("A1").Value = "Sample Value" End With End Sub
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
Post you may like