VBA Object Required

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 –

• Use of Randomize in VBA
• Create VBA Login User Form
• Month Function in Excel VBA
• CreateObject in Excel VBA
• On Error GoTo in VBA
• OverFlow Error in VBA
• Type Mismatch Error in VBA
• 1004 Error in VBA