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. 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.Add
Worksheet.Name=”Summary

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.

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.

Set Objects

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:

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

Not using 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:

Invalid: 

ActiveCell = Application.VLookup(ActiveCell.Offset(0, -1), Range("TeamNameLookup"), 2, False).Value

Valid:

ActiveCell.Value = Application.VLookup(ActiveCell.Offset(0, -1), Range("TeamNameLookup"), 2, False)

Reason: VLookup function is not an object. It lacks the Value property.

Invalid: 

Range("K7:K14") = Application.WorksheetFunction.VLookup(ActiveSheet.Name, Lookup.Range("A$1$:B$200$"), 2, 0)

Valid: 

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