Object Required in Excel VBA
Mistakes are part and parcel of coding language but the real genius lies in finding the error and fixing those errors. The first step in fixing those errors is the intelligence of finding why that errors are occurring. If you can find why those errors are coming then it is a very easy job to fix those errors without breaking the sweat. One such error in VBA coding is “Object Required”.
If you remember while learning variables and assigning data types to those variables we have “Object” data types as well. When the object data type assigned and if that object doesn’t exist in the worksheet or workbook we are referring then we would get the VBA error message as “Object Required”. So, as a new coder, it is common to state to panic in those situations because at the starting level beginner cannot find the cause for this error.
Why Object Required Error Occurs? (and… How to Fix it?)
Ok, it takes two or three examples to really understand why this error occurs and how to fix it.
For an example look at the below code.
Code:
Sub Last_Row() Dim Wb As Workbook Dim Ws As Worksheet Dim MyToday As Date Set Wb = ThisWorkbook Set Ws = ThisWorkbook.Worksheets("Data") Set MyToday = Wb.Ws.Cells(1, 1) MsgBox MyToday End Sub
Let me explain to you the above code for you.
I have declared three variables and the first two variables refer to the “Workbook” and “Worksheet” objects. The third variable refers to the “Date” data type.
When the “Object” data types are assigned to the variable we need to use the word “Set” key to assign the reference of the object to the variable, so in the next two lines by using the “Set” keyword I have assigned the reference of “ThisWorkbook” to the variable “Wb” because this variable hold the object data type as “Workbook” and for the variable “Ws” I have assigned the worksheet object of “Data” worksheet in this workbook.
Set Wb = ThisWorkbook
Set Ws = ThisWorkbook.Worksheets("Data")
- In the next line for the “Date” data type variable also I have used the “Set” keyword to assign the value of the cell A1 value in this workbook (Wb) and in the worksheet “Data” (Ws).
Set MyToday = Wb.Ws.Cells(1, 1)
- In the next line, we are showing the value of the “MyDate” variable value of cell A1 value in the message box in VBA.
MsgBox MyToday
- Ok, let’s run this code and see what we get as a result.
As you can see above it shows the VBA error message as “Object Required”. Ok, its time to examine why we are getting this error message.
- In the above error message image in the code section while showing the error message it has highlighted the error part of the code with blue color.
- So, the question remains is why we got this error. The first thing we need to see is this particular variable data type. Go back to the previous line of code where we have assigned the data type to the variable “MyDate”.
- We have assigned the variable data type as “Date” and now come back to the error line now.
In this line, we have used the keyword “Set” whereas our data type isn’t the “Object” data type. So the moment VBA code sees the keyword “Set” it assumes it is an object data type and says it requires an object reference.
So, the bottom line is “Set” keyword is used to refer only to reference the object variables like Worksheet, Workbook, etc.…
Example #1
Now take a look at the below code.
Code:
Sub Object_Required_Error() Range("A101").Value = Application1.WorksheetFunction.Sum(Range("A1:A100")) End Sub
In the above code, we have used the worksheet function “SUM” to get the total of the cell values from A1 to A100. When you run this code we will encounter below error.
Oops!! It says “Run-time error ‘424’: Object Required.
Now let’s closely look at the code now.
Instead of using “Application” we have mistakenly used “Application1”, so this encountered the error of “Object Required” in VBA code.
If the word “Option Explicit” is enabled then we will get the “Variable Not Defined” error.
Things to Remember
- Object Required means object data type reference needs to be accurate.
- When the option explicit word is not enabled in the coding then we will get Object Required error for misspelled variable words and if Option Explicit is enabled we will get the variable not defined error for misspelled variable words.
Recommended Articles
This has been a guide to VBA Object Required. Here we learn why object required error in excel VBA and some examples and download the excel template. Below are some useful excel articles related to VBA –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
