What is an apple? This potentially philosophical question is used in this article to introduce what causes the run-time error message “Object required (Error 424)."
Consider the fruit Johnny Appleseed planted for homesteaders. The red, green, or yellow orb is an object. English speakers call this object an Apple. Apple is not the object itself, but a reference to the object. Apple is the object qualifier, to use the VBA term.
Apple is not the name of the object, either. Different Apples have different names like Cortland, Granny Smith, and Yellow Delicious. Name is one of the properties of the Apple object.
VBA is an object-oriented programming language, or OOP (that’s awfully close to oops, isn’t it?). VBA is organized around objects. Microsoft Excel objects include Workbook, Worksheet, Range, Cell, Selection, and Application.
A VBA object has properties and methods. The property or method of an object immediately follows the last period.The following statements use proper VBA syntax for Apple as a fictitious VBA object qualifier.
A period separates the object qualifier from its property or method. Peel is a method. Color and Name are properties.
Tip: The equal sign (=) is the sure way to distinguish between a property and a method. Only properties use an equal sign to assign a value to a property.
Following are two valid VBA statements.Can you identify the object qualifiers, the property, and the method?
Charts and Worksheet are object qualifiers. Add is a method. Name is the property. Summary, by the way, is the value.
With thanks to a popular fruit, the rest of this article tackles causes of the error message “Object required (Error 424)" and how to fix them. “Object required" means that the required object qualifier is missing, or the object qualifier, property, or method is invalid. Sounds simple, doesn’t it? In practice, it’s not always simple, but it helps that “Object required" is a run-time error, so the statement in error is usually highlighted yellow in Break Mode. Let’s start with a simple fix.
It’s a simple typo. The Application object qualifier is misspelled. Now, when the Option Explicit declaration is added to the top of the Code Window, the error message changes to “Variable not defined," and only Application is highlighted.
Best Practice: Start every Code Window with Option Explicit to remove one cause of “Object required" error message.
Speaking of variables, a variable is declared using the Dim statement. A new instance of an object is also created using the Dim statement. Compare the two Dim statements below:
Dim strName as String
Dim myCeOutput as Range
The first Dim statement declares the variable strName with String data type. The second Dim statement creates a new instance of the Range object with the object qualifier rngCell. The procedure below to replace blanks with zeroes in a selected region causes the “Object required" error message:
VBA does not recognize rngCell as an object qualifier, because it was not Set to an object. After the object qualifier is created using Dim, the object qualifier must be assigned to an Excel object using the Set keyword. This revised procedure executes without error, assuming the user selects a region before running the macro:
Dim rngSelection As Range
Dim rngCell As Range
Set rngSelection = Selection
Set rngCell = ActiveCell
For Each rngCell In rngSelection
If IsEmpty(rngCell) Then
rngCell = 0
VBA cannot use the row count of the active sheet as the row count of ws. You can prevent this type of error by fully qualifying the Rows object qualifier with the object qualifier of the worksheet, as shown:
Dim LastRow as Integer
Dim ws as Worksheet
Set ws=Sheets(“Raw Data")
Valid:LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Without a corresponding object qualifier, VBA uses the active workbook, the active sheet, and the active cell. A fully-qualified statement removes these assumptions; for example:
Best Practice: True, it’s overkill to use the object qualifier of the workbook when its procedures only operate within the workbook. Still, prevent runtime errors and unintended results by making explicit reference to the sheet and the workbook when procedures reference more than one sheet or one workbook.
Valid Object, Invalid Property or Method
Even when the error lies with the property or method of a valid object qualifier, the error message is still “Object required." Worksheet functions are common culprits. Part of the reason for this is that VBA does not have Autocomplete for worksheet functions as it does for properties and methods. Study these examples:
Finally, the “Object required" error message occurs in rare cases when the statement with the object qualifier has the correct syntax, but the action cannot be performed. According to Microsoft, this error can occur when you try to assign a value to a read-only property, for example.
“An apple a day keeps the doctor away," so the saying goes. It is hoped this article keeps the “Object required" error message away.