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

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

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.

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

Code:

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.

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.

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

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

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.

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

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:

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.

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.

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

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.

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.

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

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.

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)

Recommended Articles

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 –

• VBA Not Equal
• VBA String Functions
• VBA 1004 Error