Contents
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”.
For example, if you try to place text in a Long integer variable or you try to place text 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).
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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.
VBA Type Mismatch YouTube Video
Don’t forget to check out my YouTube video on the Type Mismatch Error here:
How to Locate the Type Mismatch Error
The most important thing to do when solving the Type Mismatch error is to, first of all, locate the line with the error and then locate the part of the line that is causing the error.
If your code has Error Handling then it may not be obvious which line has the error.
If the line of code is complex then it may not be obvious which part is causing the error.
The following video will show you how to find the exact piece of code that causes a VBA Error in under a minute:
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
' https://excelmacromastery.com/ 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.
' https://excelmacromastery.com/ 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.
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
' https://excelmacromastery.com/ 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 realizing that this will cause a Type mismatch error in the code.
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") 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
' https://excelmacromastery.com/ 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.
' https://excelmacromastery.com/ Sub UseModuleName() ' Type Mismatch Debug.Print module1 End Sub
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
' https://excelmacromastery.com/ 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
- Worksheets – the collection of worksheets in the Workbook
- 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.
' https://excelmacromastery.com/ 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.
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
I am getting a “Run-time error 13- Type Mismatch” in MS project. When I click debug I get “If version > 12 Then call AddPPMToolRibbon”. Need help please
Check that version is a number
Hi Paul,
I’m getting a mismatch error for a date. I have a table with lots of different ideas with about 5 possible dates (as the ideas go through stages) and first I’m trying to sort the data for easier reading and eventually equations. I have “Dim hopper as Date” and the row that is flagged/highlighted when the error message pops up is “hopper = ActiveCell.Offset(0,23).Value” . The quotation marks are not included in the code. The information grabbed before this date looks right and the column is the right column, but the cell is blank. Is the error because the date is blank? If so, how do I tell the computer to leave the value of hopper for that idea blank?
Thank you!
The date cannot be blank. Check if it the cell = “” and if it does then don’t assign the date.
very useful b structured info. thanks a lot
You’re welcome
Hi there,
my problem is, somehow, different.
I do get mismatch errors on Excel, and … I usually fix them.
But my issue is … that something weird happens. No matter what I do (ex. fixing the mismatch), the macros stop running.
I have to, save, close excel, re-open the spreadsheet and then run them again, and … usually they work (if I was able to fix the mismatch. If not, I have to repeat the whole thing over again).
So, what could I do next time, besides closing and re-opening Excel?
Are you turning off events? You could be setting EnableEvents to false. When the code stops then events are turned off. You need to set EnableEvents to True or none of the events will run. You can run Application.EnableEvents = True in the Immediate Window(Ctrl + G).
I’m getting this error:
Error #13 was generated by VBAProject
Error Line: 0
Type mismatch
Specific error= Getting oShape tag attributes.
You need to show the line of code.
hi Paul, I have this error in my excel, but I have no idea how to fix it. reading your article helps me understand a little about the error, but I still have no idea how to fix it. could you please help me to fix this error for me? thank you
If Not Intersect(Target, Range(“C5:C19”)) Is Nothing Then
swc = 0
For n = 0 To 14
Set cellcat = Range(“C5:C19”).Offset(n, 0)
If cellcat.Value = (“”) Or swc = 1 Then – this is the error
cellcat.Interior.Color = RGB(246, 246, 246)
swc = 1
Else
cellcat.Interior.Color = RGB(8, 152, 139)
End If
Next n
hi Paul, I have this error in my excel, but I have no idea how to fix it. reading your article helps me understand a little about the error, but I still have no idea how to fix it. could you please help me to fix this error for me? thank you
If Not Intersect(Target, Range(“C5:C19”)) Is Nothing Then
swc = 0
For n = 0 To 14
Set cellcat = Range(“C5:C19”).Offset(n, 0)
If cellcat.Value = (“”) Or swc = 1 Then – this is the error
cellcat.Interior.Color = RGB(246, 246, 246)
swc = 1
Else
cellcat.Interior.Color = RGB(8, 152, 139)
End If
Next n
Set catRange = Range(“$C$5:” & Range(“C5”).End(xlDown).Address)
nocat = catRange.Rows.Count
‘MsgBox (nocat)
Set catdash = Sheets(“Tablero”).Range(“$N$34:$N$” & 34 + nocat – 1) – this is the error
ThisWorkbook.Names.Add Name:=”categories”, RefersTo:=catRange
ThisWorkbook.Names.Add Name:=”catdash”, RefersTo:=catdash
Application.ScreenUpdating = False
Hi Paul, I have a mismatch error in my sheet. Not sure what I’m doing wrong here.
Dim a As String
Dim b As Single
Application.EnableEvents = False
If Target.Count = 1 And Target.Column = 1 And Target.Row > 4 And Target.Row 0 And Target.Row 10: Target.Value = Mid(a, 2, 10)
End Select
Application.EnableEvents = True
The script is suppose to convert D1234567-3D to 1234567-3D upon entry in the same cell (ie D1234567-3D is scanned into the cell and converts to 1234567-3D). There’s also another code that moves it on.
Within the sheet, I have a timestamp script:
Private Sub Worksheet_Change(ByVal Target As Range)
‘TS
If Not Intersect(Target, Sheets(“PCR Worklist”).Range(“N3:N3”)) Is Nothing Then
On Error Resume Next
If Target.Value = “” Then
Target.Offset(2, 0) = “”
Else
Target.Offset(2, 0).Value = Format(Now, “hh:mm MM/DD/yyyy”)
End If
End If
the error is relating to ‘a = Target.Value’
I encountered a strange type mismatch error today while trying to read an Excel Spreadsheet from Access. The Cell in Excel is blank and when I use “Type” it tells me it’s a Number. But in Access when I use “VarType” it tells me it’s an Object! I’ve been reading versions of the same spreadsheet for a long time now, never had an issue with blank or empty cells, but this one has me stumped. Any ideas?