VBA Runtime Error 13 – Type Mismatch

A VBA Runtime Error 13: "Type Mismatch" is hitting my code hard.  This is the line I’m hung up on:

VBA Runtime Error 13

Man, I can’t figure out what I’m doing wrong.

What is VBA Runtime Error 13: Type Mismatch?

VBA Runtime Error 13:  Type mismatch occurs when VBA assigns one type of data to a variable and it gets another.

The most common VBA Runtime Error 13 occurs when you have a variable that stores a number such as an VBA Integer data type or a VBA Double data type and you try to assign a text value that VBA cannot convert into a number value.

For example, each line of the following code will generate a VBA Runtime Error 13 type mismatch error:

Sub Create_VBA_Error_13()

Dim nNumPayments As Integer

Dim dPayAmount As Double

Dim dPayDate As Date

nNumPayments = "12 Payments"

dPayAmount = "12 dollars 12 cents"

dPayDate = "Tuesday, June 12"

End Sub

Another type of VBA Error 13 occurs when you have a VBA Boolean variable that only accepts TRUE or FALSE values.  Generally if you will use a logical condition or test that evaluates to TRUE or FALSE value

Sub Create_VBA_Error_13_Boolean()

Dim bPaymentOK  As Boolean

Dim dPayAmount  As Double

dPayAmount = 12.12

' Evaluate to TRUE or FALSE

bPaymentOK = dPayAmount > 0

bPaymentOK = Not dPayAmount = 0

bPaymentOK = 1

'/ VBA ERROR 13 -

'/ "YES" Does Not = TRUE or FALSE

bPaymentOK = "YES"

End Sub

VBA Logical Loops: A Trick

In this case there is a tricky situation. The line that has an error is a VBA DO WHILE loop.

The DO WHILE loop evaluates and is expecting a Boolean TRUE or FALSE value so that it can decide to either continue or stop looping.

The problem is that the value it is evaluating is a range's Text property.

The  TEXT in that cell is not likely to convert easily into a Boolean TRUE or FALSE value. If that text data type is anything other than a TRUE or FALSE value it throws off a VBA Error 13 type mismatch because its looking for a Boolean and you are giving it a text value.

This loop is part of a dynamic looping technique I teach as part of my Excel VBA courses so I know where this is going. The dynamic looping technique loops through a list of data until we hit a blank cell, which would be the end of the list.

The correct code is:

Do Until ThisWorkbook.Worsheets("Data").Range("A1").Offset(nRowOffset).Text = ""

The reason that this works is that as VBA is looping down the list of cells it is constantly checking to see if the text property of a cell is blank by evalauting it as equal to a null string (= "").

If the cell has a non-null text value it evaluates to a FALSE value and continue the loop, if the cell is blank it evaluates to a TRUE value and the loop will stop.

, , ,

3 Responses to VBA Runtime Error 13 – Type Mismatch

  1. Dick Goossens October 20, 2012 at 9:22 am #

    Hi,

    How can I force VBA to stop the automatic typecasting. If I have a string variable, like strText and I write strText=12.2, then VBA automatically makes strText= "12.2". I want a type mismatch error here. How??

    With kind regards,
    Dick Goossens

    • Adrian Pask October 23, 2012 at 10:02 pm #

      Interesting question.

      Here is what is happening:

      You have declared a variable as a string using a statement like:

      Dim sPayment as String
      sPayment = 12.2

      Because the variable sPayment is dimensioned as a string VBA is forcing a type conversion from a number (double) to a text string. The second line is equivalent to:

      sPayment = CStr(12.2)

      Either way the variable sPayment holds "12.2" which is a text string and not a number value.

      The bigger question is why would you want a type mismatch? It is never a good idea to create an error.

      If you are worried that the value might not be numeric there are many functions that can tell you if it is a number and is likely to cause a type mismatch error.

      For example the VBA function IsNumeric() returns TRUE if the text string can be converted into a number.

      IsNumeric("12.2") = TRUE
      IsNumeric("12a") = FALSE

      Hope that answers the question. If you need more clarification please clarify in the comments. – Adrian

      • Dick Goossens October 30, 2012 at 4:01 pm #

        Thanx Adrian,

        the reason behind this question is that I want to be sure that during the design phase of the code I do not assign values to variables that are not suited for that kind of data type, resulting in loss of information. So, just a check for myself. Not during the execution of the code: the errors should be gone by then. But I will think about what you wrote. Maybe I should be more strict in my variable naming convention,

Leave a Reply