VBA Type Mismatch

Excel VBA Type Mismatch

In this article, we will see an outline on Excel VBA Type Mismatch. This is the most usual thing that we all have faced while working on VBA Macro. Sometimes, when we create a macro, due to the selection of incorrect data types or values assignment, we end up getting the error as Type Mismatch. Such kind of error mostly happens at the time of variable assignment and declaration. VBA Type Mismatch gives the “Run Time Error” message with the error code 13. To avoid such errors it is advised to assign the variables properly with proper selection of data types and objects. Also, we need to understand each data type with the type of values it can hold.

How to Fix Type Mismatch Error in VBA?

We will learn how to fix Type Mismatch Error in Excel by using the VBA Code.

Example #1 – VBA Type Mismatch

To demonstrate the type mismatch error, we need to open a module. For this, follow the below steps:

Step 1: We will go to the Insert menu tab and select the Module from there.

Step 2: Now write the subprocedure for VBA Type mismatch as shown below. We can choose any name here to define the subprocedure.

Code:

Sub VBA_TypeMismatch()

End Sub

Step 3: Now we will define a variable let say “A” as an Integer data type.

Code:

Sub VBA_TypeMismatch()

Dim A As Integer

End Sub

Step 4: As we all know, Integer data type only stores numbers and that to Whole Numbers. But, just to demonstrate here we will be assigning a text value to variable A.

Code:

Sub VBA_TypeMismatch()

Dim A As Integer
A = "Ten"

End Sub

Step 5: And to see the values stored in variable A we will use the Message box.

Code:

Sub VBA_TypeMismatch()

Dim A As Integer
A = "Ten"
MsgBox A

End Sub

Step 6: Now run the code by pressing the F5 key or by clicking on the Play Button. As we can see, we really got the error message as “Run-Time Error ‘13’” as shown below with the additional message as “Type Mismatch”.

As we already know that Integer can only store whole numbers. So giving it a text will definitely show the error. If we consider the same code and compiled it before we run it, we would have got this error message earlier also. For directly compiling the code, press the F8 function key.

Step 7: If we assign the correct value incorrect format to the variable we define, we will get the proper output.

Code:

Sub VBA_TypeMismatch()

Dim A As Integer
A = 10
MsgBox A

End Sub

Step 8: Run the code by pressing the F5 key or by clicking on the Play Button. We will get the message as 10 which we assigned in variable A.

Example #2 – VBA Type Mismatch

Let’s see another example of Type Mismatch. For this, follow the below steps:

Step 1: Write the subprocedure for VBA Type Mismatch.

Code:

Sub VBA_TypeMismatch2()

End Sub

Step 2: Again assign a new variable, let’s say “A” as Byte data type.

Code:

Sub VBA_TypeMismatch2()

Dim A As Byte

End Sub

Let’s understand the Byte Data type here. Byte can only store the numerical value from 0 to 255. And it doesn’t consider any negative value.

Step 3: Now let’s assign any value other than a number. Here we have considered the text “TEN”.

Code:

Sub VBA_TypeMismatch2()

Dim A As Byte
A = "Ten"

End Sub

Step 4: And then we will message box for output.

Code:

Sub VBA_TypeMismatch2()

Dim A As Byte
A = "Ten"
MsgBox A

End Sub

Step 5: Run the code by pressing the F5 key or by clicking on the Play Button. And we got the error message again. The message is the same as we got in example-1.

Step 6: As we have entered value in incorrect format, so the error message we got as “Type Mismatch”. What if we entered a value that is greater than 255? Let’s consider 1000 here.

Code:

Sub VBA_TypeMismatch2()

Dim A As Byte
A = 1000
MsgBox A

End Sub

Step 7: This time we got the error as “Run-Time Error ‘6’” overflow. Which means we have entered the value beyond the allow capacity of selected data type.

Example #3 – VBA Type Mismatch

Let’s see another example. Here we will try to 2 data types and use them as any mathematical operation. For this, follow the below steps:

Step 1: Write the subprocedure for VBA Type Mismatch.

Code:

Sub VBA_TypeMismatch3()

End Sub

Step 2: Now let’s consider 2 variables A and B as Integer.

Code:

Sub VBA_TypeMismatch3()

Dim A As Integer
Dim B As Integer

End Sub

Step 3: As we all have seen in the previous example, Integer only allows numbers as a whole. So we will be assigning one numeric value to one of the integers and assign any text to another variable as shown below.

Code:

Sub VBA_TypeMismatch3()

Dim A As Integer
Dim B As Integer
A = 10
B = "Ten"

End Sub

Step 4: Let’s multiply the above variables here in the message box.

Code:

Sub VBA_TypeMismatch3()

Dim A As Integer
Dim B As Integer
A = 10
B = "Ten"
MsgBox A * B

End Sub

Step 5: After running the code, we will get a message box with the error message “Run-time error ’13’”. It is because we have used one text to variable B and then multiplied A with B.

Step 6: And if we change the data type from Integer to Long. And also change the format of values.

Code:

Sub VBA_TypeMismatch4()

Dim A As Long
Dim B As Long
A = 10
B = "10"
MsgBox A * B

End Sub

Step 7: If Run the code by pressing the F5 key or by clicking on the Play Button, this code will be successfully executed. Even if we have kept the value 10 in inverted colons in variable B.

Pros of VBA Type Mismatch:

• We actually get to know the mistake where it happened.
• Error message is so to the point, that even if we do not compile the code, we will get the point of error in the code.

Things to Remember

• Even if there is a small bracket where we considered a slightly different value, we will definitely get Type Mismatch Error.
• Understand the type of data types we are going to use and the values permitted in those data types. This will allow us to avoid such silly errors and run the code successfully.
• All the basic data types have some constraint of input values. It is better to choose those data types which don’t give such error as the wide range of input such as String, Long, Variant mainly. The rest of the data types have some limitations.
• Once you are done with coding, it is better to save the code in a Macro Enabled Excel format.

Recommended Articles

This is a guide to VBA Type Mismatch. Here we discuss how to Fix Type Mismatch Error in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA DateDiff
2. VBA Square Root
3. VBA SendKeys
4. VBA Name Worksheet