While working with VBA, you might come across errors that can disrupt your workflow.
One such error is the “Type Mismatch Error“, also known as Error 13, which occurs when you assign a value to a variable that doesn’t match its data type.
The type mismatch error can occur for various reasons, but knowing how to solve it can save you time, effort, and frustration.
This tutorial discusses common causes of the VBA type mismatch error and how to fix them.
What is a VBA Type Mismatch Error?
VBA Type Mismatch Error (also called Error 13 in VBA) occurs when the data types of values used in an operation or comparison do not match in a VBA program.
For example, if you try to add a number and text together, VBA will generate a Type Mismatch Error because it cannot perform arithmetic operations on two different data types.
It’s crucial to understand the various data types used in VBA and ensure you use the correct one for your calculations and comparisons.
To avoid VBA Type Mismatch Error, you should always ensure that the variables you declare have the appropriate data type and that any input data is explicitly converted to the correct type before using it in your code.
Some common data types in VBA include Integer, Long, Single, Double, and String. When assigning values to variables, make sure they match the variable’s data type.
Also read: Excel VBA Runtime Error 1004
How to Fix the VBA Type Mismatch Error?
Now that you know what can cause the VBA Type Mismatch Error, let’s see some examples of where we get the error and how to fix it.
Cause #1: Assigning a String to a Numeric Variable
VBA throws a type mismatch error if we assign a string to a numeric variable.
When we press F5 to execute the following example sub-routine, VBA will display the error message “Run-time error ’13’: Type mismatch.”
Sub Example_1()
Dim Sum As Integer
Sum = "50b"
Debug.Print Sum
End SubThe type mismatch error occurs because, in the third line of the code, we have assigned a string value “50b” to the “Sum” variable of Integer type and can only hold numeric values.
How to Fix?
Ensure all variables are declared with the correct data type and compatible values are assigned.
We recommend using the “Option Explicit” statement at the beginning of code modules to require explicit declaration of all variables. You can make VBA automatically insert the statement for you by doing the following:
- Open the “Tools” menu and choose “Options.”
- On the “Editor” tab of the “Options” dialog box, select the “Require Variable Declaration” option and click “OK.”
Note: VBA will only automatically insert the “Option Explicit” statement in new modules. For previous modules, you must manually insert the statement.
Cause #2: Passing an Incompatible Variable Parameter to a Sub-routine
Sometimes, when coding, we assign a value to a variable and then pass the variable to another sub-routine as a parameter.
We get a type mismatch error if the parameter is incompatible with the sub-routine.
In the example code below, we have passed the “Total” string variable to the “PrintResult” sub-routine, which is expecting a “Total” parameter of Long Integer type.
Sub Example_2()
Dim Total As Long
Total = "Twenty"
Call PrintResult(Total)
End Sub
Sub PrintResult(ByVal Total As Long)
' Print the value of Total to the Immediate Window
Debug.Print Total
End SubWhen we run the code, we encounter a type mismatch error because we have passed an incompatible parameter to the “PrintResult” sub-routine.
How to Fix?
Double-check the code and ensure the correct data type parameters are passed to the relevant sub-routines.
Also read: VBA Runtime Error 91
Cause #3: Code Reads a Value of Wrong Data Type From a Cell
We get a type mismatch error when the code reads from the wrong cell or the value in the target cell was changed by mistake.
Imagine we have the following code that reads from cell A1 of “Sheet1.”
Sub Example_3()
Dim Value As Long
Value = Sheet1.Range("A1").Value
MsgBox Value
End Sub
If, for example, cell A1 contains the value 200, but a user comes along and changes it to 200B by mistake, the code will throw a type mismatch error.
Code reading from a cell whose value was changed is the most common way a type mismatch error occurs in Excel VBA.
How to Fix?
We can use error handling to deal with the type mismatch error described in this example.
The following code shows how to use the ‘On Error’ statement to deal with a type mismatch error:
Sub Example_3()
Dim Value As Long
On Error GoTo eh
Value = Sheet1.Range("A1").Value
Debug.Print Value
Exit Sub
eh:
If Err.Number = 13 Then
MsgBox "Invalid data in cell A1"
End If
End SubExplanation of the Code
The code is designed to retrieve the value of cell A1 in “Sheet1” and display it in the Immediate window.
If no errors are encountered, the code exits without going to the error-handling section. If an error occurs, the code moves to the error handling section.
If the error is a “Type mismatch,” error number 13, a message box will notify the user of the error.
The error handling section prevents the code from crashing and provides a user-friendly error message, allowing the user to fix the problem.
Here’s a breakdown of the error-handling lines in the code:
- ‘On Error GoTo eh’: Error handling is set up in this line. It tells VBA to jump to a specific label (‘eh’ in this case) when an error occurs during the execution of the code.
- ‘eh:’: This line defines a label called “eh,” where the code will jump if an error occurs.
- ‘If Err.Number = 13 Then’: This line checks if the error number (stored in the ‘Err’ object) equals 13. Error number 13 is associated with a “Type mismatch” error.
- ‘MsgBox “Invalid data in cell A1″‘: If the error number is 13 (indicating a type mismatch error), the “MsgBox” function displays a message box with the message “Invalid data in cell A1.”
Cause #4: Assigning an Invalid Date to a Date Variable
VBA is very brilliant when it comes to dates. For example, it can interpret all the following dates assigned to the “curDate” date variable as valid:
- curDate = “12/30/2023”
- curDate = “12-30-2023”
- curDate = #12/30/2023#
- curDate = “1/2023”
- curDate = “30/December/2023”
- curDate = “2/2”
However, VBA cannot interpret some date values as valid and throws a type mismatch error. Here are some examples of invalid dates:
- curDate = “20/19/2023”
- curDate = “20/Au/2023”
- curDate = “September”
How to Fix?
Double-check the code and ensure only valid dates are assigned to date variables.
Cause #5: Code Reads from a Cell that Has an Error
If the VBA code tries to read from a cell containing an error, we get a type mismatch error.
On “Sheet2”, there is a #DIV/0! error in cell B3 of the dataset.
The following code that reads from cell B3 results in a type mismatch error:
Sub Example_5()
Dim Outcome As Long
Outcome = Sheet2.Range("B3").Value
Debug.Print Outcome
End Sub
How to Fix?
To fix the error, drop the target cell in the Watch Window to see what is causing the error. Use the below steps:
- Open the Watch Window by clicking “Watch Window” on the “View” menu.
Note: You can also open the Watch Window using the keyboard shortcut “Alt+V+H.”
The Watch Window opens at the bottom of the Code window:
- Click inside the code, press F5 to run the code, and click the “Debug” button on the error message box.
The line causing the error message is highlighted:
- Select the part of the statement on the right of the “=” assignment operator:
- Open the “Debug” menu and click “Add Watch.”
- Click “OK” on the “Add Watch” dialog box.
You can see in the Watch Window that there is an error in cell B3, and you can go to it and fix the error.
Cause #6: Assigning an Incompatible Object to an Object Variable
We get a type mismatch error if we mistakenly assign an incompatible object to an object variable.
The following example code results in a type mismatch error:
Sub Example_6()
Dim myWorksheet As Worksheet
Set myWorksheet = Workbooks(2)
End SubThe error happens because we are trying to assign a workbook to a worksheet variable in the third line of the code.
How to Fix?
Ensure that object variables are correctly declared and assigned.
Also read: Subscript Out of Range Error in VBA
Cause #7: Assigning an Incompatible Object Parameter to a Sub-routine
Sometimes, when coding, we set an object to a variable and then pass the variable to another sub-routine as a parameter. We get a type mismatch error if the parameter is incompatible with the sub-routine.
In the example code below, we have passed the “myWorkbook” workbook variable to the “ExecuteObject” sub-routine, expecting a worksheet type parameter.
Sub Example_7()
Dim myWorkbook As Workbook
Set myWorkbook = ThisWorkbook
Call ExecuteObject(myWorkbook)
End Sub
Sub ExecuteObject(sh As Worksheet)
End Sub
When we run the code, we encounter a type mismatch error because we have passed an incompatible parameter to the “ExecuteObject” sub-routine.
How to Fix?
Double-check the code and ensure that compatible data type object parameters are passed to the relevant sub-routines.
Cause #8: Assigning One Cell to an Array Variable
We get a type mismatch error if we assign only one cell to an array variable.
The following example code results in a type mismatch error because we have assigned only one cell to the “arr” array variable.
Sub Example_8()
Dim arr As Variant
arr = Sheet1.Range("A1").Value
Debug.Print arr(1, 1)
End SubThe error occurs because VBA does not convert the cell to an array but to a single data type, such as a string or long integer.
How to Fix?
Ensure that only multi-cell ranges are assigned to array variables.
This tutorial discussed eight causes of the VBA type mismatch error and solutions. We hope you found the tutorial helpful.
Other Microsoft Excel articles you may also like:
