Run time error 438 vba excel как исправить

Run time error 438 vba excel как исправить

Frequently encountering Run-time error ‘438′: Object doesn’t support this property or method whenever you try to start work in Excel?

Don’t have any idea what causing this Excel runtime error 438 and how to fix it?

Well don’t get worried about it, as this post will help you to get the best fixes to resolve Excel error 438: Object doesn’t support this property or method error.  Not only this, but you will also get complete information about this Excel error 438.

What Is Excel Runtime Error 438?

Mostly it is seen that the user stuck into such annoying error code in macro when the object doesn’t support by the property or method.

If any Excel user creates a toolbar in Excel by using visual basic code then also the following error code occurs:

Run-time error “438”: Object doesn’t support this property or method

To recover lost Excel objects, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

Error Detail:

Error code: Run-time error ‘438′

Error name: Object doesn’t support this property or method

Error Screenshot:

Object doesn't support this property or method

What Are The Circumstances In Which Run-Time Error 438 In Excel Occurs?

There is not any specific reason for encountering this Excel runtime error 438: Object doesn’t support this property or method.

It is found that this Excel runtime error 438 occurs under several circumstances. So check this out:

  • When anyone tries to make use of variables for workbooks and worksheet names.
  • When executing a program within which form is already allotted to a variable. And that specific variable is now been used for accessing control over the form.
  • This error also occurs when an installed AMD driver becomes out of date.
  • The Macro you are using is maybe a wrong one or maybe it’s not working. Ultimately this will throw Excel runtime error 438.
  • Runtime error 438 also encounters when you are trying to execute the designed macro of MS Excel previous version, into the latest MS Excel application.
  • At the time of creating a custom toolbar in their Excel worksheet. User encounters a task failure error message i.e. “Object doesn’t support this property or method: Run-Time Error 438.
  • In another instance in which this error occurs, the user tries to run the Microsoft VB for Excel macro. This macro tries to set the Excel worksheet properties but fails to complete this task which ultimately results in runtime error 438 in Excel.

After catching the complete idea of what can be the reasons for the Excel runtime error 438. Now you can easily make a keen check over the sections where this problem can generate.

How To Fix Runtime Error 438 In Excel?

Fix 1# MS Office Version Supporting Issue

Runtime error 438 in Excel also encounters while trying to work with the outdated macro function designed in older version MS Office application in some latest version of MS Office.

For this, I will recommend you to, use your macro in the respective version of MS Office application in which you have designed it. OR else you can get help from this helpful post [FIXED]: “This File is Not in Recognizable Format” Excel Error.

Fix 2# Check The Codings

As we have already discussed that Excel Runtime Error 438 also occurs due to the incorrect creation of a macro. Or when the user tries t0 run the macro which Excel objects don’t support property or method.

So, to resolve this Excel Object doesn’t support this property or method error user needs to check or rewrite the coding within the VBA module.

If you are not having good command over the programming then you can contact Microsoft Advisory Services.

Fix 3# Uninstall Microsoft Works Add-in:

It is seen that Microsoft works add-in generates this Excel Object doesn’t support this property or method error.  So, uninstall this add-in just by following these steps:

  • Go to the Start menu then click the Settings option and then on the Control Panel.
  • Now tap to the Add / Remove Programs.
  • Hit the File Location present within the Options.
  • From Uninstall/Install tab, choose the add-in suit i.e Word in Works. After then tap the Add / Remove.
  • Now carefully follow the screen instructions.
  • Restart your PC and attempt to load Microsoft Word again.

This will stop the error from occurring again because you have successfully uninstalled the problem causing Works for Word add-in the program.

Fix 4# Use Microsoft Fix-It #50356

In many cases it is found that by downloading the Microsoft Fix-It #50356 user has successfully overcome the Excel error 438: Object doesn’t support this property or method.

So, you can also download the Microsoft Fix-It Patch from this link: https://support.microsoft.com/en-in/help/2970908/how-to-use-microsoft-easy-fix-solutions After complete downloading, the wizard will assist you throughout the tasks that you have to perform.

Through this Microsoft #50356 hotfix broken registry strings that are causing the issue can easily be repaired. This patch gives new keys on behave of broken registry keys. If your registry keys are broken then your Windows application displays the error message. But after downing this new patch in your PC your Window won’t show any error regarding Object doesn’t support this property or method.

If even after trying the above fixes the problem won’t resolve then move to the next solution.

Fix 5# Disable or Uninstall Windows Antivirus Software

Sometimes installed anti-viruses on your system also cause this runtime error 438. So, by disabling or uninstalling the anti-virus software you can easily get rid of this issue.

Steps to uninstall antivirus program from your PC:

  • Open the control panel of your PC.
  • After then make double-tap to Add/Remove Programs
  • Choose the antivirus program which you want to uninstall from your PC. After then, tap to the Remove or Change/Remove option.

Disable or Uninstall Windows Antivirus Software

  • Carefully follow the on-screen instructions for removing up the antivirus program. Once it gets over, restart your PC.

