What is ‘Run-time error ‘13’: Type mismatch’? And How Do You Fix It?

What exactly is Run-time error ‘13’: Type mismatch? For starters, a Run-time error is the type of error occurring during the execution of the code. It simply causes the subroutine to stop dead in its tracks.
The Run-time Error ‘13’ occurs when you attempt to run VBA code that contains data types that are not matched correctly. Thus the ‘Type Mismatch’ error description.

The simplest example of this error is when you attempt to add a number to a string. Any such attempt defies the logic that requires data types to match and interrupts further execution of the code in your macro.

Actually, you cannot make any kind of calculation with non-numeric data types. For example, you cannot add, subtract, divide or multiply a string data value in relation to a numeric type like Integer, Single, Double, or Long. However, there is one particular and simple arithmetic operator that could trip you up.

The plus sign (~ez_lsquo+ez_rsquo~) can actually be used to concatenate two string values, and not just to calculate the sum of number values. This can be the source of frustration and confusion within your code. We will look at some examples to point this out.

For more information about common errors involving string data, see this article.

EXAMPLE 1: DATA TYPES AND VARIABLES

But first, let’s look at a simple explanation of what types of operations will cause Run-time Error ‘13’: Type Mismatch. The most common operation that will throw this error is when you attempt to add a string value and a number.

In the following code, the macro is designed to find the last used row in a range. This is set to the variable, ‘myLastRow’. Then to find the next row after, we set the variable ‘x’ to a value of ‘1’ and add it to ‘myLastRow’. Also note that the variable ‘myLastRow’ has been dimensioned as an Integer data type.

But there is a slight problem with the variable ‘x’. It has been dimensioned as the string data type, and therefore, when we attempt to add ‘x’ to ‘myLastRow’ we get the Type Mismatch error.

Sub Mismatch1()</pre>
'Basic demonstration of how a Run-time Error ‘13’ Type mismatch is generated when the code attempts to add string (literal) data type to an Integer (numeric) datatype.
Set ws = ThisWorkbook.Sheets("Sheet1")
'Finds the last row of myRange
myLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
'Sets the variable 'x' to integer value of 1
x = 1
'x enclosed in double quotes is a string and this is a mismatch for this calculation since it is not a number data type
myNextRow = myLastRow + "x"
MsgBox myNextRow
End Sub

Notice that in debug mode, the source of our error is highlighted.

run-time-image-1

 

However, Excel is actually pretty savvy at dealing with this error. Even though we dimensioned ‘x’ as a string, if we remove the quotation marks from around ‘x’, Excel actually calculates the equation for the variable ‘myNextRow’ and completes the entire subroutine without error.

Sub Mismatch1()
'Basic demonstration of how a Run-time Error ‘13’ Type mismatch is generated when the code attempts to add string (literal) data type to an Integer (numeric) datatype.
Set ws = ThisWorkbook.Sheets("Sheet1")
'Finds the last row of myRange
myLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
'Sets the variable 'x' to integer value of 1
x = 1
'x enclosed in double quotes is a string and this is a mismatch for this calculation since it is not a number data type
myNextRow = myLastRow + x
MsgBox myNextRow
End Sub

EXAMPLE 2: CHANGING VARIABLE DATA TYPES

To look at this from a bit of a different angle, let’s consider a subroutine where we have dimensioned a variable as an integer but later attempt to set it equal to a string value.

Sub Mismatch2()
'Demonstrates Runtime Error 13 Type mismatch error due to variable y declared as Integer datatype but set to a string ("x")
Dim x As String
Dim y As String
Dim z As Integer
Dim myVal As Variant
Set ws = ThisWorkbook.Sheets("Sheet1")
x = "x"
ws.Cells(1, 2).Value = "x"
ws.Cells(1, 3).Value = x
y = ws.Cells(1, 2).Value
z = ws.Cells(1, 3).Value
myVal = y + z
ws.Cells(1, 4).Value = myVal
End Sub

In this subroutine, note the variable declarations carefully. We have dimensioned ‘x’ and ‘y’ as string types while dimensioning ‘z’ as an integer. We have also set the variable ‘x’ to a string value (‘x’ in double quotes) to illustrate the other point that sometimes string values can be literal and sometimes a variable.

