Type mismatch vba word ошибка - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

I have the following code as part of my sub trying to assign a range:

'Set xlApp = CreateObject("Excel.Application")

Dim xlApp As Object
Set xlApp = GetObject(, "Excel.Application")
xlApp.Visible = False
xlApp.ScreenUpdating = False

Dim CRsFile As String
Dim CRsMaxRow As Integer

' get the CR list
CRsFile = "CRs.xls"
Set CRsWB = xlApp.Workbooks.Open("C:Docs" + CRsFile)
With CRsWB.Worksheets("Sheet1")
  .Activate
  CRsMaxRow = .Range("A1").CurrentRegion.Rows.Count

  Set CRs = .Range("A2:M" & CRsMaxRow)

End With

Dim interestingFiles As Range

' get the files names that we consider interesting to track
Set FilesWB = xlApp.Workbooks.Open("files.xlsx")
With FilesWB.Worksheets("files")
    .Activate
    Set interestingFiles = .Range("A2:E5")
End With

Do you have any idea why am I getting a run time type mismatch error?

asked May 5, 2013 at 12:51

If you run the code from Word then the problem is in the declaration of ‘interestingFiles’ variable. Range exist in Word as well so use either Variant or add reference to Excel and then use Excel.Range.

Without Excel reference:

Dim interestingFiles As Variant

And with Excel reference:

Dim interestingFiles As Excel.Range

answered May 5, 2013 at 16:04

Daniel DušekDaniel Dušek

13.6k5 gold badges35 silver badges51 bronze badges

Kindly set xlApp object as in below code.
Also you provide complete path for your workbook when opening it.

Sub test()
        Dim interestingFiles As Range
        Dim xlApp As Object

        Set xlApp = GetObject(, "Excel.Application")
        ' get the files names

        Dim path As String
        path = "C:UsersSantoshDesktopfile1.xlsx"

        Set FilesWB = xlApp.Workbooks.Open(path)
        With FilesWB.Worksheets(1)
            .Activate
            Set interestingFiles = .Range("A2:E5")
        End With

    End Sub

answered May 5, 2013 at 13:00

SantoshSantosh

12.1k4 gold badges41 silver badges72 bronze badges

Источник

title	keywords	f1_keywords	ms.prod	ms.assetid	ms.date	ms.localizationpriority
Type mismatch (Error 13)	vblr6.chm1011290	vblr6.chm1011290	office	cbc7e902-b468-c335-5620-1ff9a2026b9b	08/14/2019	medium

Visual Basic is able to convert and coerce many values to accomplish data type assignments that weren’t possible in earlier versions.

However, this error can still occur and has the following causes and solutions:

Cause: The variable or property isn’t of the correct type. For example, a variable that requires an integer value can’t accept a string value unless the whole string can be recognized as an integer.

Solution: Try to make assignments only between compatible data types. For example, an Integer can always be assigned to a Long, a Single can always be assigned to a Double, and any type (except a user-defined type) can be assigned to a Variant.

Cause: An object was passed to a procedure that is expecting a single property or value.

Solution: Pass the appropriate single property or call a method appropriate to the object.

Cause: A module or project name was used where an expression was expected, for example:

Solution: Specify an expression that can be displayed.

Cause: You attempted to mix traditional Basic error handling with Variant values having the Error subtype (10, vbError), for example:

Solution: To regenerate an error, you must map it to an intrinsic Visual Basic or a user-defined error, and then generate that error.

Cause: A CVErr value can’t be converted to Date. For example:

Solution: Use a Select Case statement or some similar construct to map the return of CVErr to such a value.

Cause: At run time, this error typically indicates that a Variant used in an expression has an incorrect subtype, or a Variant containing an array appears in a Print # statement.

Solution: To print arrays, create a loop that displays each element individually.

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

[!includeAdd-ins note]

[!includeSupport and feedback]

Источник

Contents

1 VBA Type Mismatch Explained
2 VBA Type Mismatch YouTube Video
3 How to Locate the Type Mismatch Error
4 Assigning a string to a numeric
5 Invalid date
6 Cell Error
7 Invalid Cell Data
8 Module Name
9 Different Object Types
10 Sheets Collection
11 Array and Range
12 Conclusion
13 What’s Next?

VBA Type Mismatch Explained

