How to Fix Excel Runtime Error 13 Type Mismatch in VBA

The Runtime Error 13 Type Mismatch in VBA is one of the most common types of errors while using VBA and occurs if the data type that is given does not match the data type that the system is expecting. However, this is not the case for every scenario. We’ll provide over a dozen of possible reasons behind the error and solutions.

runtime error 13 type mismatch vba


How to Launch the VBA Macro Editor in Excel

In order to run any VBA code, we first need to write or edit the code in the VBA Macro Editor.

Opening Visual Basic Window

  • Go to the Developer tab and then to Visual Basic. This will open the Visual Basic window.

Inserting VBA Module

  • Select Insert and Module in the macro editor.

VBA Module

  • An empty module will appear on the screen where you can write the code.

Runtime Error 13 Type Mismatch in VBA: 8 Possible Reasons with Solutions

If you press Debug in the VBA window, the portion with the error will be highlighted.


Reason 1 – Setting a String Value to a Number Type Variable

If you assign a string value to a numeric data type variable, VBA cannot convert it.

Runtime Error 13 for Setting String Value to Number Type Variable

  • The error prompt showing the data type mismatch error will appear on the screen.
  • Press Debug to find the line where the error occurred.

Debugging Code to find why runtime error 13 type error happened in vba

  • The error occurred when we assign the num variable which is an Integer type variable to a string value present in the C5 cell of the worksheet.

Solution – Assigning a Numeric Value to Number Type Variable

Assigning Proper Value to Numeric Variable to solve runtime error 13 type mismatch in vba

When we change the value in the C5 cell to a numeric value, as evident from the alignment of the data from the other values in that column, the code executes properly without any type mismatch error.


Reason 2 – Using Operators on Mismatching Data Types

The plus operator can’t concatenate a string and numeric value and results in a type mismatch error.

Using Operators on Mismatching Data Types

Here, we want to concatenate the First Name and ID of each employee with “@gmail.com” string with the addition operator. However, the code shows an error message. When you press the Debug option, you will get to the cause of the error.

Debugging VBA Code to Find the Error Source

The code finds the line where it’s trying to concatenate the First Name and ID with a string as the source of the error, because the ID numbers are in numeric form.

Solution – Using Proper Data Types while Using Operators

Changing Datatype to Perform Operations without showing runtime error 13 type in vba

  • Convert the ID numbers into string values.
  • The easiest way to do that is to add non-numeric characters in the cells. In this way, VBA will assume them as strings.
  • You can also convert the cell formatting.
  • The code will concatenate the strings by using the addition operator and we will get the Email ID of the employees.

Reason 3 – Inserting the Wrong Data Type into a VBA Range

If the datasheet contains data types that the code is not expecting, it will show the Runtime Error 13: Type Mismatch error.

Incorrect Datatype in Worksheet Data showing runtime error 13 type in vba

Here, we want to calculate the total revenue of each fruit by multiplying the unit price by the maximum sales of the fruit. However, as soon as we execute the code, the code shows an error message.

Debugging Code 

The line where we are multiplying each unit price with the sales is the source of error. In the C8 cell, the unit price is in text format and VBA cannot perform a multiplication operation between a string and a number.

Solution – Inserting Proper Data into the Dataset

Changing Worksheet Data to Resolve the Issue

Change the value in the C8 cell to a numeric value and the code executes.

Read More: [Fixed!] Runtime Error 438 in Excel VBA


Reason 4 – Passing a Wrong Argument Type to a Function

In VBA we can build user-defined functions and ask users to pass arguments to those functions to get an output. In those functions, we can define the data types of the arguments. However, if someone passes an argument to that function that is other than the documented data type, then the function will show a mismatch error.

Inserting Incorrect Argument to UDF showing runtime error 13 type mismatch in vba

In this example, we have created a simple function Multi that takes in two Double type numbers as its argument and multiplies it to show in a MsgBox. However, when we call the function in the Wrong_Argument subroutine, an error message pops up. We will Debug the code next.

Performing Debugging Operation

As seen from the image above, we passed “Jhon”, a string-type argument to that function which was expecting a numeric value.

Solution – Inserting Proper Arguments

Inserting Correct Argument to UDF

If we pass in two numeric values to that function, the function will work properly.


Reason 5 – Passing an Object Reference to a Procedure That Expects a Property or Value

The MsgBox property of VBA shows a value. So, it expects a value as its input. However, if someone passes an object as the input of a MsgBox, a Runtime Error 13: Type Mismatch message will show up on the screen.


Example 1 – Mismatch Error for Range Object

Passing Range Object Instead of Values

In this case, we set myRange as a Range type variable and assigned the range B4:D12 as its value. When we tried to output the range, an error message appeared on the screen.

Debugging VBA Code for Error

The MsgBox was expecting a value from that range instead we passed the entire range as the input of the MsgBox. So, we got a mismatch error.


Example 2 – Mismatch Error for Array Object

Inserting Array Object in Place of Value

In this example, we took the B4:D12 range inside an array named Arr. When we tried to show the Arr array in a MsgBox, the Type Mismatch error occurred.

