WallStreetMojo

WallStreetMojo

Home » Excel, VBA & Power BI » Learn VBA » VBA Object Required

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 a sweat. One such error in VBA coding is “Object Required.”

Object Required Error

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 is assigned, and if that object doesn’t exist in the worksheet or workbook we are referring to going, 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, a beginner cannot find the cause for this error.

VBA Object Required

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.

You can download this VBA Object Required Excel Template here – VBA Object Required Excel Template

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.

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

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.

VBA Object Required Example 1

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.

VBA Object Required Example 1-1

  • 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.”

VBA Object Required Example 1-2

  • We have assigned the variable data type as “Date” and now come back to the error line now.

VBA Object Required Example 1-3

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 the below error.

Example 2

Oops!! It says, “Run-time error ‘424’: Object Required.

Now let’s closely look at the code now.

Example 2-1

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.

Example 2-2

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 the object required error in excel VBA and some examples and download the excel template. Below are some useful excel articles related to VBA –

0 Shares
Share
Tweet
Share
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>