A VBA Type Mismatch Error occurs when you try to assign a value between two different variable types.

The error appears as “run-time error 13 – Type mismatch”.

For example, if you try to place text in a Long integer variable or you try to place text in a Date variable.

Let’s look at a concrete example. Imagine we have a variable called Total which is a Long integer.

If we try to place text in the variable we will get the VBA Type mismatch error(i.e. VBA Error 13).

' https://excelmacromastery.com/
Sub TypeMismatchString()

    ' Declare a variable of type long integer
    Dim total As Long
    
    ' Assigning a string will cause a type mismatch error
    total = "John"
    
End Sub

Let’s look at another example. This time we have a variable ReportDate of type Date.

If we try to place a non-date in this variable we will get a VBA Type mismatch error

' https://excelmacromastery.com/
Sub TypeMismatchDate()

    ' Declare a variable of type Date
    Dim ReportDate As Date
    
    ' Assigning a number causes a type mismatch error
    ReportDate = "21-22"
    
End Sub

In general, VBA is very forgiving when you assign the wrong value type to a variable e.g.

Dim x As Long

' VBA will convert to integer 100
x = 99.66

' VBA will convert to integer 66
x = "66"

However, there are some conversions that VBA cannot do

Dim x As Long

' Type mismatch error
x = "66a"

A simple way to explain a VBA Type mismatch error, is that the items on either side of the equals evaluate to a different type.

When a Type mismatch error occurs it is often not as simple as these examples. For these more complex cases we can use the Debugging tools to help us resolve the error.

VBA Type Mismatch YouTube Video

Don’t forget to check out my YouTube video on the Type Mismatch Error here:

How to Locate the Type Mismatch Error

The most important thing to do when solving the Type Mismatch error is to, first of all, locate the line with the error and then locate the part of the line that is causing the error.

If your code has Error Handling then it may not be obvious which line has the error.

If the line of code is complex then it may not be obvious which part is causing the error.

The following video will show you how to find the exact piece of code that causes a VBA Error in under a minute:

The following sections show the different ways that the VBA Type Mismatch error can occur.

Assigning a string to a numeric

As we have seen, trying to place text in a numeric variable can lead to the VBA Type mismatch error.

Below are some examples that will cause the error

' https://excelmacromastery.com/
Sub TextErrors()

    ' Long is a long integer
    Dim l As Long
    l = "a"
    
    ' Double is a decimal number
    Dim d As Double
    d = "a"
    
    ' Currency is a 4 decimal place number
    Dim c As Currency
    c = "a"
    
    Dim d As Double
    ' Type mismatch if the cell contains text
    d = Range("A1").Value
    
End Sub

Invalid date

VBA is very flexible when it comes to assigning a date to a date variable. If you put the month in the wrong order or leave out the day, VBA will still do it’s best to accommodate you.

The following code examples show all the valid ways to assign a date followed by the cases that will cause a VBA Type mismatch error.

' https://excelmacromastery.com/
Sub DateMismatch()

    Dim curDate As Date
    
    ' VBA will do it's best for you
    ' - These are all valid
    curDate = "12/12/2016"
    curDate = "12-12-2016"
    curDate = #12/12/2016#
    curDate = "11/Aug/2016"
    curDate = "11/Augu/2016"
    curDate = "11/Augus/2016"
    curDate = "11/August/2016"
    curDate = "19/11/2016"
    curDate = "11/19/2016"
    curDate = "1/1"
    curDate = "1/2016"
   
    ' Type Mismatch
    curDate = "19/19/2016"
    curDate = "19/Au/2016"
    curDate = "19/Augusta/2016"
    curDate = "August"
    curDate = "Some Random Text"

End Sub

Cell Error

A subtle cause of the VBA Type Mismatch error is when you read from a cell that has an error e.g.

If you try to read from this cell you will get a type mismatch error

Dim sText As String

' Type Mismatch if the cell contains an error
sText = Sheet1.Range("A1").Value

To resolve this error you can check the cell using IsError as follows.

Dim sText As String
If IsError(Sheet1.Range("A1").Value) = False Then
    sText = Sheet1.Range("A1").Value
End If

However, checking all the cells for errors is not feasible and would make your code unwieldy. A better way is to check the sheet for errors first and if errors are found then inform the user.

