VBA Object Required

What Is Object Required In VBA?

VBA Object Required means that the Object data type reference needs to be accurate. When Option Explicit is not enabled in the code, we get an “Object Required” error for misspelled variable words and if it is enabled, we will get a variable not defined error.

Similarly, one of the errors in VBA is an Object Required error. When you write VBA codes in Excel to automate tasks, you might face the following error.

Excel is a collection of objects.

Excel App >>> Workbooks >>> Worksheets >>> Ranges >>> Cells.

We can assign these objects as data types in VBA to variables while coding. When we use the object variable, we must use the SET keyword to assign the respective object to the variable; if no object is assigned to the variable and if you still try to use the object using the variable, we will end up getting the object required error in VBA.

Why Object Required Error Occurs?

Before we fix VBA object required error, we need to know why this error occurs and when.

Let us show you some of the example codes to understand the error better.

Example #1 – When We Use The SET Keyword For Non-Object Variable

When we define a variable to hold object data type, we need to use the SET keyword to assign the object to that variable.

For example, look at the following code.

Sub Example1_Object_Required_Error()

Dim Ws As Worksheet
Set Ws = Worksheets("Example 1")

End Sub

Code Explanation

Step 1: Dim Ws As Worksheet

We have defined the variable ‘Ws’ and assigned the data type ‘Worksheet.’

Step 2: Set Ws = Worksheets(“Example 1”)

Then we used the SET keyword and assigned the worksheet named ‘Example 1’ to the variable ‘Ws.’

Step 3: Now, whenever we need to use the worksheet ‘Example 1,’ we can simply use the variable name ‘Ws’ and access the properties and methods of the worksheet.

Look at the following code now.

Sub Example1_Object_Required_Error()

Dim Ws As Integer
Set Ws = Worksheets("Example 1")

End Sub

Step 4: Let’s try to run this code and see what happens.

It says, Compile Error: Object Required.

When we declare the variable ‘Ws,’ we have assigned the data type ‘Integer’ which is not an object variable.

In the next line, we have used the SET keyword, which is used only for the object variables to assign the appropriate object to the variable. Because we have used the SET keyword for the non-object data type variable, we get the object required error.

Example #2 – Try Creating Object Without Using The Word SET

When we try to create an object using the variable but if we forget to use the SET word, we end up getting the VBA object required error.

For example, look at the following code.

Sub Example2_Object_Required_Error()

Dim Ws
Ws = CreateObject("Excel.Application")
Ws.Visible = True

End Sub

Firstly, we have defined the variable “Ws” but have not assigned any of the variables to it. When we do not assign any of the variables, it becomes variant, and any data type can be assigned.

Step 1: For the defined variable ‘Ws’ we are creating an Excel application object without using the word ‘SET.’

Step 2: Next, using the variable ‘Ws,’ we are making the Excel application visible.

When we run this code, we will get the following run time error.

It says, Run-time error ‘424’:”, Object required.

This is because, when we try to use the object or create the object, we have not used the word ‘SET.’ Hence, we ended up getting the VBA object required error.

Example #3 – When The Object Name Is Misspelled

When we try to reference the object, it has to be perfect and should be the same as in the VBA object’s library.

For example, VBA RANGE is an object. When we type the object name, it should be RANGE, and not any shortcuts.

Step 1: For instance, look at the following example code.

Sub Example3_Object_Required_Error()

ActiveCel.Copy

End Sub

The code is a one-liner and easy to understand. Seems like nothing is wrong with this code.

Step 2: Let’s run this code, and we will get the following error message.

The reason for this though is that we have misspelled the ACTIVE CELL object (missed out on spelling L at last). When we try to execute this code, VBA cannot find the object named ‘ACTIVECEL;’ hence we got the Run-time error 424 Object Required.

Different Error With Option Explicit

If we have enabled Option Explicit in our Visual Basic Editor Window, we may get a different error than the object required error. When the ‘Option Explicit’ is enabled, then we will get the following error.

The code is the same, but the error is different because of the word ‘Option Explicit’ at the top.

The word ‘Option Explicit’ makes the user declare the variables mandatorily. Since we have misspelled the object ‘ACTIVECELL,’ VBA treats this as a variable. Since we have added the ‘Option Explicit,’ we must first define the variable using the DIM statement. We cannot assign a value or object to the variable before defining the variable.

How To Handle Object Required Error?

Now, we have seen why object required error occurs. Now, let’s see how to handle such errors. We will use the same example codes for better understanding.