Performing Debugging Operation

We passed the entire array object instead of one of the values from the array as the input of the MsgBox thus resulting in an error.

Solution – Passing in a Value or Property Instead of Object

Passing in Value to Resolve the Issue

We wrote myRange.Cells(2,1) as the input of the MsgBox which indicates the value from the 2nd row and 1st column of the myRange variable. So, the MsgBox displays Apple as the output without showing any errors.

Entering Array Value to Get Rid of the Error

In the second case, we passed Arr(2,1) as the input of the MsgBox. This refers to the value from the 2nd row and 1st column of the Arr array which is Apple. Thus, we see it as the output message.


Reason 6 – Writing a Date in an Invalid Format

Writing dates in invalid formats that the VBA compiler can’t understand, or invalid dates that are formatted as text, results in Runtime error 13 type mismatch in VBA.

Prompting Users to Insert Employee Name

In this instance, the code prompts users to write an employee name from the dataset, and in return, it will show the joining date of the employee. So, we wrote Martha and pressed OK.

Type Mismatch Error Due to Invalid Date

The code showed a Type Mismatch error. We clicked on Debug to know the error source.

Debuuging VBA Code

As seen from the image, the line where we assigned the joining date from the Joining Date column as the value of the dt variable is the source of the error. Because dt is a Date type variable and it expects a valid date as its value. However, the date pertaining to the employee Martha is 14/14/2023 which is an invalid date and creates the error.

Solution – Passing the Date in a Valid Format

Correcting Date Format to Resolve the runtime error 13 type mismatch in vba

We fixed the date, and the code worked perfectly.


Reason 7 – Trying to Convert Incompatible Data Types

If we try to convert “Adam” to a numeric value, VBA will show the type mismatch error.

Error Due to Invalid Conversion

Here, we want to show the ID of each employee in a MsgBox. But when we ran the code, it showed an error.

Executing Debugging Operation

As the image suggests, the line inside the For Loop is the source of the error.

Solution – Converting Data Types That Are Compatible

Converting Compatible Data Type to solve runtime error 13 type mismatch in vba

The ID of the employees was in string format. However, in the code inside the For Loop we tried to convert them into an integer with the Int(ID.Cells(i,1) command. Thus, we ran into an error. We changed the IDs to integers and the code ran just fine.

Read More: [Fixed!] Excel VBA Run Time Error 1004


Reason 8 – Invalid Array Conversion from a Range

In this instance, we will deal with another tricky Type Mismatch error. This happens when the conversion of a range into an array goes wrong.

runtime 13 type mismatch error Due to Incorrect Convertion From Range to Array in VBA

We assigned the Range(“B4”) to an array named Arr and tried to display the first element of the array in a MsgBox. As soon as we hit the run option, the error message popped up.

Debugging VBA Code

While debugging the code, we can see that the output code of the MsgBox is the source of the error. It is because the range has only one value B4 and so VBA assumed  Arr as a single variable instead of an array. So, when we tried to display the first element of the array by writing Arr(1,1), the mismatch error occurred as Arr never became an array.

Solution – Creating a Proper Array from a Range

Setting Proper Range as the Array Value to solve runtime error 13 type mismatch in vba

We fixed the code by adding a range of values to the Arr variable, making it an array. When we tried to show the first element of the array, the code displayed it without an error.


Frequently Asked Questions

How do I fix macro errors in Excel?

To fix macro errors in Excel, follow these general steps:

  • Determine the error message: When you receive an error message while running a macro, read the error message and try to understand what it’s telling you. Understanding the error message is the first step in fixing the problem.
  • Debug your code: Use the debugging tools in the Visual Basic Editor to step through your code line-by-line to identify where the error occurs. You can use the “Debug” menu to run your macro in “Step Into” mode, which allows you to step through each line of code.
  • Check your code for syntax errors: Review your code to check for syntax errors, such as missing or incorrect punctuation, or incorrect variable or function names. Fix any syntax errors that you find.
  • Check for logical errors: If your code appears to be syntactically correct, but is still causing errors, check for logical errors. These are errors in the way your code is structured that may cause it to behave incorrectly. Review your code and make any necessary adjustments.
  • Use error handling: You can use error handling to catch and handle errors that occur during the execution of your macro. This allows you to gracefully handle errors and provide useful feedback to the user.
  • Test your code: Once you have fixed any errors in your code, test your macro to ensure that it works as expected.

Things to Remember

  • In all the methods above, we could add Error Handling to get rid of the error. In VBA, you can use error handling to catch and handle errors that occur during the execution of your code. The syntax for error handling in VBA is as follows:
On Error GoTo [label] 
OR
On Error  Resume Next
  • The On Error statement sets the error trapping mode. There are two modes:
    • GoTo [label]: This mode allows you to jump to a specified label in the code when an error occurs. The label should be defined using the “Label:” syntax.
    • Resume Next: This mode tells VBA to continue executing the code from the next line when an error occurs.

Download the Practice Workbook


Related Article:

Get FREE Advanced Excel Exercises with Solutions!
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo