VBA Type Mismatch Error

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Type mismatch error, or we can also call it Error code 13, occurs when we assign a value to a variable that is not of its data type. For example, if we provide a decimal or long value to an Integer data type variable, we will encounter this Type mismatch error when we run the code, which shows as error code 13.

What is VBA Type Mismatch Error?

VBA Type Mismatch Error in excel is a type of “Run Time Error,” and it is the number 13 error in this category.

To start learning in VBA and for beginners, it is hard to find the error thrown by the VBA codes. Remember, VBA is not throwing an error. Rather, it is just highlighting our mistakes while writing the code.

We usually declare variables. We assign data types to them. When we assign a value to those variables, we need to remember what kind of data it can hold. If the assigned value is not as per the data type, we will get “Run-time error ’13’: Type mismatch.”

VBA Type Mismatch Error

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Type Mismatch Error (wallstreetmojo.com)

How to Fix VBA Type Mismatch Run-time Error 13?

Let us see some examples to understand this VBA Type Mismatch Error.

You can download this VBA Type Mismatch Excel Template here – VBA Type Mismatch Excel Template

VBA Type Mismatch – Example #1

Look at the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Code:

Sub Type_MisMatch_Example1()

Dim k As Byte

k = "Hiii"

MsgBox k

End Sub
vba mismatch example 1.1

We have declared the variable “k” as Byte.

The variable “k” can hold values from 0 to 255. But in the next line, we have assigned the value for the variable “k” as “Hiii.”

The data type cannot hold a text’s value, so the Type Mismatch Error comes.

vba mismatch example 1.2

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

VBA Type Mismatch – Example #2

Now, look at one more example with a different data type. Look at the below code.

Code:

Sub Type_MisMatch_Example2()

Dim x As Boolean

x = 4556

MsgBox x

End Sub
vba mismatch example 2.1

We have declared the variable “x” as Boolean.

Boolean is a data typeBoolean Is A Data TypeBoolean is an inbuilt data type in VBA used for logical references or logical variables. The value this data type holds is either TRUE or FALSE and is used for logical comparison. The declaration of this data type is similar to all the other data types.read more that can hold the value of either TRUE or FALSE.

In the above code, we have assigned a value of 4556, which is not as per the data type values of TRUE or FALSE.

When we run this code, you would expect a type mismatch error, but see what happens when we run this code.

vba mismatch example 2.2

You must wonder why this is not “Run-time error ’13’ ” or a type mismatch.

The reason for this is excel treats all the numbers as TRUE except zero. So, zero value will be treated as FALSE. So that is why we got the result as TRUE instead of an error.

Now see, we will assign a numerical value with text.

Code:

Sub Type_MisMatch_Example2()

Dim x As Boolean

x = "4556a"

MsgBox x

End Sub
vba mismatch example 2.3

It will throw “Run-time error ’13’: Type mismatch.”

vba mismatch example 2.4

VBA Type Mismatch – Example #3

Now, look at the below code for this example.

Code:

Sub Type_MisMatch_Example4()

Dim x As Integer
Dim y As String

x = 45
y = "2019 Jan"

MsgBox x + y

End Sub
example 3.1

Variable “x” is an Integer data type, and “y” is a String data type.

X = 45 and y = 2019 Jan.

In the message box, we have added x + y.

But this is not the perfect code because we cannot add numbers with string texts. As a result, we will encounter “Run-time error ’13.’ “

example 3.2

VBA Type Mismatch – Example #4

Exceptional Cases

There are situations where excel forgives the erroneous data assigned to the variable data type. For example, look at the below code.

Code:

Sub Type_MisMatch_Example3()

Dim x As Long
Dim y As Long

x = 58.85

y = "85"

MsgBox x & vbNewLine & y

End Sub
example 4.1

Two declared variables are “x” and “y.”

For this variable, the assigned data type is “Long.”

The long data type accepts only whole numbers, not decimal values.

So, the general perception is to get “Run-time error 13′ ” of type mismatch error.

But let us see what happens when we run this code

example 4.2

We got the values 59 and 85.

VBA will convert the decimal value 58.85 to the nearest integer value. Even though numbers enclosed with double quotes, it only converts to the integer value.

Recommended Articles

This article has been a guide to VBA Type Mismatch. Here, we discussed VBA Type Mismatch “Run-time error ’13’ ‘ in VBA with examples and a downloadable Excel template. Below are some useful articles related to VBA: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *