When VBA is not able to recognize the object for which you are referring to the property or a method it shows you the Object Required error. In simple words, if you refer to an object, but the name of that object is not correct (that object is not in the VBA’s object hierarchy) it shows error 424, like the following.
In the above code, as you can see, I have misspelled the active cell object, and when VBA’s executes that line of code can’t that object because there’s no object with that name (as I have misspelled it).
Note: If you have used the Option Explicit statement in the module then with the same, you’ll get a different error (see image below).
Used “Set” Keyword for a Non-Object Variable
When you use a variable to assign an object to it, you need to use the keyword “Set”. In the following example, you have a myWKS for the worksheet and iVal for the value from cell A1.
As you can see, in the above code you have variables out of which one is declared as a worksheet object and the second as a string. But at the time of assigning the value, we have used the “Set” keyword to the variable “iVal” which is not declared as an object but as a string.
How to Fix Object Required (Error 424) in VBA
- Go to the Debug menu in your visual basic editor.
- Use the step to run the entire code step by step.
- The moment you reach the line where you have an error VBA will show you an error.
- Correct that line of code.
The other way could be going through the code line by line by reading it to make sure you are referring to the right objects and using the correct name of the variables and objects.
You can also use the GOTO statement to surpass an error or show a message to the users once an error occurred.
⇠ Back to What is VBA in Excel – Error Handling in VBA
Helpful Links – Developer Tab – Visual Basic Editor – Run a Macro – Personal Macro Workbook – Excel Macro Recorder – VBA Interview Questions – VBA Codes