VBA Object Required

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 to find why those 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 a 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. So, when the object data type is assigned, and if that object does not exist in the worksheet or workbook we refer to, we will get the VBA error message as “Object required.” So, as a new coder, it is common to panic in those situations because, at the starting level, a beginner cannot find the cause for this error.

It takes two or three examples to understand why this error occurs and how to fix it.

For 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.

We have declared three variables, and the first two refer to the “Workbook” and “Worksheet” objects. The third variable refers to the “Date” data type.

When the "Object" data types assign the variable, we need to use the word "Set" key to assign the object's reference to the variable. So, in the next two lines, by using the "Set" keyword, we have assigned the reference of "ThisWorkbook" to the variable "Wb" because this variable holds the object data type as "Workbook." For the variable "Ws," we have assigned the worksheet object of the "Data" worksheet in this workbook.

Set Wb = ThisWorkbook

Set Ws = ThisWorkbook.Worksheets("Data")

• In the next line for the "Date" data type variable also, we have used the "Set" keyword to assign the value of the cell A1 value in this workbook (Wb) and the worksheet "Data" (Ws).

Set MyToday = Wb.Ws.Cells(1, 1)

• In the next line, we show the value of the “MyDate” variable value of cell A1 in the message box in VBA.

MsgBox MyToday

• Let us run this code and see what we get.

As you can see above, it shows the VBA error message as "Object required." Therefore, it is 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 why we got this error. The first thing we need to see is this particular variable data type. Go back to the previous code line, assigning the data type to the variable "MyDate."

• We have assigned the variable data type as "Date" and now return to the error line.

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 the "Set" keyword one may use 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 the below error.

It says, "Run-time error '424': Object required."

Now, let us closely look at the code now.

Instead of using "Application," we mistakenly used "Application1", so this encountered the error of "Object required" in the VBA code.

If the word “Option Explicit” is enabled, we will get the “Variable not defined” error.

• The "Object required" means object data type reference needs to be accurate.
• When the Option Explicit word is unenabled in the coding, we will get an "Object Required" error for misspelled variable words. If Option Explicit is enabled, we will get the variable not defined error for misspelled variable words.