Run time Error 13 type mismatch in Excel Visual Basic : Fix

by

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.

 

featured image min

 

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.

 

visual basic min

 

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.

[Example –

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

 

]

 

first one min

 

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.

 

sub in that min

 

Step 4 – After this, click the “Debug” tool and choose the Step operations (like Step into, Step Over, Step Out) to debug the code.

 

debug min

 

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.

Example –

Sub Excel_MisMatch
Dim k As Integer
Dim q As String
k = "1"
q="TGP"
MsgBox k*q
End Sub

 

last one min

 

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.



Read More: