Visual basic ошибка 438

Return to VBA Code Examples

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.

VBAError438 errormsg

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

VBAError438 debug

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.

VBAError438 properties

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

VBAError438 auto list members

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).

VBAError438 menu object browser

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).

VBAError438 object browser

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!
vba save as

Learn More!

First of all, you should know, that some of functions, used on the worksheet, have limitations. So my point is avoid of using them in VBA, if it is not necessary.
For example, function POWER() returns error on attempt to raise a zero to zero. An alternative is to use 0 ^ 0 combination, which is exactly doing the same, but looks more simply and operates without such error.
But also there is no embedded alternative in VBA to the FACT() function, so you can use it, or simply add your own function factor() — it’s uppon your choise.

If you just have started learning VBA, I would recomend you to use Option Explicit. It will help you to find out, which variables are not defined, and sometimes to avoid errors related to variable names missprint.

Here is your code, fixed and a little bit optimized:

Option Explicit' It is an option that turns on check for every used variable to be defined before execution. If this option is not defined, your code below will find undefined variables and define them when they are used. Good practice is to use this option, because it helps you, for example to prevent missprinting errors in variable names.

Sub Bezier()
    Dim C as Double , t As Double
    Dim k  As Long, n As Long, i As Long
    n = 3
    For i = 0 To 100
        t = i * 0.01
        Cells(i + 2, 6) = 0
        Cells(i + 2, 7) = 0
        For k = 0 To n
            C = (WorksheetFunction.Fact(n) / WorksheetFunction.Fact(k)) / WorksheetFunction.Fact(n - k)
            Cells(i + 2, 6) = Cells(i + 2, 6).Value + Cells(k + 2, 1).Value * C * (t ^ k) * ((1 - t) ^ (n - k))
            Cells(i + 2, 7) = Cells(i + 2, 7).Value + Cells(k + 2, 2).Value * C * (t ^ k) * ((1 - t) ^ (n - k))
        Next
    Next
End Sub

UPDATE
Here are some examples of factorial calculations.

    Public Function fnFact(number) ' a simple cycle example of Factorial function
    Dim tmp As Long ' new temporary variable to keep the "number" variable unchanged
    tmp = number
    fnFact = number
    While tmp > 1
        tmp = tmp - 1
        fnFact = fnFact * tmp
    Wend
End Function

Public Function fnFactR(number) ' a simple example of recursive function for Factorial calculation
    If number > 0 Then
        fnFactR = fnFactR(number - 1) * number ' function calls itself to continue calculations
    Else
        fnFactR = 1 ' function returns {1} when calculations are over
    End If
End Function

Sub Factor_test() 'RUN ME TO TEST ALL THE FACTORIAL FUNCTIONS
    Dim number As Long
    number = 170 ' change me to find Factorial for a different value
    MsgBox "Cycle Factorial:" & vbNewLine & number & "!= " & fnFact(number)
    MsgBox "WorksheetFunction Factorial:" & vbNewLine & number & "!= " & WorksheetFunction.Fact(number)
    MsgBox "Recursive Factorial:" & vbNewLine & number & "!= " & fnFactR(number)
End Sub

All those functions are available to calculate Factorial only for numbers before 170 inclusively, because of large result value.
So for my PC the limitation for WorksheetFunction.Fact() function is also 170.
Let me know, if your PC has different limitation for this function, — it’s quite interesting thing. :)

UPDATE2
It is recomended to use Long data type instead of Integer each type when integer (or whole number) variable is needed. Long is slightly faster, it has much wider limitations and costs no additional memory. Here are proof links:
1. MSDN:The Integer, Long, and Byte Data Types
2. ozgrid.com:Long Vs Integer
3. pcreview.co.uk:VBA code optimization — why using long instead of integer?
Thanks for @Ioannis and @chris neilsen for the information about Long data type and proof links!

Good luck in your further VBA actions!

I have this code in which I’ve been getting help with a bit, but I’ve run into an issue, or what I think is an issue. The last lookup, I am being told that the object doesn’t support this property or method. I know it’s probably something easy, but my brain is smoldering. I’d like some help if someone knows the answer of why this is happening.

Thanks.

Option Explicit