Example #1 – Assign A Correct Data Type

Sub Example1_Object_Required_Error()

    Dim Ws As Integer

    Set Ws = Worksheets(“Example 1”)

End Sub

We have assigned the data type ‘Integer’ to the variable ‘Ws’ i.e., only integer values can be assigned to this variable.

However, we are forced to assign a different VBA variable data type. Hence, we get VBA object required error.

We need to change the data type from Integer to the required object data type. Since we are assigning the worksheet object to this variable, we need to assign the data type ‘Worksheet’ instead of the ‘Integer’ data type.

Sub Example1_Object_Required_Error()

    Dim Ws As Worksheet

    Set Ws = Worksheets(“Example 1”)

End Sub

Example #2 – Always Use The SET Keyword

Even after assigning the object type, we still get the object required error if we do not use the keyword SET. If we look at the Example #2 code, we have not used the SET keyword.

Sub Example2_Object_Required_Error()

    Dim Ws

    Ws = CreateObject(“Excel.Application”)

            Ws.Visible = True

End Sub

Without using the word ‘SET,’ we are trying to create an Excel application object. To fix this issue, we need to use the keyword SET before the variable name.

Sub Example2_Object_Required_Error()

    Dim Ws

    Set Ws = CreateObject(“Excel.Application”)

            Ws.Visible = True

End Sub

This will solve VBA object required error problem.

Example #3 – Type the Object Name As It Is In The VBA Object Library

When we type the object names in VBA, they should be exactly the same as in the VBA object library. Even a single character missing or misplacing of characters will lead to VBA object required error.

Sub Example3_Object_Required_Error()

    ActiveCel.Copy

End Sub

Instead of typing the cell object ACTIVECELL, we missed the character L at last. Hence, we are getting the VBA object required error.

To fix this issue, we need to type the object name correctly.

Sub Example3_Object_Required_Error()

    ActiveCell.Copy

End Sub

In the above code, we have added the character ‘L’ at last, which is the exact object name available in VBA.

Skip The Error And Show Error At The End

It is not wise to allow the user to see the error when executing the code. Instead of the user-facing the error, let them know that they got the error in the process and show them at the end of the macro in excel.

For example, look at the following code.

Sub Example2_Object_Required_Error()

    Dim Ws

    Ws = CreateObject(“Excel.Application”)

            Ws.Visible = True

End Sub

As we have seen in Example #3, this code returns VBA Object Required error because we have not used the SET keyword, as shown in the following image.

Instead of the user experiencing this error, we can use the On Error GoTo method to show the error at the end. The following code will showcase the error message in the message box.

Sub Example2_Object_Required_Error()

On Error GoTo Message
Dim Ws
Ws = CreateObject(“Excel.Application”)
Ws.Visible = True

If Err.Description <> “” Then
Message: MsgBox “We have encountered an error of ” & Err.Description
Else
MsgBox “No Error Occurred, Everything Ran Smoothly”
End If

End Sub

We have used the On Error GoTo code to encounter the error and show what error has occurred during the execution in a message box.

We will get the following message box saying We have encountered an error of Object Required whenever the error occurs.

For example, when the code runs smoothly without any error, then, there is no point in showing the error message to the users.

For example, let us add the SET keyword to the code to make the code run correctly.

Sub Example2_Object_Required_Error()

On Error GoTo Message

    Dim Ws

   Set Ws = CreateObject(“Excel.Application”)

            Ws.Visible = True

If Err.Description <> “” Then

Message: MsgBox “We have encountered an error of ” & Err.Description

Else

    MsgBox “No Error Occurred, Everything Ran Smoothly”

End If

End Sub

We have added the SET keyword at the end. This makes the code run without any errors. When the code runs without any error, we will get the following message box saying, No Error Occurred, Everything Ran Smoothly.

Important Things To Note

• VBA Object Required Error is also described as ‘Run-Time Error 424.’
• If the Option Explicit word is added, then, we will not get VBA object required error. But, if the object name is not spelled properly in the code, we will get the variable not defined error.
• Option Explicit word makes the variable declaration mandatory and cannot use variables without declaring them by using the DIM keyword.
• Err.Description will give us the error name. Similarly, if we use the Err.Num, we will get the error number.

Frequently Asked Questions

Download Template

This article must be helpful to understand the VBA Object Required, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Object Required. Here we explain how to Handle Object Required Error in Excel VBA with examples and downloadable excel template. You may learn more from the following articles –

• VBA Cells
• VBA Global Variables
• VBA AutoFilter