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."

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

VBA Type Mismatch - Example #1

Look at the VBA code.

Code:

Sub Type_MisMatch_Example1()

Dim k As Byte

k = "Hiii"

MsgBox k

End Sub

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 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

We have declared the variable "x" as Boolean.

Boolean is a data type 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.

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

It will throw "Run-time error '13': Type mismatch."

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

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.' "

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

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

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.