VBA Type Mismatch Error

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 off the learnings 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 and we assign data types to it. 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”.

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

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

VBA Type Mismatch – Example #1

For an example look at the below VBA code.

Code:

Sub Type_MisMatch_Example1()

Dim k As Byte

k = "Hiii"

MsgBox k

End Sub

I have declared the variable “k” as Byte.

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

It is very clear that data type cannot hold the value of a text, so here comes the Type Mismatch Error.

VBA Type Mismatch – Example #2

Now take a 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 be wondering why this isn’t give run time error 13 of type mismatch error.

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

Now see I will assign a numerical value with text.

Code:

Sub Type_MisMatch_Example2()

Dim x As Boolean

x = "4556a"

MsgBox x

End Sub

This will definitely 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, I have added x + y.

But this isn’t the perfect code because we cannot add numbers with string texts. We will encounter Run Time Error 13.

VBA Type Mismatch – Example #4

Exceptional Cases

There are situations where excel forgives the wrong data assigned to the variable data type. For an 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” & “y”.

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

Long data type accepts only whole numbers, not decimal values.

So general perception is to get run time error 13 of type mismatch error.

But let’s see what happens when we run this code.

Wow!!! We got the values as 59 & 85.

This is because VBA will convert the decimal value 58.85 to the nearest integer value and even though numbers are enclosed with double quotes still it converts to the integer value only.

Recommended Articles

This has been a guide to VBA Type Mismatch. Here we discussed VBA Type Mismatch Run Time error 13 in VBA with examples and downloadable excel template. Below are some useful articles related to VBA –

• Data Type in Excel VBA
• VBA Type
• What is the Integer Data Type?
• VBA Sub Procedure
• VBA On Error Types
• VBA Set
• FIND in VBA
• How to Select Case in VBA?