You can use the following function to do this

Function CheckForErrors(rg As Range) As Long

    On Error Resume Next
    CheckForErrors = rg.SpecialCells(xlCellTypeFormulas, xlErrors).Count

End Function

The following is an example of using this code

' https://excelmacromastery.com/
Sub DoStuff()

    If CheckForErrors(Sheet1.Range("A1:Z1000")) > 0 Then
        MsgBox "There are errors on the worksheet. Please fix and run macro again."
        Exit Sub
    End If
    
    ' Continue here if no error

End Sub

Invalid Cell Data

As we saw, placing an incorrect value type in a variable causes the ‘VBA Type Mismatch’ error. A very common cause is when the value in a cell is not of the correct type.

A user could place text like ‘None’ in a number field not realizing that this will cause a Type mismatch error in the code.

If we read this data into a number variable then we will get a ‘VBA Type Mismatch’ error error.

Dim rg As Range
Set rg = Sheet1.Range("B2:B5")

Dim cell As Range, Amount As Long
For Each cell In rg
    ' Error when reaches cell with 'None' text
    Amount = cell.Value
Next rg

You can use the following function to check for non numeric cells before you use the data

Function CheckForTextCells(rg As Range) As Long

    ' Count numeric cells
    If rg.Count = rg.SpecialCells(xlCellTypeConstants, xlNumbers).Count Then
        CheckForTextCells = True
    End If
    
End Function

You can use it like this

' https://excelmacromastery.com/
Sub UseCells()

    If CheckForTextCells(Sheet1.Range("B2:B6").Value) = False Then
        MsgBox "One of the cells is not numeric. Please fix before running macro"
        Exit Sub
    End If
    
    ' Continue here if no error

End Sub

Module Name

If you use the Module name in your code this can cause the VBA Type mismatch to occur. However in this case the cause may not be obvious.

For example let’s say you have a Module called ‘Module1’. Running the following code would result in the VBA Type mismatch error.

' https://excelmacromastery.com/
Sub UseModuleName()
    
    ' Type Mismatch
    Debug.Print module1

End Sub

Different Object Types

So far we have been looking mainly at variables. We normally refer to variables as basic data types.

They are used to store a single value in memory.

In VBA we also have objects which are more complex. Examples are the Workbook, Worksheet, Range and Chart objects.

If we are assigning one of these types we must ensure the item being assigned is the same kind of object. For Example

' https://excelmacromastery.com/
Sub UsingWorksheet()

    Dim wk As Worksheet
    
    ' Valid
    Set wk = ThisWorkbook.Worksheets(1)
    
    ' Type Mismatch error
    ' Left side is a worksheet - right side is a workbook
    Set wk = Workbooks(1)

End Sub

Sheets Collection

In VBA, the workbook object has two collections – Sheets and Worksheets. There is a very subtle difference

Worksheets – the collection of worksheets in the Workbook
Sheets – the collection of worksheets and chart sheets in the Workbook

A chart sheet is created when you move a chart to it’s own sheet by right-clicking on the chart and selecting Move.

If you read the Sheets collection using a Worksheet variable it will work fine if you don’t have a chart sheet in your workbook.

If you do have a chart sheet then you will get the VBA Type mismatch error.

In the following code, a Type mismatch error will appear on the Next sh line if the workbook contains a chart sheet.

' https://excelmacromastery.com/
Sub SheetsError()

    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Sheets
        Debug.Print sh.Name
    Next sh

End Sub

Array and Range

You can assign a range to an array and vice versa. In fact this is a very fast way of reading through data.

' https://excelmacromastery.com/
Sub UseArray()

    Dim arr As Variant
    
    ' Assign the range to an array
    arr = Sheet1.Range("A1:B2").Value
    
    ' Print the value a row 1, column 1
    Debug.Print arr(1, 1)

End Sub

The problem occurs if your range has only one cell. In this case, VBA does not convert arr to an array.

If you try to use it as an array you will get the Type mismatch error

' https://excelmacromastery.com/
Sub UseArrayError()

    Dim arr As Variant
    
    ' Assign the range to an array
    arr = Sheet1.Range("A1").Value
    
    ' Type mismatch will occur here
    Debug.Print arr(1, 1)

End Sub

