An Apple a Day Keeps “Object Required” Error Message Away
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. If you use the wrong property or method for the object, you’ll get a run time error.
The following statements use proper VBA syntax for Apple as a fictitious VBA object qualifier.
Apple.Peel Apple.Color=”Red” Apple.Name=”Empire”
A period separates the object qualifier from its property or method.
Peel is a method.
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?
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.
Variable Not Defined
This statement causes the “Object required” error message. Can you find the problem?
Range("A101").Value = Applicatoin.WorksheetFunction.Sum(Range("A1:A100")
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
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:
Sub ReplaceBlanks() 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 End If Next rngCell End Sub
Tip: Learn to identify Excel VBA objects. Object qualifiers that end with “s” reference Collection objects. Commonly-used objects include Workbook and Workbooks, Worksheet and Worksheets, Sheets, Cell and Cells, Range, Selection, Rows, Columns, and Application. For a complete list of VBA objects, visit: https://docs.microsoft.com/en-us/office/vba/api/overview/Excel/object-model
Don’t Set Variables
Set with variables is just as important as using
Set with objects. If you use
Set with variables, VBA generates an error message. Study the example below:
Fully Qualified Objects
The “Object required” error message can be very frustrating when the statement appears to be correct, like this one:
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
The error message occurs when the worksheet referenced by ws is not the active sheet. VBA executes the statement this way:
LastRow = ws.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
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:
StartRow = Application.Workbooks(1).Worksheets(1).Cells(n, 1).Row
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:
ActiveCell = Application.VLookup(ActiveCell.Offset(0, -1), Range("TeamNameLookup"), 2, False).Value
ActiveCell.Value = Application.VLookup(ActiveCell.Offset(0, -1), Range("TeamNameLookup"), 2, False)
Reason: VLookup function is not an object. It lacks the Value property.
Range("K7:K14") = Application.WorksheetFunction.VLookup(ActiveSheet.Name, Lookup.Range("A$1$:B$200$"), 2, 0)
Range("K7:K14") = Application.WorksheetFunction.VLookup(ActiveSheet.Name, Lookup.Range("$A$1:$B$200"), 2, 0)
Reason: Dollar signs ($) are in the wrong place.
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.
See also: Break Mode in VBA