The “Object variable or With block variable not set” error in VBA is one of the most common runtime errors developers encounter. It happens when your code tries to use an object variable that hasn’t been properly assigned to an object. This can occur with worksheets, ranges, workbooks, or any other object in VBA. Understanding why this error occurs and how to fix it is essential to writing stable and reliable VBA code.
In this article, we will demonstrate several methods to prevent and resolve this error, including proper object assignment, checking for Nothing, and safely using With blocks. Following these techniques will help your macros run smoothly without unexpected interruptions.
Steps to properly Declare and Set Object Variables
➤ Press Alt + F11 to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
➤ Paste the following code:
Sub CheckForNothing()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
' Attempt to find "Alice" in column B
Set rng = ws.Range("B:B").Find("Alice")
' Check if the employee was found
If Not rng Is Nothing Then
' Use the found cell safely
MsgBox rng.Value & "'s performance: " & ws.Cells(rng.Row, "E").Value & "%"
Else
MsgBox "Employee not found."
End If
End Sub➤ Return to Excel, press Alt + F8 , select ProperlySetObject, and click Run.
➤ The macro will calculate the performance percentage for all the employees and display a message box.
Properly Declare and Set Object Variables
When working with objects like worksheets or ranges in VBA, it’s easy to forget that they must first be properly assigned before you can use them. If you try to reference an object that hasn’t been initialized, VBA will throw the “Object variable or With block variable not set” error.
This method focuses on correctly declaring and setting object variables using the Set keyword before accessing their properties or methods. Doing so ensures your code runs smoothly and prevents unexpected runtime errors when handling workbook or worksheet objects.
This is the dataset we will be using for our method:
Using our dataset, suppose you want to reference Sheet1 to update employee sales or targets. Properly assigning the worksheet object first will prevent errors when accessing its cells or ranges.
Steps:
➤ Press Alt + F11 to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
➤ Paste the following code:
Sub ProperlySetObject()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim Sales As Variant
Dim Target As Variant
' Assign the worksheet object
Set ws = ThisWorkbook.Sheets("Sheet1")
' Find the last used row based on the Employee ID column
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loop through each row to calculate performance
For i = 2 To lastRow ' Assuming row 1 has headers
Sales = ws.Cells(i, "D").Value
Target = ws.Cells(i, "E").Value
' Check if values are numeric and Target is not zero
If IsNumeric(Sales) And IsNumeric(Target) And Target <> 0 Then
ws.Cells(i, "F").Value = (Sales / Target) * 100
Else
ws.Cells(i, "F").Value = "Error"
End If
Next i
MsgBox "Performance percentage calculated for all employees."
End Sub
➧ Set ws = ThisWorkbook.Sheets("Sheet1") assigns the actual worksheet to the object variable.
➧ Once set, you can safely access its ranges and cells without triggering the "Object variable not set" error.
➤ Return to Excel, press Alt + F8 , select ProperlySetObject, and click Run.
➤ The macro will calculate the performance percentage for the first employee and display a message box.
Check for Nothing Before Using an Object
Sometimes in VBA, you might assume an object exists when it actually doesn’t. For instance, if a Range.Find search doesn’t locate a value, it returns Nothing, and trying to use that result immediately will cause the “Object variable or With block variable not set” error.
To avoid this, you can add a simple check to confirm the object is valid before using it. This approach makes your code more stable and helps prevent those frustrating runtime interruptions.
By using the same dataset from the last method, suppose you want to find an employee named “Alice” in column B and calculate her performance percentage only if she exists in the worksheet.
Steps:
➤ Press Alt + F11 to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
➤ Paste the following code:
Sub CheckForNothing()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
' Attempt to find "Alice" in column B
Set rng = ws.Range("B:B").Find("Alice")
' Check if the employee was found
If Not rng Is Nothing Then
' Use the found cell safely
MsgBox rng.Value & "'s performance: " & ws.Cells(rng.Row, "E").Value & "%"
Else
MsgBox "Employee not found."
End If
End Sub
➧ If Not rng Is Nothing Then ensures the object exists before using it.
➧ Only if the object is valid does the macro access rng.Row to retrieve data safely.
➤ Return to Excel, press Alt + F8 , select CheckForNothing, and click Run.
➤ The macro will either show Alice’s performance or indicate that the employee was not found.
Frequently Asked Questions (FAQs)
What causes the “Object variable or With block variable not set” error?
This error occurs when you try to access properties or methods of an object variable that hasn’t been assigned a valid reference using Set. For example, trying to use a worksheet or range that hasn’t been initialized will trigger this error.
Can this error happen with Range.Find?
Yes. Methods like Range.Find can return Nothing if no match is found. Always check the result with If Not rng Is Nothing Then before using it.
Is using On Error Resume Next a good solution?
It can suppress errors temporarily, but it’s better to properly check that objects are valid. Using If Not obj Is Nothing ensures safe code and avoids hidden issues.
Do I always need to use Set with object variables?
Yes. Failing to use Set to assign an object will make your variable invalid, causing the “Object variable not set” error when accessed.
Can With…End With blocks work without checking if the object is set?
No. Entering a With block using an uninitialized object will immediately throw the error. Always confirm the object is valid first.
Wrapping Up
The “Object variable or With block variable not set” error is one of the most common runtime errors in VBA, but it is easy to prevent. Always assign objects using Set, check for Nothing when a method might fail, and validate objects before entering With…End With blocks. By following these practices, your code will run more reliably, avoid crashes, and handle unexpected cases gracefully. Proper object management not only prevents errors but also makes your VBA projects cleaner and easier to maintain.