In this scenario, you can use the IsArray function to check if arr is an array

' https://excelmacromastery.com/
Sub UseArrayIf()

    Dim arr As Variant
    
    ' Assign the range to an array
    arr = Sheet1.Range("A1").Value
    
    ' Type mismatch will occur here
    If IsArray(arr) Then
        Debug.Print arr(1, 1)
    Else
        Debug.Print arr
    End If

End Sub

Conclusion

This concludes the post on the VBA Type mismatch error. If you have a mismatch error that isn’t covered then please let me know in the comments.

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

Источник

Type Mismatch Error VBA возникает при попытке назначить значение между двумя различными типами переменных.

Ошибка отображается как:
run-time error 13 – Type mismatch

Например, если вы пытаетесь поместить текст в целочисленную переменную Long или пытаетесь поместить число в переменную Date.

Давайте посмотрим на конкретный пример. Представьте, что у нас есть переменная с именем Total, которая является длинным целым числом Long.

Если мы попытаемся поместить текст в переменную, мы получим Type Mismatch Error VBA (т.е. VBA Error 13).

Давайте посмотрим на другой пример. На этот раз у нас есть переменная ReportDate типа Date.

Если мы попытаемся поместить в эту переменную не дату, мы получим Type Mismatch Error VBA.

В целом, VBA часто прощает, когда вы назначаете неправильный тип значения переменной, например:

Тем не менее, есть некоторые преобразования, которые VBA не может сделать:

Простой способ объяснить Type Mismatch Error VBA состоит в том, что элементы по обе стороны от равных оценивают другой тип.

При возникновении Type Mismatch Error это часто не так просто, как в этих примерах. В этих более сложных случаях мы можем использовать средства отладки, чтобы помочь нам устранить ошибку.

Использование отладчика

В VBA есть несколько очень мощных инструментов для поиска ошибок. Инструменты отладки позволяют приостановить выполнение кода и проверить значения в текущих переменных.

Вы можете использовать следующие шаги, чтобы помочь вам устранить любую Type Mismatch Error VBA.

Запустите код, чтобы появилась ошибка.
Нажмите Debug в диалоговом окне ошибки. Это выделит строку с ошибкой.
Выберите View-> Watch из меню, если окно просмотра не видно.
Выделите переменную слева от equals и перетащите ее в окно Watch.
Выделите все справа от равных и перетащите его в окно Watch.
Проверьте значения и типы каждого.
Вы можете сузить ошибку, изучив отдельные части правой стороны.

Следующее видео показывает, как это сделать.

На скриншоте ниже вы можете увидеть типы в окне просмотра.

Используя окно просмотра, вы можете проверить различные части строки кода с ошибкой. Затем вы можете легко увидеть, что это за типы переменных.

В следующих разделах показаны различные способы возникновения Type Mismatch Error VBA.

Присвоение строки числу

Как мы уже видели, попытка поместить текст в числовую переменную может привести к Type Mismatch Error VBA.

Ниже приведены некоторые примеры, которые могут вызвать ошибку:

Недействительная дата

VBA очень гибок в назначении даты переменной даты. Если вы поставите месяц в неправильном порядке или пропустите день, VBA все равно сделает все возможное, чтобы удовлетворить вас.

В следующих примерах кода показаны все допустимые способы назначения даты, за которыми следуют случаи, которые могут привести к Type Mismatch Error VBA.

Ошибка ячейки

Тонкая причина Type Mismatch Error VBA — это когда вы читаете из ячейки с ошибкой, например:

Если вы попытаетесь прочитать из этой ячейки, вы получите Type Mismatch Error.

Чтобы устранить эту ошибку, вы можете проверить ячейку с помощью IsError следующим образом.

Однако проверка всех ячеек на наличие ошибок невозможна и сделает ваш код громоздким. Лучший способ — сначала проверить лист на наличие ошибок, а если ошибки найдены, сообщить об этом пользователю.

Вы можете использовать следующую функцию, чтобы сделать это:

Ниже приведен пример использования этого кода.

Неверные данные ячейки

Как мы видели, размещение неверного типа значения в переменной вызывает Type Mismatch Error VBA. Очень распространенная причина — это когда значение в ячейке имеет неправильный тип.

Пользователь может поместить текст, такой как «Нет», в числовое поле, не осознавая, что это приведет к Type Mismatch Error в коде.

Если мы прочитаем эти данные в числовую переменную, то получим
Type Mismatch Error VBA.

Вы можете использовать следующую функцию, чтобы проверить наличие нечисловых ячеек, прежде чем использовать данные.

Вы можете использовать это так:

Имя модуля

Если вы используете имя модуля в своем коде, это может привести к
Type Mismatch Error VBA. Однако в этом случае причина может быть не очевидной.

Например, допустим, у вас есть модуль с именем «Module1». Выполнение следующего кода приведет к о
Type Mismatch Error VBA.

Различные типы объектов

До сих пор мы рассматривали в основном переменные. Мы обычно называем переменные основными типами данных.

Они используются для хранения одного значения в памяти.

В VBA у нас также есть объекты, которые являются более сложными. Примерами являются объекты Workbook, Worksheet, Range и Chart.

Если мы назначаем один из этих типов, мы должны убедиться, что назначаемый элемент является объектом того же типа. Например:

Коллекция Sheets

В VBA объект рабочей книги имеет две коллекции — Sheets и Worksheets. Есть очень тонкая разница.

Worksheets — сборник рабочих листов в Workbook
Sheets — сборник рабочих листов и диаграммных листов в Workbook

Лист диаграммы создается, когда вы перемещаете диаграмму на собственный лист, щелкая правой кнопкой мыши на диаграмме и выбирая «Переместить».

Если вы читаете коллекцию Sheets с помощью переменной Worksheet, она будет работать нормально, если у вас нет рабочей таблицы.

Если у вас есть лист диаграммы, вы получите
Type Mismatch Error VBA.

В следующем коде Type Mismatch Error появится в строке «Next sh», если рабочая книга содержит лист с диаграммой.

Массивы и диапазоны

Вы можете назначить диапазон массиву и наоборот. На самом деле это очень быстрый способ чтения данных.

Проблема возникает, если ваш диапазон имеет только одну ячейку. В этом случае VBA не преобразует arr в массив.

Если вы попытаетесь использовать его как массив, вы получите
Type Mismatch Error .

В этом сценарии вы можете использовать функцию IsArray, чтобы проверить, является ли arr массивом.

Заключение

На этом мы завершаем статью об Type Mismatch Error VBA. Если у вас есть ошибка несоответствия, которая не раскрыта, пожалуйста, дайте мне знать в комментариях.

7 Ways To Fix Excel Runtime Error 13 Type Mismatch

Summary:

This post is written with the main prospective of providing you all with ample amount of detail regarding Excel runtime error 13. So go through this complete guide to know how to fix runtime error 13 type mismatch.

In our earlier blogs, we have described the commonly found Excel file runtime error 1004, 32809 and 57121. Today in this article we are describing another Excel file runtime error 13.

What Is Runtime Error 13 Type Mismatch?

Run-time error ‘13’: Type Mismatch usually occurs meanwhile the code is executed in Excel. As a result of this, you may get terminated every time from all the ongoing activities on your Excel application.

This run time error 13 also put an adverse effect on XLS/XLSX files. So before this Excel Type Mismatch error damages your Excel files, fix it out immediately with the given fixes.

Apart from that, there are many reasons behind getting the Excel file runtime error 13 when the Excel file gets corrupted this starts showing runtime error.

To recover lost Excel data, 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:

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

Error Detail:

Error code: Run-time error ‘13’

Declaration: Excel Type Mismatch error

Here is the screenshot of this error:

Why Am I Getting Excel Runtime Error 13 Type Mismatch?

Following are some reasons for run time error 13 type mismatch:

When multiple methods or files require to starts a program that uses Visual Basic (VB) environment
Runtime error 13 often occurs when mismatches occur within the software applications which you require to use.
Due to virus and malware infection as this corrupts the Windows system files or Excel-related files.
When you tap on the function or macro present on the menu which is created by another Macro then also you will receive the same run time error 13.
The runtime error commonly occurs due to the conflict between the software and the operating system.
Due to the corrupt or incomplete installation of Microsoft Excel software.
The Run-time Error 13 appears when the users try to run VBA code that includes data types that are not matched correctly. Thus it starts displaying Runtimeerror 13 type mismatch.
Due to conflict with other programs while opening the VBA Excel file.

