Md. Abu Sina Ibne Albaruni
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: 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.
Download Practice Workbook
You can download this practice book while going through this article.
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.
- A new window will appear. It is the Visual Basic Editor Window. To write a new code, go to Insert > 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.
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.
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_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
Solution: Correct Workbook Reference
We are going to modify the previous code a little bit to eliminate runtime error 438. 
Now, 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 SubVBA 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 SubFinally, 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.
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.
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
Solution: Insert Valid Worksheet Reference
We are going to modify this code a little bit to eliminate runtime error 438.
Enter 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 SubVBA 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 SubThe 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.
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.
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
Solution: Insert Correct Range Object Reference
We are going to modify the previous code a little bit to eliminate runtime error 438.
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 SubVBA 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 SubThe 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.
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.
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
Solution: Remove the Wrong Property of an Object
We are going to modify this code a little bit to eliminate runtime error 438.
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 SubVBA 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 SubThese 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.
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.
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
Solution: Add Property of an Object
We are going to modify the previous code a little bit to eliminate runtime error 438.
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 SubVBA 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 SubThe 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.
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.
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.
