How to Fix Excel Runtime Error 13 Type Mismatch in VBA

In this article, we are going to discuss how to fix Excel Runtime Error 13 Type Mismatch in VBA. This is one of the most common types of errors while using VBA. This occurs if the data type that is given does not match with the data type that the system is expecting. However, this is not the case for every scenario. Sometimes while debugging, the reason behind this error becomes very tricky to find. So, in this article we will cover in detail all the possible reasons behind this error and give a solution to that.

How to Launch 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. Follow the simple steps below to open up the VBA Macro Editor.

Go to Developer Tab >> Visual Basic.
This will open the Visual Basic window.

Select Insert >> Module in the macro editor.

As a result, 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

Today, we are going to discuss 8 possible reasons behind Type Mismatch error in Excel VBA and come up with a solution to each cause. The reasons will include the obvious ones like assigning a string value to a numeric variable as well as some subtle ones like passing in an object to a command instead of a value or property. However, in all the cases VBA will show an error and after pressing Debug, the portion with the error will be highlighted from the entire code.

Reason 1: Setting String Value to Number Type Variable

The reason for the error is the most common one. If we assign a string value to a numeric data type variable, VBA cannot convert that string value to a numeric value and thus the error occurs.

Here, as soon as we run the code, the error prompt showing the data type mismatch error will appear on the screen. Next, we will press Debug to find the line where the error occurred.

As seen from the image above, 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 Numeric Value to Number Type Variable

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 arithmetic operators work on numeric values only. Sometimes, however, the addition operator concatenates two strings. But, this operator can not concatenate a string and numeric value and thus resulting in a type mismatch error.

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. Now, if we press the Debug option, we will get to the cause of the error.

As we can see, the code finds the line where we are concatenating the First Name and ID to be the source of the error. Because, here, the ID numbers are in numeric form. The addition operator cannot combine strings with a numeric value and hence the error occurs.

Solution: Using Proper Data Type while Using Operators

In order to fix the error, we will convert the ID numbers into string values by adding a string after each number. In this way, VBA will assume them as strings. Thus, the code will concatenate the strings by using the addition operator and we will get the Email ID of the employees.

Reason 3: Inserting Wrong Data Type into VBA Range

Often, we need to read data from the worksheet while executing a VBA code. If the datasheet contains data types that the code is not expecting then it will show the Runtime Error 13: Type Mismatch error.

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 executes for a while only to show an error message afterward. Now, we click on Debug to find the reason behind the error.

As we can see, the line where we are multiplying each unit price with the sales is the source of error. This is because in the C8 cell, the unit price is in text format and VBA cannot perform a multiplication operation between a string and a number. So, the error occurred.

Solution: Inserting Proper Data into Dataset

In this case, we changed the value in the C8 cell to a numeric value and thus the code executed perfectly.

Reason 4: Passing in Wrong Type Argument 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.

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.

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 Argument

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

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

Sometimes the mismatch error can be a bit tricky and hard to understand. This example is one such case. 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.

Example1: Mismatch Error for Range Object

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

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

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. We will press the Debug option to know the source of the error.

Here too, 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 Value or Property Instead of Object

In this case, 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 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 Date in Invalid Format

Often users write dates in invalid formats that the VBA compiler cannot understand or the date does not exist. These result in Runtime error 13 type mismatch in VBA.

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.

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

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 hence the error.

Solution: Passing in Date in a Valid Format

So, we fixed the date format and the code works perfectly.

Reason 7: Trying to Convert Incompatible Data Types

Sometimes users try to convert a value of a particular data type to another data type that is incompatible with the previous one. For example, if we try to convert “Adam” to a numeric value, VBA will show the type mismatch error.

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

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

Solution: Converting Data That Are Compatible

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. So, we changed the ID to integers and the code ran just fine.

Reason 8: Invalid Array Conversion from 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.

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

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 Proper Array from Range

So, we fixed the code by adding a range of values to the Arr variable making it an array. Now, 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 Practice Workbook

You can download the practice book here.

Runtime Error 13 Type Mismatch.xlsm

Conclusion

In this article, we discussed in length the possible causes and solutions of the Excel RunTime Error 13: Type Mismatch error in VBA. This article will allow users to understand the error properly and take necessary actions when the error occurs. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.

Get FREE Advanced Excel Exercises with Solutions!