Well, these are some of the common reasons for getting the Excel file runtime error 13.

How To Fix Excel Runtime Error 13 Type Mismatch?

Learn how to Fix Excel Runtime Error 13 Type Mismatch.

1: Using Open and Repair Utility

There is a ‘File Recovery’ mode within Excel which gets activated automatically when any corruption issue hits your worksheet or workbook.

But in some cases, Excel won’t offer this ‘File Recovery’ mode and at that time you need to use Excel inbuilt tool ‘Open and Repair’.

Using this inbuilt utility tool you can recover corrupted/damaged Excel files. Try the following steps to fix Visual Basic runtime error 13 type mismatch in Excel.

Here follow the steps to do so:

In the File menu> click “Open”
And select corrupt Excel file > from the drop-down list of open tab > select “Open and Repair”

Lastly, click on the “Repair” button.

However, it is found that the inbuilt repair utility fails to repair the severely damaged Excel file.

2. Uninstall The Program

It is found some application and software causes the runtime error.

So, to fix the Excel file error, simply uninstall the problematic apps and programs.

First, go to the Task Manager and stop the running programs.
Then in the start menu > select Control Panel.
In the Control Panel > choose Add or Remove Program.

Here, you will get the list of installed programs on your PC.

Then from the list select Microsoft Work.
Click on uninstall to remove it from the PC.

Hope doing this will fix the Excel file Runtime error 13, but if not then follow the third solution.

3. Scan For Virus/Malware

Virus intrusion is quite a big problem for all Windows users, as it causes several issues for PC and Excel files.

This can be the great reason behind this Runtime 13 error. As viruses damage the core program file of MS Office which is important for the execution of Excel application.

This makes the file unreadable and starts generating the following error message: Visual Basic runtime error 13 type mismatch in Excel

To avoid this error, you need to remove all virus infections from your system using the reliable anti-virus removal tool.

Well, it is found that if your Windows operating system in having viruses and malware then this might corrupt Excel file and as a result, you start facing the runtime file error 13.

So, it is recommended to scan your system with the best antivirus program and make your system malware-free. Ultimately this will also fix runtime error 13.

4. Recover Missing Macros

Well, as it is found that users are getting the runtime error 13 due to the missing macros, So try to recover the missing Macros.

Here follow the steps to do so:

Open the new Excel file > and set the calculation mode to Manual
Now from the Tools menu select Macro > select Security > High option.
If you are using Excel 2007, then click the Office button > Excel Options > Trust Center in the left panel
And click on Trust Center Settings button > Macro Settings > Disable All Macros without Notification in the Macro Settings section > click OK twice.

Now, open the corrupted workbook. If Excel opens the workbook a message appears that the macros are disabled.
But if in case Excel shut down, then this method is not workable.
Next press [Alt] + [F11] for opening the Visual Basic Editor (VBE).
Make use of the Project Explorer (press [Ctrl]+R) > right-click a module > Export File.

Type name and folder for the module > and repeat this step as many times as required to export the entire module.
Finally, close the VBE and exit.

Now open the new blank workbook (or the recently constructed workbook that contains recovered data from the corrupted workbook) and import the modules.

5. Run The ‘Regedit’ Command In CMD

This Excel error 13 can also be fixed by running the ‘Regedit’ command in the command prompt.

In the search menu of your system’s start menu type run command.
Now in the opened run dialog box type “regedit” command. After that hit the OK
This will open the registry editor. On its right side there is a ‘LoadApplnit_DLLs value.’ option, just make double-tap to it.
Change the value from 1 to ‘0‘and then press the OK.

Now take exit from this opened registry editor.
After completing all this, restart your PC.

Making the above changes will definitely resolve the Runtime Error 13 Type Mismatch.

6: Create New Disk Partition And Reinstall Windows

If even after trying all the above-given fixes Excel type mismatched error still persists. In that case, the last option left here is to create the new partition and reinstall Windows.

In your PC insert windows DVD/CD and after that begin the installation procedure.
For installation, choose the language preference.
Tap to the option” I accept” and then hit the NEXT
Select the custom advance option and then choose the Disk O partition 1

Now hit the delete> OK button.
The same thing you have to repeat after selecting the Disk O partition 2.
Now hit the delete> OK button to delete this too.
After completing the deletion procedure, tap to create a new partition.
Assign the disk size and tap to the Apply.

Now choose the Disk 0 partition 2 and then hit the Formatting.
After complete formatting, hit the NEXT button to continue.

Note: before attempting this procedure don’t forget to keep a complete backup of all your data.

However, if you are still facing the Excel Runtime file error 13 then make use of the third party automatic repair tool.

7: Use MS Excel Repair Tool

It is recommended to make use of the MS Excel Repair Tool. This is the best tool to repair all sort of issues, corruption, errors in Excel workbooks. This tool allows to easily restore all corrupt excel file including the charts, worksheet properties cell comments, and other important data.

This is a unique tool to repair multiple excel files at one repair cycle and recovers the entire data in a preferred location. It is easy to use and compatible with both Windows as well as Mac operating systems.

Steps to Utilize MS Excel Repair Tool:

Final Verdict:

After reading the complete post you must have got enough idea on Visual Basic runtime error 13 type mismatch in Excel. Following the listed given fixes you are able to fix the Excel runtime file error 13.

I tried my best to provide ample information about the runtime error and possible workarounds that will help you to fix the Excel file error.

So, just make use of the solutions given and check whether the Excel error is fixed or not.

In case you have any additional workarounds that proved successful or questions concerning the ones presented, do tell us in the comments.

Steps to Resolve Excel Runtime Error 13

Encountering error with Excel application that you use every day, whether frequently or sometimes; at home or in office, is undoubtedly an unwanted situation. The trouble doubles when the error that you have encountered, is unknown or for the first time. Both Excel XLS and XLSX files become corrupt or damaged at times and may return different errors including runtime errors.

A runtime error that commonly affects MS Excel or its XLS/XLSX files other than Excel runtime 1004, 32809, 57121 error, etc. is Excel Runtime Error 13. Not knowing what to do when there is a time constraint to resolve the error, it is evident for you to get perplexed. This blog is an intent to help you resolve the terrible situation you are experiencing due to runtime error 13. Know all about the error: what is it, its causes and the fixes.

Excel Runtime Error 13

The VBA runtime file error 13 is a type of mismatch error in Excel. Usually, it arises when one or more files or processes are required to launch a program that employs the Visual Basic (VB) environment by default. This means the error occurs when Excel users try to run VBA code containing data types that are not matched in the correct manner. Consequently, ‘runtime error 13: type mismatch Excel’ appears in Excel.

Causes for Excel Runtime Error 13

The Excel runtime error 13 causes are as follows:

Damaged or incomplete installation of MS Excel application
The conflict between the Excel application and Operating System
When a missing menu function or a macro is clicked on by the user from Excel file
Virus/malware attack or malicious code infection damaging Excel files
Conflict with other programs while VBA Excel file is open

Fixes

The methods to fix Excel runtime error 13 are as follows:

Fix 1: Make use of the ‘Open and Repair’ utility

MS Excel automatically provides ‘File Recovery’ mode when it discovers a damaged workbook or worksheet. It does this to repair the damaged Excel files. But there are times when Excel does not provide the ‘File Recovery’ mode automatically. This is the time when you can employ ‘Open and Repair’, an inbuilt utility to repair Excel files. The steps to use this utility are:

Open Excel application
Go to File->Open
Select the ‘Excel’ file
Click the ‘Open’ dropdown
Click ‘Open and Repair..’ button

Click ‘Repair’ button to recover as much work as possible Or Click ‘Extract Data’ tab to extract values and formulae

Fix 2: Uninstall the ‘error causing program’

It is found that some application and software cause the runtime error. Uninstall those application or software to fix the Excel file runtime error. To do so, the steps are:

Go to ‘Task Manager’ and stop the error causing programs one by one
Click ‘Start’ menu
Click ‘Control Panel’ button
Select ‘Add or Remove Program’ or “uninstall a program” option in Control Panel

All the installed programs on the PC is enlisted
Select MS Office and click ‘Uninstall’ to remove it from the PC

Limitations

Using Microsoft’s Open & Repair Utility and uninstalling error causing software-programs may or may not resolve Excel Runtime Error 13. In that case a sure-shot and reliable software helps in resolving the error.