Sub Update_Dakota()

    Dim wsDAO As Worksheet              'Dakota OOR
    Dim wsDAD As Worksheet              'Dakota Data
    Dim wsDAR As Worksheet              'Dakota Archive
    Dim wsPOR As Workbook               'New Workbook
    Dim lastrow As Long, fstcell As Long
    Dim strFile As String, NewFileType As String, filename As String

    Set wsDAO = Sheets("Dakota OOR")
    Set wsDAD = Sheets("Dakota Data")
    Set wsDAR = Sheets("Dakota Archive")


    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    lastrow = wsDAD.Range("B" & Rows.Count).End(xlUp).Row + 1

    With wsDAD
        .Range("I2").Formula = "=COUNTIFS('Dakota OOR'!$B:$B,$A2,'Dakota OOR'!$D:$D,$C2, 'Dakota OOR'!$G:$G,$F2)"
        .Range("J2").Formula = "=IF(I2,""Same"",""Different"")"
        wsDAD.Range("I2:J2").Copy wsDAD.Range("I3:J" & lastrow)
        wsDAD.Range("I:J").Calculate
    End With


    strFile = Application.GetOpenFilename()
    NewFileType = "Excel Files 2007 (*.xls)"
    Set wsPOR = Application.Workbooks.Open(strFile)
    lastrow = wsPOR.Range("A" & Rows.Count).End(xlUp).Row + 1

    wsPOR.Range("A2:G" & lastrow).Select


End Sub

Community's user avatar

asked Sep 5, 2012 at 19:40

Matt Ridge's user avatar

1

The Error is here

lastrow = wsPOR.Range("A" & Rows.Count).End(xlUp).Row + 1

wsPOR is a workbook and not a worksheet. If you are working with «Sheet1» of that workbook then try this

lastrow = wsPOR.Sheets("Sheet1").Range("A" & _
          wsPOR.Sheets("Sheet1").Rows.Count).End(xlUp).Row + 1

Similarly

wsPOR.Range("A2:G" & lastrow).Select

should be

wsPOR.Sheets("Sheet1").Range("A2:G" & lastrow).Select

answered Sep 5, 2012 at 19:46

Siddharth Rout's user avatar

Siddharth RoutSiddharth Rout

147k17 gold badges206 silver badges250 bronze badges

6

title keywords f1_keywords ms.prod ms.assetid ms.date ms.localizationpriority

Object doesn’t support this property or method (Error 438)

vblr6.chm1011328

vblr6.chm1011328

office

0fbab746-dc6d-b227-429a-1f56bb4ca448

06/08/2017

medium

Object doesn’t support this property or method (Error 438)

Not all objects support all properties and methods. This error has the following cause and solution:

  • You specified a method or property that doesn’t exist for this Automation object.

    See the object’s documentation for more information on the object and check the spellings of properties and methods.

  • You specified a Friend procedure to be called late bound. The name of a Friend procedure must be known at compile time. It can’t appear in a late-bound call.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

[!includeSupport and feedback]

Home > VBA > VBA Object Doesn’t Support this Property or Method Error (Error 438)

VBA error 438 occurs when you try to use a property or method that does not support by that object. as you know all objects have some properties and methods that you can use but there could be a situation when using a property or method that does not apply to a particular object.

Let’s take an example to understand this: with the worksheet object there comes up a method to select the worksheet.

Now as you know you can activate a workbook but there is no method that you can use to select a workbook because you cannot select a workbook, you can only activate it.

So when you try to use this method with the workbook object you’ll get the runtime error 438. Even you can see this method is not in the list of properties and methods of the workbook object.

Now you can understand that it can be a mistake on the end of the person who writes the code and can be committed even if you are proficient in VBA.

Note: When you have written a code in the latest version of Microsoft Excel and now you try to run it in an older version, there’s could be a chance that that version doesn’t have a method or a property for the object you are using.

The best way to deal with this error 438 (Object Doesn’t Support this Property or Method) you need to be aware of the properties and methods that are supported by the object you are using.

When you define an object, you can see the list of all the properties and methods when you type a dot (.).

This list can help you to decide if the method you want to use is there or not. And if it’s not there you need to find a different way to write a code for the task that you want to accomplish. Otherwise, you can also open the object browser (F2) to see the properties and methods you have with an object.

What is VBA

  • VBA ERROR Handling
  • VBA Automation Error (Error 440)
  • VBA Error 400
  • VBA Invalid Procedure Call Or Argument Error (Error 5)
  • VBA Object Required Error (Error 424)
  • VBA Out of Memory Error (Error 7)
  • VBA Overflow Error (Error 6)
  • VBA Runtime Error (Error 1004)
  • VBA Subscript Out of Range Runtime Error (Error 9)
  • VBA Type Mismatch Error (Error 13)

Возможно, вам также будет интересно:

  • Visit workshop мерседес значение ошибки
  • Visit workshop display faulty w203 ошибка
  • Visio произошла ошибка во время последнего запуска
  • Visio ошибка 920 при выполнении действия сохранить как
  • Visio ошибка 1203 нельзя создать объект

  • Понравилась статья? Поделить с друзьями:
    0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии