VBA Type Mismatch Error – The Ultimate Guide

VBA Type Mismatch Explained

A VBA Type Mismatch Error occurs when you try to assign a value between two different variable types.

The error appears as “run-time error 13 – Type mismatch”.
 
 
VBA Type Mismatch Error 13

 
 
For example, if you try to place text in a Long integer variable or you try to place a number in a Date variable.

Let’s look at a concrete example. Imagine we have a variable called Total which is a Long integer.

If we try to place text in the variable we will get the VBA Type mismatch error(i.e. VBA Error 13).

Sub TypeMismatchString()

    ' Declare a variable of type long integer
    Dim total As Long
    
    ' Assigning a string will cause a type mismatch error
    total = "John"
    
End Sub

 
 

Let’s look at another example. This time we have a variable ReportDate of type Date.

If we try to place a non-date in this variable we will get a VBA Type mismatch error

Sub TypeMismatchDate()

    ' Declare a variable of type Date
    Dim ReportDate As Date
    
    ' Assigning a number causes a type mismatch error
    ReportDate = "21-22"
    
End Sub

 
 
In general, VBA is very forgiving when you assign the wrong value type to a variable e.g.

Dim x As Long

' VBA will convert to integer 100
x = 99.66

' VBA will convert to integer 66
x = "66"

 
 

However, there are some conversions that VBA cannot do

Dim x As Long

' Type mismatch error
x = "66a"

 
 

A simple way to explain a VBA Type mismatch error, is that the items on either side of the equals evaluate to a different type.

When a Type mismatch error occurs it is often not as simple as these examples. For these more complex cases we can use the Debugging tools to help us resolve the error.

 
 

Using the Debugger

VBA has some very powerful tools for finding errors. The debugging tools allow you to pause the code and check the values in the current variables.

You can use the following steps to help you tackle any VBA Type mismatch error.

  1. Run the code so that the error appears.
  2. Click Debug on the error dialog. This will highlight the line with the error.
  3. Select View->Watch from the menu if the Watch Window is not visible.
  4. Highlight the variable to the left of the equals and drag it into the Watch window.
  5. Highlight everything to the right of the equals and drag it into the Watch window.
  6. Check the values and the types of each.
  7. You can narrow the error further by examining the individual parts of the right hand side.

 
 
The following video shows how to do this

In the screenshot below, you can see the types in the Watch Window.

VBA Type Mismatch Watch

 
 
By using the Watch Window, you can check the different parts of the line of code with the error. You can then easily see what the variable types are.

The following sections show the different ways that the VBA Type Mismatch error can occur.
 
 

Assigning a string to a numeric

As we have seen, trying to place text in a numeric variable can lead to the VBA Type mismatch error.

Below are some examples that will cause the error

Sub TextErrors()

    ' Long is a long integer
    Dim l As Long
    l = "a"
    
    ' Double is a decimal number
    Dim d As Double
    d = "a"
    
    ' Currency is a 4 decimal place number
    Dim c As Currency
    c = "a"
    
    Dim d As Double
    ' Type mismatch if the cell contains text
    d = Range("A1").Value
    
End Sub

 
 

Invalid date

VBA is very flexible when it comes to assigning a date to a date variable. If you put the month in the wrong order or leave out the day, VBA will still do it’s best to accommodate you.

The following code examples show all the valid ways to assign a date followed by the cases that will cause a VBA Type mismatch error.

Sub DateMismatch()

    Dim curDate As Date
    
    ' VBA will do it's best for you
    ' - These are all valid
    curDate = "12/12/2016"
    curDate = "12-12-2016"
    curDate = #12/12/2016#
    curDate = "11/Aug/2016"
    curDate = "11/Augu/2016"
    curDate = "11/Augus/2016"
    curDate = "11/August/2016"
    curDate = "19/11/2016"
    curDate = "11/19/2016"
    curDate = "1/1"
    curDate = "1/2016"
   
    ' Type Mismatch
    curDate = "19/19/2016"
    curDate = "19/Au/2016"
    curDate = "19/Augusta/2016"
    curDate = "August"
    curDate = "Some Random Text"

End Sub

 
 

Cell Error

A subtle cause of the VBA Type Mismatch error is when you read from a cell that has an error e.g.

VBA Runtime Error

 
 
If you try to read from this cell you will get a type mismatch error
 
 

Dim sText As String

' Type Mismatch if the cell contains an error
sText = Sheet1.Range("A1").Value

 
 
To resolve this error you can check the cell using IsError as follows.

Dim sText As String
If IsError(Sheet1.Range("A1").Value) = False Then
    sText = Sheet1.Range("A1").Value
End If

 
 
However, checking all the cells for errors is not feasible and would make your code unwieldy. A better way is to check the sheet for errors first and if errors are found then inform the user.

You can use the following function to do this

Function CheckForErrors(rg As Range) As Long

    On Error Resume Next
    CheckForErrors = rg.SpecialCells(xlCellTypeFormulas, xlErrors).Count

End Function

 
 
The following is an example of using this code

Sub DoStuff()

    If CheckForErrors(Sheet1.Range("A1:Z1000")) > 0 Then
        MsgBox "There are errors on the worksheet. Please fix and run macro again."
        Exit Sub
    End If
    
    ' Continue here if no error

End Sub

 
 

Invalid Cell Data

As we saw, placing an incorrect value type in a variable causes the ‘VBA Type Mismatch’ error. A very common cause is when the value in a cell is not of the correct type.

A user could place text like ‘None’ in a number field not realising that this will cause a Type mismatch error in the code.

 
 
VBA Error 13
 
 
If we read this data into a number variable then we will get a ‘VBA Type Mismatch’ error error.

Dim rg As Range
Set rg = Sheet1.Range("B2:B5").Value

Dim cell As Range, Amount As Long
For Each cell In rg
    ' Error when reaches cell with 'None' text
    Amount = cell.Value
Next rg

 
 
You can use the following function to check for non numeric cells before you use the data

Function CheckForTextCells(rg As Range) As Long

    ' Count numeric cells
    If rg.Count = rg.SpecialCells(xlCellTypeConstants, xlNumbers).Count Then
        CheckForTextCells = True
    End If
    
End Function

 
 
You can use it like this

Sub UseCells()

    If CheckForTextCells(Sheet1.Range("B2:B6").Value) = False Then
        MsgBox "One of the cells is not numeric. Please fix before running macro"
        Exit Sub
    End If
    
    ' Continue here if no error

End Sub

 
 

Module Name

If you use the Module name in your code this can cause the VBA Type mismatch to occur. However in this case the cause may not be obvious.

For example let’s say you have a Module called ‘Module1’. Running the following code would result in the VBA Type mismatch error.

Sub UseModuleName()
    
    ' Type Mismatch
    Debug.Print module1

End Sub

 
 
VBA Type Mismatch Module Name

 
 

Different Object Types

So far we have been looking mainly at variables. We normally refer to variables as basic data types.

They are used to store a single value in memory.

In VBA we also have objects which are more complex. Examples are the Workbook, Worksheet, Range and Chart objects.

If we are assigning one of these types we must ensure the item being assigned is the same kind of object. For Example

Sub UsingWorksheet()

    Dim wk As Worksheet
    
    ' Valid
    Set wk = ThisWorkbook.Worksheets(1)
    
    ' Type Mismatch error
    ' Left side is a worksheet - right side is a workbook
    Set wk = Workbooks(1)

End Sub

 
 

Sheets Collection

In VBA, the workbook object has two collections – Sheets and Worksheets. There is a very subtle difference

  1. Worksheets – the collection of worksheets in the Workbook
  2. Sheets – the collection of worksheets and chart sheets in the Workbook

 
 
A chart sheet is created when you move a chart to it’s own sheet by right-clicking on the chart and selecting Move.

If you read the Sheets collection using a Worksheet variable it will work fine if you don’t have a chart sheet in your workbook.

If you do have a chart sheet then you will get the VBA Type mismatch error.

In the following code, a Type mismatch error will appear on the Next sh line if the workbook contains a chart sheet.

Sub SheetsError()

    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Sheets
        Debug.Print sh.Name
    Next sh

End Sub

 
 

Array and Range

You can assign a range to an array and vice versa. In fact this is a very fast way of reading through data.

Sub UseArray()

    Dim arr As Variant
    
    ' Assign the range to an array
    arr = Sheet1.Range("A1:B2").Value
    
    ' Print the value a row 1, column 1
    Debug.Print arr(1, 1)

End Sub

 
 

The problem occurs if your range has only one cell. In this case, VBA does not convert arr to an array.

If you try to use it as an array you will get the Type mismatch error

Sub UseArrayError()

    Dim arr As Variant
    
    ' Assign the range to an array
    arr = Sheet1.Range("A1").Value
    
    ' Type mismatch will occur here
    Debug.Print arr(1, 1)

End Sub

 
 

In this scenario, you can use the IsArray function to check if arr is an array

Sub UseArrayIf()

    Dim arr As Variant
    
    ' Assign the range to an array
    arr = Sheet1.Range("A1").Value
    
    ' Type mismatch will occur here
    If IsArray(arr) Then
        Debug.Print arr(1, 1)
    Else
        Debug.Print arr
    End If

End Sub

 
 

Conclusion

This concludes the post on the VBA Type mismatch error. If you have a mismatch error that isn’t covered then please let me know in the comments.

 
 

What’s Next?

If you want to read about more VBA topics you can view a complete list of my posts here. I also have a free eBook(see below) which you will find useful if you are new to VBA.

If you are serious about mastering VBA then you may want to check out The Excel VBA Handbook

 
 

Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook

 
 


 
 

Leave a Reply

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