Fix 3: Use Stellar Repair for Excel

A professional Excel file repair software that successfully repairs damaged Excel .XLS and .XLSX file without hassle. Recovers all important Excel file components: table, chart, chartsheet, formula, cell comment, image, sort, filter, etc. without data loss or change in the structure or formatting of the files. With user-friendly and intuitive interface having easily accessible tabs, buttons, and menus, the Excel repair process is easy and saves time.

About The Author

Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software’s. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.

Best Selling Products

Stellar Repair for Excel

Stellar Repair for Excel software provid

Stellar Toolkit for File Repair

Microsoft office file repair toolkit to

Stellar Repair for QuickBooks ® Software

The most advanced tool to repair severel

Stellar Repair for Access

Powerful tool, widely trusted by users &

9 comments

Yesterday, I tried to access MS Excel 2016 file but it displays “Excel Runtime Error 13”. To resolve this error I tried manual methods like Open & Repair Utility, Uninstall the error causing program and also used 3rd party free software’s.

But still, I am getting error repeatedly.

Have you tried free demo of Stellar Repair for Excel ?

Its free demo facilitates preview of recoverable Excel data. Then, you would be able to get a better decision.

I have a runtime error 13 type mismatch error on every excel sheet i am trying to use. I just migrated from Windows 10 to a IMAC with El Capitan OSX. I used a easy Bill excel sheet to make bills for my customers, and a bigger sheet for making my daily in and out of cash. The Sheets are working without any problems on windows. Then sent it to the Mac again, but the same problem still occurs.

Has anyone a suggestion?

This problem is associated with an error or damage in an Excel file. So, you can go for professional Excel file repair software.

I keep on receiving the “Runtime Error 13: Type Mismatch whenever I try to use the reshaping tool.

I am happy now with the performance of Excel.

You can share your testimonial with us.

Yesterday, my newly created macro was working fine, but today I always receive the error message:

“Excel VBA Run-time error ’13’ Type mismatch”

How to fix this error?

When inserting / deleting row / rows, get error “Type Mismatch error 13” in Excel. How I can sort out this issue.

Источник

Home > VBA > VBA Type Mismatch Error (Error 13)

Type Mismatch (Error 13) occurs when you try to specify a value to a variable that doesn’t match with its data type. In VBA, when you declare a variable you need to define its data type, and when you specify a value that is different from that data type you get the type mismatch error 13.

In this tutorial, we will see what the possible situations are where runtime error 13 can occurs while executing a code.

Type Mismatch Error with Date

In VBA, there is a specific data type to deal with dates and sometimes it happens when you using a variable to store a date and somehow the value you specify is different.

In the following code, I have declared a variable as the date and then I have specified the value from cell A1 where I am supposed to have a date only. But as you can see the date that I have in cell one is not in the correct format VBA is not able to identify it as a date.

Sub myMacro()
Dim iVal As Date
iVal = Range("A1").Value
End Sub

Type Mismatch Error with Number

You’re gonna have you can have the same error while dealing with numbers where you get a different value when you trying to specify a number to a variable.

In the following example, you have an error in cell A1 which is supposed to be a numeric value. So when you run the code, VBA shows you the runtime 13 error because it cannot identify the value as a number.

Sub myMacro()
Dim iNum As Long
iNum = Range("A6").Value
End Sub

Runtime Error 6 Overflow

In VBA, there are multiple data types to deal with numbers and each of these data types has a range of numbers that you can assign to it. But there is a problem when you specify a number that is out of the range of the data type.

In that case, we will show you runtime error 6 overflow which indicates you need to change the data type and the number you have specified is out of the range.

Other Situations When it Can Occurs

There might be some other situations in which you could face the runtime error 14: Type Mismatch.

When you assign a range to an array but that range only consists of a single cell.
When you define a variable as an object but by writing the code you specify a different object to that variable.
When you specify a variable as a worksheet but use sheets collection in the code or vice versa.

How to Fix Type Mismatch (Error 13)

The best way to deal with this error is to use to go to the statement to run a specific line of code or show a message box to the user when the error occurs. But you can also check the court step by step before executing it. For this, you need to use VBA’s debug tool, or you can also use the shortcut key F8.

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 Doesn’t Support this Property or Method Error (Error 438)
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)

Источник