[Fixed!] Runtime Error 438 in Excel VBA (5 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

Runtime error 438 in Excel VBA is a very common error that is caused by referencing an object or property that doesn’t exist or is not recognized by the Excel VBA. There are multiple reasons for runtime error 438. In this article, we will discuss some of the reasons for runtime error 438 in Excel VBA and their possible solutions.


What Is Run-Time Error 438: Object Doesn’t Support This Property or Method?

Runtime Error 438 Excel VBA

“Runtime error 438: Object doesn’t support this property or method” is an error that occurs in Excel VBA when attempting to call a property or method that is not supported by the object that is being used in the VBA code. Runtime error 438 typically indicates that the code is trying to use a method or a property that is not defined by the object in question. This error can occur when working with objects in Excel VBA, such as workbooks, worksheets, ranges as well as objects in other applications.


How to Open VBA Macro Editor in Excel

You need to follow the steps below to get into the VBA Macro Editor in your worksheet:

  • You will see the Developer tab on top of the worksheet. Click on the Developer tab and select Visual Basic. Alternatively, you can press Alt+F11.

Opening Visual Basic from the Developer Tab

  • A new window will appear. It is the Visual Basic Editor Window. To write a new code, go to Insert > Module.

Creating a New Module

  • In the module, write the code and click on the Run button to run the code. You can also press the F5 key to run the code.

Running VBA Module


Runtime Error 438 in Excel VBA: 5 Possible Reasons with Solutions

In the following subsections, we have demonstrated five different examples of runtime error 438 in Excel VBA. Here in the dataset, we have some information about various real-life incidents that occurred in different parts of the world. The dataset includes the date, location, type, and number of people who died in those incidents.


Reason 1: Incorrect Workbook Reference

The following code has an incorrect workbook reference. If we run this code, we will see runtime error 438.VBA Code with Incorrect Workbook Reference which results in runtime error 438 in excelLet’s understand the defective code first. Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub incorrect_workbook_reference()
    Dim Incident, Location As String
    Dim People As Integer
    Dim wb As Workbook
    Set wb = Workbooks("runtime error 438 excel vba").Select
    Incident = Range("D5")
    Location = Range("C5")
    People = Range("E5")  
    MsgBox "The " & Incident & " in " & Location & " killed " & People & " people"
End Sub
The code shows a runtime error 438 because the Set statement is not used correctly to assign a reference to the workbook object, which causes the object variable to be empty and unable to perform subsequent actions on it.

Runtime Error 438 in Excel VBA


Solution: Correct Workbook Reference

We are going to modify the previous code a little bit to eliminate runtime error 438.  VBA Code with Corrected Workbook ReferenceNow, enter the modified code in your VBA Editor and press the Run button or F5 key to run the code:

Sub correct_workbook_reference()
    Dim Incident, Location As String
    Dim People As Integer
    Dim wb As Workbook
    Set wb = Workbooks("runtime error 438 excel vba")
    Incident = Range("D5")
    Location = Range("C5")
    People = Range("E5")
    MsgBox "The " & Incident & " in " & Location & " killed " & People & " people"
End Sub

VBA Breakdown:

Sub correct_workbook_reference()
    Dim Incident, Location As String
    Dim People As Integer
    Dim wb As Workbook
    Set wb = Workbooks("runtime error 438 excel vba")

This VBA code defines three variables: Incident, Location, and People. It then sets a Workbook object variable wb to refer to a workbook named runtime error 438 excel vba, which is selected using the Workbooks method.

Incident = Range("D5")
Location = Range("C5")
People = Range("E5")

The code then assigns the values of cells D5, C5, and E5 to the variables Incident, Location, and People, respectively.

MsgBox "The " & Incident & " in " & Location & " killed " & People & " people"
End Sub

Finally, a MsgBox is displayed showing the concatenation of the three variables in a sentence.

In this modified code, the Set statement has been used to correctly assign the reference to the workbook object named runtime error 438 excel vba.

Output MsgBox with Corrected Workbook Reference

Read More: How to Fix Excel Runtime Error 13 Type Mismatch in VBA


Reason 2: Invalid Worksheet Reference

The following code has an invalid worksheet reference. So, if we run this code, we will see runtime error 438.

VBA Code with Invalid Worksheet Reference which results in runtime error 438 in Excel

Let’s understand the defective code first. Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub invalid_worksheet_reference()
    Dim Incident, Location As String
    Dim People As Integer
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("run_time_error")
    Incident = Range("D6")
    Location = Range("C6")
    People = Range("E6")
    If ws = "run_time_error" Then  
        MsgBox "The " & Incident & " in " & Location & " killed " & People & " people"
    End If
End Sub
This code shows runtime error 438 because ws is a worksheet variable that stores the worksheet and “run_time_error” is just a string. So, they are not comparable.

Runtime Error 438


Solution: Insert Valid Worksheet Reference

We are going to modify this code a little bit to eliminate runtime error 438.

VBA Code with Valid Worksheet ReferenceEnter the modified code in your VBA Editor and press the Run button or F5 key to run the code:

Sub valid_worksheet_reference()
    Dim Incident, Location As String
    Dim People As Integer
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("run_time_error")
    Incident = Range("D6")
    Location = Range("C6")
    People = Range("E6")
    If ws.Name = "run_time_error" Then
        MsgBox "The " & Incident & " in " & Location & " killed " & People & " people"
    End If
End Sub

VBA Breakdown:

Sub valid_worksheet_reference()
    Dim Incident, Location As String
    Dim People As Integer
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("run_time_error")

This VBA code defines three variables: Incident, Location, and People. It then sets a Worksheet object variable, ws that refer to a worksheet named run_time_error within the active workbook, using the ActiveWorkbook.Worksheets method.

Incident = Range("D6")
Location = Range("C6")
People = Range("E6")

The code then assigns the values of cells D6, C6, and E6 to the variables Incident, Location, and People, respectively.

If ws.Name = "run_time_error" Then   
        MsgBox "The " & Incident & " in " & Location & " killed " & People & " people"
 End If
End Sub

The code checks if the worksheet variable ws refers to a worksheet named “run_time_error” using an If statement. Then, it shows a MsgBox.

In this modified code to fix the error, ws.Name has been used to check the name of the worksheet object with a string, “run_time_error”. The Name property returns a string, so both sides of the If statement have the same data type. So, no error occurred this time.

Output MsgBox with Valid Worksheet Reference


Reason 3: Incorrect Range Object Reference

The following code has an incorrect range object reference. If we run this code, we will see runtime error 438.

VBA Code with Incorrect Range Reference which results in runtime error 438 in excel

Let’s understand the defective code first. Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub incorrect_range_reference() 
    Dim ws As Worksheet
    Dim wb As Workbook
    Set wb = Workbooks("runtime error 438 excel vba")
    Set ws = wb.Worksheets("run_time_error")
    Dim myRng As Range
    Set myRng = wb.Range("B4:E14")
    myRng.Select
End Sub
The code shows a runtime error 438 because the Range object belongs directly to the worksheet, ws, not to the workbook, wb.

Runtime Error 438


Solution: Insert Correct Range Object Reference

We are going to modify the previous code a little bit to eliminate runtime error 438.

VBA Code with Corrected Range Reference

Enter the modified code in your VBA Editor and press the Run button or F5 key to run the code:

Sub correct_range_reference() 
    Dim ws As Worksheet
    Dim wb As Workbook
    Set wb = Workbooks("runtime error 438 excel vba")
    Set ws = wb.Worksheets("run_time_error")
    Dim myRng As Range
    Set myRng = ws.Range("B4:E14")
    myRng.Select
End Sub

VBA Breakdown:

Sub correct_range_reference()
    Dim ws As Worksheet
    Dim wb As Workbook
    Set wb = Workbooks("runtime error 438 excel vba")
    Set ws = wb.Worksheets("run_time_error")

This VBA code sets a Workbook object variable wb to refer to a workbook named runtime error 438 excel vba, using the Workbooks method. It then sets a Worksheet object variable ws to refer to a worksheet named run_time_error within the workbook wb, using the Worksheets method.

Dim myRng As Range
Set myRng = ws.Range("B4:E14") 
myRng.Select
End Sub

The code defines a Range object variable myRng to refer to cells B4:E14 of the worksheet, ws, using the Range method.

To fix the error, the modified code uses the ws worksheet object instead of the wb workbook object to set the myRng range. It selects the myRng range using the Select method.

Selected Dataset with Corrected Range Reference


Reason 4: Wrong Property of an Object

The following code includes the wrong property of an object. If we run this code, we will see runtime error 438.

VBA Code with Wrong Property of an Object

Let’s understand the defective code first. Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub wrong_property()
    Dim summa As Integer
    summa = 0
    Dim values As Range
    Dim wb As Workbook
    Set wb = Workbooks("runtime error 438 excel vba")
    Dim ws As Worksheet
    Set ws = wb.Worksheets(1)  
    Set values = ws.Range("E5:E14").values  
    For i = 1 To values.Cells.Count
        summa = summa + values.Cells(i)
    Next i 
    MsgBox "The total number of people died : " & summa
End Sub
It shows a runtime error 438 because .values property can’t be used in a Set statement. There is no such property.

Runtime Error 438


Solution: Remove the Wrong Property of an Object

We are going to modify this code a little bit to eliminate runtime error 438.

VBA Code with Correct Property of an Object

Enter the modified code in your VBA Editor and press the Run button or F5 key to run the code:

Sub wrong_property_fixed()
    Dim summa As Integer
    summa = 0
    Dim values As Range
    Dim wb As Workbook
    Set wb = Workbooks("runtime error 438 excel vba")
    Dim ws As Worksheet
    Set ws = wb.Worksheets(1)
    Set values = ws.Range("E5:E14")    
    For i = 1 To values.Cells.Count
        summa = summa + values.Cells(i)
    Next i
    MsgBox "The total number of people died : " & summa
End Sub

VBA Breakdown:

Sub wrong_property_fixed()   
    Dim summa As Integer
    summa = 0
    Dim values As Range
    Dim wb As Workbook
    Set wb = Workbooks("runtime error 438 excel vba")
    Dim ws As Worksheet
    Set ws = wb.Worksheets(1)

This VBA code defines a variable summa as an integer and initializes it to zero. It then defines a Range object variable values and a Worksheet object variable ws. The code sets ws to refer to the first worksheet in the workbook runtime error 438 excel vba using the Worksheets method.

Set values = ws.Range("E5:E14")  
    For i = 1 To values.Cells.Count
        summa = summa + values.Cells(i)
    Next i  
    MsgBox "The total number of people died : " & summa
End Sub

These lines set values to refer to the values of cells E5:E14 in the worksheet, ws. The code used a For Loop to sum up the values in the values range and finally got the summation of the numbers in an output MsgBox.

To fix the error, we removed the .values property and set the range of values to the desired range.

Output MsgBox with Correct Property of an Object

Read More: [Fixed!] Excel VBA Run Time Error 1004


Reason 5: Missing Property of an Object

The following code has an object whose property is missing. So, if we run this code, we will see runtime error 438.

VBA Code with Missing Property of an Object

Let’s understand the defective code first. Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub missing_property()
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("run_time_error")
    If ws(1, 1) = "" Then
        MsgBox "No Error"
    End If  
End Sub
The code tries to access the cell value using the Worksheet object variable, ws like an array, which shows a runtime error 438. So, a property of the Worksheet object seems to be missing here.

Runtime Error 438


Solution: Add Property of an Object

We are going to modify the previous code a little bit to eliminate runtime error 438.

VBA Code with Property of an Object 

Now, enter the modified code in your VBA Editor and press the Run button or F5 key to run the code:

Sub missing_property_fixed()
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("run_time_error")
    If ws.Cells(1, 1) = "" Then
        MsgBox "No Error"
    End If
End Sub

VBA Breakdown:

Sub missing_property_fixed()
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("run_time_error")

This VBA code sets a Worksheet object variable, ws, to refer to a worksheet named run_time_error within the active workbook using the ActiveWorkbook.Worksheets method.

If ws.Cells(1, 1) = "" Then
       MsgBox "No Error"
 End If    
End Sub

The code checks if the value of the cell at the first row and first column of the ws worksheet is an empty string using an If statement. If the cell is empty, the code returns a MsgBox.

To fix the error, the code needs to use the Range or Cells method to refer to a specific cell or range of cells, like ws.Cells(1,1) or ws.Range(“A1”), instead of trying to access a cell without proper indexing.

Output MsgBox with Property of an Object


Things to Remember

There are a few things to keep in mind to avoid runtime error 438 in Excel VBA:

  • Check the spelling of the object or property
  • Check for any missing reference
  • Check if the object or property exists

Frequently Asked Questions

  • What causes runtime error 438 in Excel VBA?

Runtime error 438 can occur due to several factors. We have pointed out five factors that may cause runtime errors and their possible solutions in this article.

  • How do I debug runtime error 438 in Excel VBA?

To debug runtime error 438 in Excel VBA, you can use the built-in VBA Editor debugger to step through the code and identify the exact line where the error occurs. You can then make the necessary arrangements to fix the error.

  • Can I prevent runtime error 438 in Excel VBA?

To prevent runtime error 438 in Excel VBA, you can ensure that you have spelled object and property names correctly, used the correct object types, and referenced properties of an object that is available in the current context.


Download Practice Workbook

You can download this practice book while going through this article.


Conclusion

In this article, we have discussed in detail how and why runtime error 438 in Excel VBA occurs. There are several reasons for runtime error 438. We have discussed these reasons and possible solutions.

This article will allow users to use Excel more efficiently and effectively.  If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, Exceldemy.com, and unlock a great resource for Excel-related content.


Related Articles:

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abu Sina Ibne Albaruni
Md. Abu Sina Ibne Albaruni

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo