Fix VBA Error 438 – Object Doesn’t Support Property or Method

This article will demonstrate how to Fix VBA Error 438 – Object Doesn’t Support Property or Method.

Error 438 is a frequently occuring error in Excel VBA and is caused by either omitting a property or method when referring to an object, or by using a property or method that is not available to an object in the VBA code.

Check the VBA code to Fix Error 438

Let us examine the following code:

Sub RemoveWorksheet()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim sheetName As String
  sheetName = "Sheet 1"
  Set wb = ActiveWorkbook
  For Each ws In wb.Sheets
     If ws = sheetName Then
        wb.Sheets(sheetName).Delete
        Exit For
     End If
  Next ws
End Sub

If we run this code, Error 438 will occur.

To resolve this issue, we click on Debug to see where the error is occurring.

This line of code is trying to equate the worksheet (depicted by the variable ws) to the sheet name. This is not possible as the worksheet is an object but the sheet name is a string so Error 438 is therefore returned.

To solve this, compare the string sheetName to the name property of the worksheet object:

ws.name = sheetName

Now the code runs without issue!

To show a list of all the properties or methods that are available to the worksheet object, we can type a dot (.) after the object.

This invokes the VBA Editor’s Intellisense feature. To make sure it’s turned on, in the Menu, select Tools > Options.

Make sure Auto List Members is checked and then click OK.

NOTE: This is usually on by default.

You can also find a list of all the Properties, Methods and Events for an Excel Object in the Object Browser.

In the Menu, select View > Object Browser or press F2 (See more VBE shortcuts).

A list of classes and objects will appear in the left hand side of the screen. If you click on the object you wish to use (eg: Workbook), a list of all the Properties, Methods and Events that that object supports will appear in the right hand side of the screen. (eg: Members of Workbook).

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!