While executing the Visual Basic codes in an Excel spreadsheet, you may have faced this “Run time Error 13. Type mismatch” prompt. Visual Basic Application module helps you to enhance your worksheet. But, just like other codes, Visual Basic codes can also get runtime errors and it is pretty normal. Follow these sets of solutions on your computer to fix this Visual Basic Error on your system.
Fix 1 – Check the value type
The lion’s share of the users who encounter this Type Mismatch error gets that due to the wrong type of value declared in the code.
Step 1 – While you are in that Excel file, go to the “Developer” tab.
Step 2 – Later, click the “Visual Basic” to load the visual basic code for the worksheet.
Or, you can tap the “Debug” button in the Runtime Error prompt.
Step 3 – As the Visual Basic Application opens up, check the code clearly on the right-hand pane.
Step 4 – Check the declared variable types in the code.
Sub Excel_MisMatch Dim k As Integer k = "TGP" MsgBox k End Sub
As you can see the k is declared as an integer, here. The Integer value takes any value within the range of -2,147,483,647 to 2,147,483,647. But, in the next line of the code, k is “TGP”, which is the wrong type of data.
Tweaking the code just a bit should fix this –
Sub Excel_MisMatch Dim k As Integer k = "1" MsgBox k End Sub
After this, save the changes and run the VB code once more. This time, it will be executed without any runtime errors.
Fix 2 – Tweak the input type
The second most prominent reason is the wrong input at executing the Visual Basic code.
You have to ensure that the users of the worksheet does enter the correct format/type of data as input. Like, if you have designed the code snippet like this –
Sub Excel_MisMatch Dim k As String Dim q As Integer k = InputBox("Enter the credentials") q = InputBox("Enter the age")
Now, you have to make sure the user doesn’t input any letter for the q variable as it an integer (can’t accept characeters as input).
Fix 3 – Use VBA Debug tools
There is a dedicated VBA Debug tool that you can use to debug the code in there.
Step 1 – In Microsoft Excel, press the Win+F11 keys at once.
Step 2 – As the Visual Basic module loads up, you will see the code snippet on the right-hand pane.
Step 3 – Next, click once before the Sub key.
Step 4 – After this, click the “Debug” tool and choose the Step operations (like Step into, Step Over, Step Out) to debug the code.
Additionally, you can use a sub procedure to debug the functions as well.
Step 5 – Repeat the same thing for all the lines of code.
Complie the visual basic code from the terminal and test it.
You won’t get into the type mismatch error in Visual Basic panel.
Fix 4 – Decalre the operations correctly
Another possible reason why this type of mismatch error is coming up is due to misconfigured operations used in the code. While coding, you can not operate on two other data types at the same time.
Sub Excel_MisMatch Dim k As Integer Dim q As String k = "1" q="TGP" MsgBox k*q End Sub
As the variables are k, q are two distinct data types (k is integer, q is the string), so you can’t use a operator function on them. So, check the Visual Basic code and eliminate any such discussed occurences.