Skip to content
Excel VBA 📅 2026-02-05

Resolving Excel VBA Runtime Error 13: Type Mismatch

🚨 Symptoms & Diagnosis

When working with Excel VBA macros, encountering a Runtime Error 13 indicates a fundamental issue with how data types are being handled. This error typically surfaces when VBA expects one type of data (e.g., a number) but receives another (e.g., text), or when an object reference is incorrect.

You'll usually see messages similar to:

Run-time error '13': Type mismatch

Or, specifically within the VBA Editor:

' Error 13 occurs here if .Cells(UserRow, SheetCol).Value is not compatible
If .Cells(UserRow, SheetCol).Value = "Ø" Then
    ' ...
End If

Root Cause: Runtime Error 13 primarily occurs when your VBA code attempts to assign or compare values between incompatible data types, often stemming from unexpected cell values, non-numeric input, or incorrect object references.

🛠️ Solutions

Immediate Mitigation: Use Type Conversion and Validation

This quick fix helps you bypass immediate errors by validating and converting data types before they cause a mismatch, particularly useful when dealing with variable cell contents.

Immediate Mitigation: Use Type Conversion and Validation

Employ IsNumeric for numerical checks and CVar for flexible type conversion, allowing your macro to gracefully handle unexpected data.

  1. Press Ctrl+Break to halt the running VBA code.
  2. Navigate to the Developer tab on the Excel Ribbon and click Visual Basic, or press Alt+F11 to open the VBA Editor.
  3. From the menu, select Debug > Compile VBAProject to identify the line of code where the error is occurring.
  4. Modify the suspect line by adding an IsNumeric check for potential numbers and CVar for general conversion before comparisons.
' Original problematic code might look like:
' If .Cells(UserRow, SheetCol).Value = "Ø" Then
'     Sheets(SheetNm).Unprotect "5896"
' End If

' Modified code with IsNumeric and CVar
If IsNumeric(.Cells(UserRow, SheetCol).Value) Then
    If CVar(.Cells(UserRow, SheetCol).Value) = "Ø" Then
        Sheets(SheetNm).Unprotect "5896"
    End If
End If

Best Practice Fix: Implement Robust Error Handling and Variant Usage

For resilient and stable VBA applications, proactive error handling and strategic variable declaration are essential. This approach prevents runtime errors from crashing your macro and provides diagnostic information.

Best Practice Fix: Implement Robust Error Handling and Variant Usage

Integrate On Error GoTo statements and leverage the Variant data type with runtime checks (VarType, IsNumeric) to build highly robust macros.

  1. Open the VBA Editor (Alt+F11).
  2. At the beginning of your subroutine or function, add On Error GoTo ErrorHandler to redirect execution in case of an error.
  3. Utilize the Locals Window (Debug > Windows > Locals) or Watch Window (Debug > Windows > Watch) to monitor variable types and values during execution. You can also press Ctrl+G to open the Immediate Window for quick type checks.
  4. Carefully watch variables that are involved in assignments or comparisons where the type mismatch occurs.
  5. Declare variables as Variant unless there is a strict need for a specific, explicit type. Variant variables can hold any type of data, deferring type checking until runtime, which can be useful when dealing with diverse cell inputs.
Sub SafeCode()
    On Error GoTo ErrorHandler ' Establish error handling

    Dim cellVal As Variant ' Declare as Variant for flexibility

    ' Example: Assigning a cell value
    ' Replace '.Cells(UserRow, SheetCol)' with your actual range reference
    cellVal = ActiveSheet.Cells(1, 1).Value ' For demonstration, assuming data in A1

    ' Robust check before comparison
    If VarType(cellVal) <> vbError And IsNumeric(cellVal) Then
        ' Convert to string for comparison if the target is a string ("Ø")
        ' Or convert to appropriate type if comparing against a number
        If CStr(cellVal) = "Ø" Then
            ' Replace 'Sheets(SheetNm)' with your actual sheet reference
            ActiveSheet.Unprotect "5896" ' Example action
        ElseIf CInt(cellVal) = 100 Then ' Example if comparing to a number
            MsgBox "Cell value is 100!"
        End If
    ElseIf VarType(cellVal) = vbString Then ' Handle string values specifically
        If cellVal = "Not Applicable" Then
            MsgBox "Value is 'Not Applicable'."
        End If
    Else
        ' Handle other non-numeric or error types if necessary
        MsgBox "Cell value is not numeric or an expected string, type: " & TypeName(cellVal)
    End If

    Exit Sub ' Exit the sub normally if no error occurs

ErrorHandler:
    ' Display a user-friendly message with the error description
    MsgBox "An error occurred: " & Err.Description & vbCrLf & _
           "Error Number: " & Err.Number, vbCritical, "VBA Error"
    Resume Next ' Optional: Resume next statement after error (use with caution)
    ' Or simply Exit Sub to stop execution
End Sub

🧩 Technical Context (Visualized)

The Excel VBA Runtime Engine is responsible for executing your macro code, including handling variable type checking, object assignments, and expression evaluation. When a value is assigned to a variable, or an expression is evaluated, the engine performs an implicit or explicit type conversion. If the types are fundamentally incompatible (e.g., trying to put "Hello" into an Integer variable), or if a comparison cannot be made due to mismatched types (like comparing an #N/A error in a cell to a number), the Run-time error '13': Type mismatch is triggered.

graph TD
    A[VBA Code Execution] --> B{Variable Assignment / Expression Evaluation};
    B --> C[Retrieve Value & Determine Its Type];
    C --> D[Determine Target Variable/Expression Type];
    D --> E{Are Value Type & Target Type Compatible?};
    E -- No --> F["Run-time error '13': Type mismatch"];
    E -- Yes --> G[Operation Successful];
    G --> H[Code Continues Execution];

✅ Verification

After implementing your chosen solution, verify that the macro now executes without the Run-time error '13'.

  1. Execute your VBA code by pressing F5 in the VBA Editor.
  2. Set a breakpoint (press F9) on the line of code that previously caused the error. Step through the code line-by-line using F8 to observe variable values and types as they change.
  3. Open the Locals Window (Debug > Windows > Locals) and observe the Type column for suspect variables. Ensure they are the expected types (Variant, String, Long, etc.).
  4. Use the Immediate Window (Ctrl+G) to test types at runtime. For any variable cellVal, type Debug.Print TypeName(cellVal) & ": " & cellVal and press Enter to see its current type and value.

📦 Prerequisites

To implement these solutions, ensure you have:

  • Excel 365/2021+: While concepts apply broadly, newer versions offer optimal stability.
  • Developer Tab Enabled: Go to File > Options > Customize Ribbon and check Developer.
  • VBA Editor Access: The Visual Basic for Applications (VBA) Editor is required for all code modifications.