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.
Read More: [Fixed!] Subscript Out of Range Error in Excel VBA
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.
Read More: Reasons And Solutions for Excel Object Required Error in VBA
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.
Read More: [Fixed!] Excel Application-Defined or Object-Defined Error in VBA
Download Practice Workbook
You can download and practice the dataset that we have used to prepare this article.
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.
Related Articles:
- How to Fix Compile Error in Hidden Module in Excel
- [Fixed!] Invalid Forward Reference in VBA
- [Fixed!] Unable to Set the Visible Property of the Worksheet Class
- Because of Your Security Settings Macros Have Been Disabled
- [Fixed!] Method Open of Object Workbooks Failed
- [Solved]: User Defined Type Not Defined in Excel VBA
- Sub or Function Not Defined in Excel VBA
- [Solved!] Excel VBA “Argument Not Optional” Error