Fix 6# Reinstall The Device Drivers For The Device

Reinstalling the device driver can fix Excel Runtime Error 438. Try the following steps to resolve Object doesn’t support this property or method:

  1. Go to your system taskbar and make a tap on the start button. Here you will see a search box, in this box type device manager. After then choose the Device Manager.
  2. Make a right-click on the device name you need to uninstall and choose the Uninstall option.
  3. After uninstalling the device, restart your PC.
  4. Windows will try for driver reinstallation.

Reinstall The Drivers For The Device

Fix 7: Resolve The Corruption Issue

Sometimes Excel sheet gets damaged or corrupt due to so many reasons like sudden system shutdown, software malfunction, virus attack, etc. Once the Excel spreadsheet gets corrupted /damaged you can’t access it anymore or it starts throwing error messages. So, the chances are high that some of your Excel file Objects got corrupted and thus it showing Object doesn’t support this property or method error.

Hence for the quick and easy solution to repair and restore corrupt Excel files go with the recommended option i.e Excel Repair Tool. It is the best software for repairing the damaged excel file.

* Free version of the product only previews recoverable data.

With this efficient repair tool, user can easily be able to fix all known errors that lead to corruption of excel files on Mac. It deeply scans the selected excel files without making any changes to the original content of the worksheet and fetches all the issues.  It restores all the charts, objects, hidden sheets, pictures, clip charts, and other important Excel file content.

Wrap Up:

Carefully try all the above fixes to resolve runtime error 438 in Excel as some of the listed fixes may hamper your system settings if performed incorrectly. Even after trying all the above fixes if the Excel Object doesn’t support this property or method error won’t be resolved then let us inform by commenting in our comment section.

Priyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.

Источник

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!

Источник

The error is here:

If wksht = wkbk.Sheets(2) Then

The Worksheet class doesn’t have a default property, so you can’t let-coerce that object; VBA is trying to invoke the default member of Worksheet to satisfy the comparison operator, but there’s no such member, so error 438 is raised.

I think what you’re trying to do is to validate whether wksht is wkbk.Sheets(2). You need the Is operator for reference equality:

If wksht Is wkbk.Sheets(2) Then

Note that this is different from comparing the .Name of the sheets: here we’re comparing object references.

That said, I’d buy a vowel or two here, it’s too easy to make a typo typing those… Make sure Option Explicit is specified!

Addendum

wkbk.Sheets.Add Before:=wkbk.Sheets(1)
Set DataSheet = ActiveSheet

Worksheets.Add returns the added worksheet object, so you can do this instead, and not rely on the side-effect of the added sheet now being the ActiveSheet:

Set DataSheet = wkbk.Sheets.Add(Before:=wkbk.Sheets(1))

Источник

Есть файл, работающий уже лет 7. На странице кнопка, при открытии проверяется контрольное поле и в зависимости от этого кнопка активна или нет (.Enable=True or False). Файл работал в нескольких версиях оффиса. Последнее время на 2010 и 2013 (основная версия). В результате последнего апдейта оффиса стало появляться сообщение, приведенное в описании темы. Кроме того, ранее в режиме конструктора можно было просмотреть свойства объекта (кнопки, к примеру) на листе, теперь же вызываются свойства листа, содержащего объект.
Проверил на соседней машине, оффис 2010 СП2 без последних апдейтов – все ОК. Выполнил обновление – картина та же, что описана выше…

Кто-нить сталкивался с подобной проблемой? Спасибо!

ЗЫ: Кстати, подобную проблему нашел в архиве за 2012, но на ХР у меня не возникало подобных ошибок…

http://www.planetaexcel.ru/forum/?PAGE_NAME=read&FID=8&TID=42275

Источник

David Zemens got your answer.

Here’s how to avoid repeating it.

Looking at this line:

For Each item In Worksheets("Collector").SlicerCaches("Slicer_RptDate").SlicerItems

If we made every statement explicit, it would look like this:

For Each item In Worksheets.Item("Collector").SlicerCaches.Item("Slicer_RptDate").SlicerItems

In other words:

Worksheets.Item("Collector") _
          .SlicerCaches.Item("Slicer_RptDate") _
                       .SlicerItems

That’s a lot of member accesses for a single instruction.

By introducing intermediate variables…

Dim collectorSheet As Worksheet
Set collectorSheet = Worksheets("Collector")

Dim rptDateSlicerCache As SlicerCache
Set rptDateSlicerCache = collectorSheet.SlicerCaches("Slicer_RptDate") '*

For Each item In rptDateSlicerCache.SlicerItems
    '...
Next

…you could have noticed while typing the line marked with a '* comment, that IntelliSense doesn’t offer SlicerCaches as a member of collectorSheet.

Why? Because this:

Worksheets("Collector")

Returns an Object – and from that point on, you’re on your own: IntelliSense can’t help you with autocompletion, because members of an Object aren’t resolved until runtime.

By assigning that object to a Worksheet variable, you give yourself compile-time checking, and avoid that pesky runtime error 438.

Источник

Добавить комментарий