VBA Type Mismatch Error

What Is VBA Type Mismatch Error?

VBA Type Mismatch error is also denoted as Run-time error-13. This type of error usually occurs when we assign a value between different data types. To avoid the VBA Type Mismatch error-13, we must understand the type of value a data type/variable is designed for and then assign its respective value. Let us see an example that will demonstrate the error.

We know that we can only assign numeric values to an integer. However, if we assign it a non-numeric value or a text using the VBA code and run it, we can observe a Run-Time Error code 13, i.e., Type Mismatch. Open VBA in Excel and click on the “Insert” tab and choose the option “Module.” You have to type the following code in it and run the code using F5. You will observe the result as shown below:

Code:

Sub TypeMismatch_Ex()
Dim X As Integer
X= “Thank You”
MsgBox X
End Sub

VBA Type Mismatch Error - Intro

We must use only numeric values for the integer data type to avoid this error.

Key Takeaways
  • VBA Type Mismatch error is the 13th type of VBA error in Excel.
  • The error usually occurs when we assign a value to a variable of a different data type than what it is declared with.
  • To avoid VBA Type Mismatch error 13, we need to understand the different data types available in VBA and their permissible values.

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

Let us look at the following steps to fix VBA Type Mismatch Run-time Error 13.

Step 1: Open VBA in Excel and click the “Insert” tab. Here, choose the option “Module.” It will open a new module, as shown in the image below.

VBA Type Mismatch Error - fix - Step 1

Step 2: Write the code starting with the sub-procedure and define a variable “XY” as an integer data type.

Code:

VBA Type Mismatch Error - fix - Step 2

Step 3: If we assign a text value instead of a numeric one to the variable “XY,” this error occurs. To observe the error, define a MsgBox for the XY variable.

VBA Type Mismatch Error - fix - Step 3

Step 4: Run the above code using the Run button shown in the VBA Ribbon or the F5 key. The result will be displayed in the message box.

VBA Type Mismatch Error - fix - Step 4

Step 5: To fix this error, we must use a numeric value like 200 instead of text for the integer data type.

Code:

Sub TypeMismatch_Ex()
Dim XY As Integer
XY= “200”
MsgBox XY
End Sub

VBA Type Mismatch Error - fix - Step 5 - code.jpg

Step 6: Run the code and the Type Mismatch error disappears. You will see the output as shown below:

VBA Type Mismatch Error - fix - Step 6

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

For a detailed understanding, let us see more examples of the VBA Type Mismatch error 13.

Example #1

In this example, let us use a “Boolean” data type. As we all know, the Boolean data type has either of two values, TRUE and FALSE. Thus, if we provide any other value to the variable, it should show a VBA Type Mismatch error 13.

Let us observe the following steps.

Step 1: Write the code starting with a sub-procedure and define variables X and Y as Boolean data types. We know that it only allows us to assign True or False values to it.

Example 1 - Step 1

Step 2: Now, if we assign a numeric value instead of True or False to the variables X and Y, a VBA Run time error will occur. We add a MsgBox to show the output.

Code:

Sub TypeMismatch_Ex()
Dim X As Boolean
Dim Y As Boolean
X = 9876
Y = 0
MsgBox X & Y
End Sub

Example 1 - Step 2

Step 3: Run the above code using F5 or the Run button in the VBA Ribbon and check the result in the message box as shown below:

VBA Type Mismatch Error - Example 1 - Step 3

Surprisingly, the result does not show a Type Mismatch error! Instead, it shows TrueFalse. It is because Excel considers all numeric values as True, and zero, as False. Thus, we obtain the result TrueFalse because we are printing both X and Y in the message box instead of the Type Mismatch error.

Example #2

Let us look at another data type named “Byte.” For byte, you can include a value from 0 to 255. Therefore, if a Byte variable is assigned any other number apart from these, it will show a VBA Run-time error 13.

Step 1: Write the code and define a variable “X” as a Byte data type.

Example 2 - Step 1

Step 2: If we assign a text value instead of numeric value to the variable X, a VBA Run-time error occurs. Define a MsgBox to print the value of the X variable.

VBA Type Mismatch Error - Example 2 - Step 2

Step 3: Run the above code using the F5 key in the VBA Ribbon. Check the result in the box shown below:

VBA Type Mismatch Error - Example 2 - Step 3

We get the Type Mismatch error by assigning a String to a Byte variable.

Code:

Sub TypeMismatch_Ex()
Dim X As Byte
X = “Name”
MsgBox X
End Sub

Example #3

Let us see another example of an integer data type. Here we will try adding two data types using a VBA mathematical operator.

Step 1: Write the code starting with the sub-procedure and define variables X and Y of integer data types.

VBA Type Mismatch Error - Example 3 - Step 1

Step 2: Now, we assign a numeric value to the variable X and a non-numeric value to Y and use the addition VBA operator.

Example 3 - Step 2

Step 3: To see the result,run the above code using the Run option in the VBA Ribbon.

Example 3 - Step 3

When adding two different data types, you get a VBA Type Mismatch error 13. To avoid this, we should assign a numeric value to both variables, and it will show their sum in the result.

Example 3 - Step 3 - Output

Important Things To Note

  • We should understand the VBA data types in Excel and use only those values compatible with the data type with which the variable is declared.
  • If we use a value belonging to the declared data type, the VBA Run-time error 13 can be avoided.
  • VBA Type Mismatch error handling can be done using the appropriate variable value that a data type can hold. If that does not work, we can use VBA’s debug tool or F8 key.
  • Other than VBA Run time error 13 Type Mismatch, VBA Type Mismatch error 2042 can also be encountered. This error is caused when we don’t set an “Object Variable .” This error occurs when we try to use an object variable that has not been assigned an object or a value.

Frequently Asked Questions (FAQs)

1. What is error code 13 type mismatch in VBA?

Excel VBA Run-time error 13 is also known as a Type Mismatch error. This type of error occurs when we assign a value to a variable that is not its data type. For example, an integer data type can be assigned only numeric values. Therefore, if we provide a value other than a numeric value, it will show a VBA Run-time error 13 in Excel.

2. How do I fix Runtime error 13 type mismatch in Excel VBA?

To fix VBA Run-time error 13 in Excel, follow the below steps:

Step 1: Write a sub-procedure in VBA.

Step 2: Check the variable value. Reassign it with a value that fits the declared data type and run the code.

Step 3: Now, we can observe the desired result without the error.
We should also check the code stepwise before executing it. Furthermore, we can use the VBA debug tool or the key F8.

We should also check the code stepwise before executing it. Furthermore, we can use the VBA debug tool or the key F8.

3. How to avoid type mismatch errors in Excel VBA?

To avoid Type Mismatch errors in Excel VBA, ensure the variable value should be matched to its data type.

For example: If we consider a data type as Integer. So, if we assign a value to a variable other than a numeric value, say, a text value “Cricket,” and run the VBA code, it will show a Type Mismatch Run-time error 13. To avoid this error, we should assign a value of type integer. The result will display the value assigned.

Guide to VBA Type Mismatch Error. Here we explain why runtime error 13 occurs and how to fix them in excel VBA with examples. You may learn more from the following articles –

Reader Interactions

Leave a Reply

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

CHATGPT & AI FOR MICROSOFT EXCEL COURSE - Today Only: 60% + 20% OFF! 🚀

X