Ultimately, this macro is designed to output the value of another variable, ‘myVal’, in cell D1 of the worksheet. In the body of the code itself, everything looks like it should work just fine. The variable myVal should be the result of the statement ‘y + z’, and since the variables ‘y’ and ‘z’ are set to equal a cell value (both being the string value ‘x’), this should work, right?

But if we step through the code by pressing ‘F8’, we soon get the infamous Run-time Error ‘13’ pop-up:

run-time-image-2

If we click ‘Debug’, the visual basic editor highlights our problem line.

run-time-image-3

But you say to yourself, “how does this line throw an error when the line before it processed just fine?". The error is in the line but it takes knowing what to look for to find it. Remember, we are dealing with variables here, so the causes of these errors need to be traced back to the origin of the variables themselves.

Knowing what we have already discussed, what could possibly be causing a type mismatch error when the values of the cells that we are setting the variables ‘y’ and ‘z’ are identical? It would have to be the data type that we dimensioned the variable as in the first place and sure enough, ‘z’ is the integer type when it should be string.

So the takeaway here is that the Type mismatch error gets its origin from the same problem, but it might take figuring our just where in the code that mismatch is happening. We have seen an example where it was obvious in a line of code. And now we have seen an example where it was not obvious in the line of code itself, but rather where the variable in the code was declared as the wrong data type for the operation.

EXAMPLE 3: MISMATCHED TYPES WITHIN AN OPERATION

Let’s take a look at another example of some VBA code that will throw Run-time Error ‘13’. The following looks similar to what we have been discussing, but can you find the problem?

Sub Mismatch3()
'Demonstrates Run-time Error ‘13’ Type mismatch error due y being declared as String and set to string but inserted into a calculation for the variable myVal
Dim x As Integer
Dim y As String
Dim z As Integer
x = 3
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Cells(1, 2).Value = "x"
ws.Cells(1, 3).Value = x
y = ws.Cells(1, 2).Value
z = ws.Cells(1, 3).Value
myVal = y + z
End Sub

If we run the macro, we get the error and by clicking on ‘Debug’ in the error message, Excel highlights the line that caused the error.

run-time-image-4

But why would ‘myVal = y + z’ throw a type mismatch error? It would seem that one of the variables in the right side of the statement is probably the wrong data type. But this is an interesting conundrum. In reality, until the code execution gets to the highlighted line throwing the error, everything is actually correct.

Note that ‘y’ has been dimensioned as the string data type and is is set equal to a string value of “x" in cell B1 of our worksheet. This is actually correct so far.

Also note that the variable ‘z’ is dimensioned as the integer data type and is set to equal the value of ‘x’ in cell C1 in our worksheet. Furthermore, since in our code we have also set the variable ‘x’ to a value of 3, everything is actually correct on the variable side of things in our code.

But the problem is that you cannot execute a mathematical operation involving a string data type. This is why the statement ‘myVal = y + z’ throws the error. It’s not because there is something wrong with the variables involved in the operation of that line of code, at least not explicitly. It’s simply because the operation that the line of code itself is designed to perform cannot be done involving a value that is non-numeric.

All that said, recall that the plus sign can actually be used to join two strings of text. So if we change the data type of the variable ‘z’ to string…

Sub Mismatch3()
'Demonstrates Run-time Error ‘13’ Type mismatch error due y being declared as String and set to string but inserted into a calculation for the variable myVal
Dim x As Integer
Dim y As String
Dim z As String
x = 3
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Cells(1, 2).Value = "x"
ws.Cells(1, 3).Value = x
y = ws.Cells(1, 2).Value
z = ws.Cells(1, 3).Value
myVal = y + z
MsgBox myVal
End Sub

…look what happens when we output the value of ‘myVal’ to a message box.

run-time-image-5

So while you cannot add a string value to an integer data type, you actually can execute an operation involving the plus sign with string data types as long as the data types in the operation match. And this is the key to understanding ‘Run-time error ‘13’: Type mismatch’. You simple have to be aware and make sure your data types match when used in operations that require the match.

One of the best ways to further understand this error is to simply do what we have done in these examples. Create some simple subroutines and mismatch data types on purpose. Then fix them as you go along so the concept sinks in. The more you do this, the more equipped you will be to recognize where the problem is lurking within your own VBA code.

Leave a Reply

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