Summary: The runtime error 13 in Excel can occur while running VBA projects. This error usually occurs if there is a mismatch in datatype in the VBA code. However, there can be various other causes for this error. In this blog, we will discuss the possible causes of the runtime error 13 - Type Mismatch and the solutions to fix it. We will also mention an advanced Excel repair software that can help resolve the error if it has occurred due to corruption in the Excel file.
You can encounter the Runtime Error 13 – Type Mismatch while working on VBA code in Excel. It is one of the Excel type-conversion errors and occurs when the Excel application fails to recognize the queries you are running in the VBA code. This happens when there is some issue with the queries in the code. The queries might contain incompatible datatypes, incorrect objects, invalid arguments or parameters, incorrect subtypes, etc. This error can prevent you from executing the code, disrupting your workflow. Let’s understand the possible causes behind this error and the solutions to resolve it.
Reasons for VBA Type Mismatch (Error 13) Error in Excel
There could be several reasons for this runtime error 13 in Excel. Some of them are:
- Trying to assign a value to the variable with incompatible data type. For example, assigning a string value to an integer type.
- Incorrect or missing objects in VBA code.
- Trying to specify an object in a procedure with an invalid value.
- The variant used in an expression has an invalid subtype.
- Incorrect syntaxes in the code.
- Faulty Excel add-ins.
- Name of the method you specified in the code is not correct.
- Corruption in the Excel file.
- Using the module name in place of objects.
Methods to Fix VBA Type Mismatch Error 13 in Excel
You can try the below methods to fix the VBA runtime 13 error in Excel.
Method 1 – Check Excel Macro Settings
You may encounter the VBA type mismatch error 13 if the macro settings are disabled. If this is the case, you can go the macro settings in Excel and check if the macros are disabled or not. If disabled, enable the Excel Macro settings.
Method 2 – Remove Faulty Excel Add-ins
Incompatible, outdated, or faulty add-ins may also cause runtime errors in Microsoft Excel. You can check if any Excel add-ins are causing the error. For this, open Run window by pressing Windows + R, type Excel /safe, and then press Enter. This will open the Excel in safe mode.
Next, open the affected file and try to execute the VBA code. If the error persists, it indicates that add-ins are causing the issue. You can remove add-ins in Excel to resolve the error.
Method 3 – Check Type in Variable/Property
You can experience the runtime error 13 – Type Mismatch in Excel when trying to assign a value to a variable but that value is not compatible with the variable. For example, you have declared variable “employee” as Long integer but you’re trying to add a string value “Hello” in it (see the below example).
Private Sub Main()
Dim employee As Long
employee = “Hello”
Debug.Print employee
End Sub
In such a case, Excel will display the type mismatch error because the variable “employee” can only hold a number.
So, you need to change the incompatible value of the employee –from string “Hello” to a number “29”. To do this, press Alt + F11 to open the Visual Basic editor.
Private Sub Main()
Dim employee As Long
employee = “29”
Debug.Print employee
End Sub
Apply the required changes and then run the code to check whether the issue is resolved.
Method 4 – Check the Values/Names in VBA Code
The Excel type-conversion errors can also occur if the application fails to match the input value with the expected value. For example, you have used a module name (Debug.Print Module1) instead of an expression to display the expression. Make sure you have specified the expression correctly in the code.
Private Sub Main()
Dim employee As Long
employee = “29”
Debug.Print Module1
End Sub
Method 5 – Check if you have Passed the Values Correctly
You can get the type mismatch error if you have not passed the value of the variable or datatype correctly in the VBA code. For example, you have placed a text in a number variable or declared a string variable to the sub. So, check the VBA code and make sure you have passed the values correctly to the variable.
Method 6 – Check CVErr Function and its Sub-type
You can experience the runtime error 13 if you’re using CVErr function incorrectly in the code. The CVErr function in Excel is used to create user-defined errors in VBA projects. Make sure the variant used in the expression in the CVErr function has the correct subtype. If you need to print an array in a variant, then you can create a loop instead of printing the # statement to prevent runtime errors.
You can also encounter runtime error 13 when trying to convert CVErr values to Date. To fix this, you can use a SELECT Case statement or other construct statements to map the return value of CVErr to a specific date value.
Method 7 – Repair your Excel File
The Excel runtime error 13 can occur due to damaged VBA code, macros, and other objects in the Excel file. You can use the Microsoft’s built-in utility – Open and Repair – to repair damaged or corrupted Excel files. Here are the steps:
- In your Excel, click the File tab, and then click Open.
- Click Browse to select the affected Excel file.
- In the Open dialog box, click on the desired file.
- Click the arrow next to the Open option and then select Open and Repair.
- A dialog box will open with three options – Repair, Extract Data, and Cancel.
- Click on the Repair option to recover maximum data.
- Once the repair process is complete, click Close on the displayed message.
The Open and Repair utility may fail to repair the severely corrupted Excel file. In such a case, you can use a professional Excel file repair tool, like Stellar Repair for Excel to repair the corrupted Excel (.xls, .xlsx, .xltm, .xltx, and .xlsm) file. The tool can restore all the objects, including macros, queries, etc., from the corrupted Excel file with complete integrity. The tool is compatible with Windows 11, Windows 10, and earlier editions.
Conclusion
The Excel VBA runtime error 13 – Type Mismatch can prevent you from executing VBA code in Excel. It occurs when the application fails to read the queries in the code. You can try the workarounds and solutions mentioned above to resolve the error. If corruption in the Excel file is causing this runtime error, then you can use a reliable Excel repair tool, like Stellar Repair for Excel. The tool can easily repair corrupt Excel file and restore all the file data, including macros, queries, tables, etc.
