VBA Type Mismatch Error

What is a Type Mismatch Error?

VBA Type Mismatch Error is a run time error in excel, which often occurs when the data types contained in a VBA code are not matched in the correct order. This error is also known as run time error 13 or the number 13 error in the error’s category.

In VBA, whenever this error occurs, a dialog box pops up stating as "run-time error 13: Type mismatch".

Reasons for its occurrence

Below are some causes for which the Excel type mismatch error generally occurs:

1. The specified variable has been assigned to different data type and the data type is not matched in the correct order.
2. If your Ms Excel application has damaged or has been installed incompletely.
3. If there is any disagreement between the operating system and the Excel application
4. If a user clicks on a missing menu function or a macro in an Excel file.
5. If the malicious Virus/malware attack damages the Excel code
6. Conflict with other programs while VBA Excel file is open

Advantages

• The advantages of the Type mismatch error are as follows:
• It alerts the user about the positioning of the mistake where it occurred in the VBA code.
• The user gets to know about the point of error even before the compilation of the code.

Example

Code:

Sub TypeMisMatch_Example()
 'Declaring the variable
 Dim x As Byte
 'assigning a value to variable 'x'
 x = "Hello World"
 'using the message box to display the variable value
 MsgBox x
 End Sub 

Let’s us analyze step-step the above VBA code:

Step 1: Open the developer window by using the shortcut keywords Alt +F11.

Step 2: Create a module by right-clicking on the VBA Project-> Click on Insert-> Click on Module.

Step 3: In the Module window, introduce the sub-block, followed by your macro name.

Step 4: Declare your variable with the required data type. For instance, in the below reference, we have declared the variable ‘x’ as Byte.

Step 5: Next, we must assign the value for the variable ‘x’. Unlike, we have assigned the value for ‘x’ as “Hello World!”.

Step 6: the last step is to call the MsgBox so as we can store the value of x and can display variable content within it.

Output

Step 7: Execute the above code either by pressing the F5 shortcut key or by clicking on the Run button.

Step 8: You will notice that the Excel VBA will throw a runtime error dialogue box stating: run-time error 13: Type mismatch".

The reason for the above error is that the Byte data type cannot hold the string or text value (Byte x = “Hello Word”), so the VBA macro throws a Type Mismatch Error.

Debug

Step 9: In the Microsoft Visual Basic dialogue box, click on the Debug option.

Step 10: The VBA will highlight the code consisting of the mismatch error.

Step 11: Resolve it (with int variable x assign an integer value) and again run the program.

Step 12: The program will run successfully, displaying the message box.

Type Mismatch Example 2

Code:

Sub TypeMisMatch_Example2()
 'Declaring the variables
 Dim x As Byte
 Dim y As String
 Dim z As Integer
 'assigning the values to variables 'x' and 'y'
 x = 100 
 y = "Fifty"
 'adding both the variables
 z = x + y
 'using the message box to display the addition output
 MsgBox z
 End Sub 

The above code would also throw a mismatch error as we are adding values of different data types (byte x+ string y). Thus, violating the VBA data type correct manner.

Output

Debug

Debug and again run the code for error free output.