WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
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
View Course

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 –

  • VBA Login User Form
  • CreateObject in VBA
  • OverFlow Error in VBA
  • 1004 Error in VBA
  • VBA COUNTA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Object Required Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More