Excel VBA Type Mismatch: Causes and Fixes

In Excel VBA, “Run-time error 13: Type mismatch” means VBA tried to use a value as a different data type than the operation allows. Find the highlighted line, inspect the values and their types, then convert or validate the input explicitly instead of relying on automatic conversion.

What the VBA type mismatch error means

VBA assigns types to values and variables: a number may be an Integer, Long, Double, or Currency; text is a String; a date is a Date; and an Excel cell or worksheet is an object. A Variant can hold several kinds of values, but it does not make every operation valid. For example, adding text that cannot be interpreted as a number to a numeric variable can produce a mismatch.

The error is commonly shown as “Run-time error ’13’: Type mismatch.” It usually appears while a macro is running, rather than when VBA first compiles the procedure. The line highlighted after you choose Debug is the place where VBA detected the incompatible operation. The value that caused the problem may have been read or changed earlier, so inspect the inputs on that line as well as the expression itself.

This is different from a compile-time “ByRef argument type mismatch.” That message concerns an argument passed to a procedure parameter, often because the argument’s declared type does not match the parameter’s type. It is also different from “Overflow,” which means a value is outside a variable’s permitted range, and “Object required,” which indicates that code expected an object reference.

Common causes when working with worksheet cells

Cells are a frequent source of mismatches because a column that looks uniform can contain numbers, text, blanks, formulas, errors, or dates. Formatting does not guarantee that the underlying value has the type your code expects. A cell displaying 25 could contain the number 25 or the text “25”; those may behave differently when assigned or compared.

Text in a numeric field: A cell might contain a word, a space, a number stored as text, or a value such as “12 apples.” Assigning it to a Double or applying a numeric operation can fail.
Excel errors: A formula can return #N/A, #VALUE!, or another worksheet error. VBA receives an error value, not an ordinary string or number. Check it with IsError before converting it.
Blank cells and empty strings: A genuinely empty cell and a formula that returns "" are not the same value. Check for Empty where relevant, and account for zero-length text separately.
Unexpected dates or Booleans: A date may arrive as a Date value or as a number, depending on how it was read. A cell containing TRUE or FALSE is not automatically a usable numeric measurement.
More than one cell: Reading a multi-cell range can return a two-dimensional Variant array. Code that expects one scalar value may fail when it receives an array or passes it to a scalar conversion.

Excel’s Range.Value2 property returns cell contents without using the VBA Currency and Date subtypes; date-like values are returned as numbers. That can be useful for predictable data handling, but it means code must know whether a number represents a date or a measurement. The cell’s number format alone does not make every read operation return a Date.

How to find the failing value

When the error dialog appears, choose Debug. VBA highlights the statement where the mismatch surfaced. If the editor is not visible, open it with Alt+F11 on Windows. Step through the procedure with F8 to see which statement runs immediately before the error; on some keyboards, you may need to hold Fn to send F8. Excel for Mac also has the Visual Basic Editor and stepping tools, although function-key behavior depends on macOS and keyboard settings.

Use the Immediate window in the Visual Basic Editor (View > Immediate Window) to inspect variables while execution is paused. For a Variant named v, check ? TypeName(v) and ? VarType(v). You can also hover over a variable in the editor. These checks help distinguish a number from numeric-looking text, an error value, an array, or an object reference.

Then trace where that value came from. Check the source cell, the range size, the variable declaration, and any function that might return a different type in different circumstances. A routine may work for most rows and fail only when it reaches the first blank, formula error, or unexpected text entry. Reproduce the failure with that exact input before changing the code.

Handle cell values before converting them

Validate a value before calling CDbl, CDate, or another conversion function. The following example expects a single value in Sheet1 cell A2. It checks for an Excel error, Null, an empty cell, blank text, and nonnumeric content before converting the value to a Double.

Save a copy of the workbook before running any macro. Macro actions cannot be undone with Ctrl+Z, even if a particular example only reads a value.

Option Explicit

Sub ReadAmountSafely()
    Dim rawValue As Variant
    Dim amount As Double

    ' Read one cell without assuming its content is numeric.
    rawValue = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value2

    If IsError(rawValue) Then
        MsgBox "A2 contains an Excel error."
        Exit Sub
    End If
    If IsNull(rawValue) Then
        MsgBox "A2 contains Null."
        Exit Sub
    End If
    If IsEmpty(rawValue) Then
        MsgBox "A2 is empty."
        Exit Sub
    End If
    If Len(Trim$(CStr(rawValue))) = 0 Then
        MsgBox "A2 is blank text."
        Exit Sub
    End If
    If Not IsNumeric(rawValue) Then
        MsgBox "A2 is not numeric."
        Exit Sub
    End If

    amount = CDbl(rawValue)
    MsgBox CStr(amount)
End Sub

This is an example of input validation, not a universal parsing rule. IsNumeric answers whether VBA can interpret a value as numeric; it does not establish that the value is appropriate for your task. For example, a numeric-looking text entry may use a decimal or thousands separator that depends on regional settings. If a workbook imports data from different locales, define and test a consistent parsing approach rather than assuming every string has the same format.

Choose suitable variable and range types

Declare variables to match their intended use. Use Long for many whole-number counters and row numbers; Excel worksheet row numbers can exceed the range of the older 16-bit Integer type. Use Double for general floating-point calculations, String for text, Date for dates you have validated as dates, and an object type such as Worksheet for a worksheet reference. Use Variant when a value genuinely needs to hold more than one kind of content, not simply to hide uncertainty.

For a single cell, a Variant is often a practical receiving variable because it can preserve the value’s actual type long enough for the code to inspect it. For a multi-cell range, store the result in a Variant array and process the array deliberately. Do not assume that assigning a range to a typed scalar will select one cell or convert every item automatically.

Use Option Explicit at the top of each module. It requires variables to be declared and catches misspelled variable names at compile time, which prevents some bugs that can otherwise lead to confusing runtime behavior. It does not prevent a type mismatch caused by bad data, but it makes the code’s intended types easier to review.

Conversions, dates, and comparisons

VBA conversion functions make intent clearer, but they can still fail when the input is unsuitable. CLng converts to a Long, CDbl to a Double, CStr to a String, and CDate to a Date when the input can be interpreted accordingly. Check for error and empty values first; do not use a conversion function as a substitute for validation.

Be especially careful with dates. A cell can display a date while its underlying value is a serial number, and a text date such as 03/04/2026 can be ambiguous across regional conventions. If the source is text, establish its expected format and parse it accordingly. If it is already a valid Excel date value, use a date-aware variable or format it for display rather than comparing a localized text rendering.

Comparisons can also produce surprising results when one side is text and the other is numeric. Normalize values at the boundary of the procedure: convert validated inputs once, then perform calculations and comparisons using consistent types. Avoid scattering conversions through a long expression, where it becomes harder to identify which part fails.

Prevent mismatches in procedures and loops

When passing arguments to a procedure, check both the parameter declaration and the argument type. VBA parameters are ByRef by default, meaning the procedure receives a reference to the supplied variable. A ByRef argument type mismatch is generally reported during compilation, not as run-time error 13. Declaring a parameter ByVal passes a value instead, but it is not a blanket fix: the value still has to be compatible with the parameter type.

In loops over worksheet data, test each cell before converting it. A column may contain a valid number on one row and a formula error on the next. If blank rows are meant to be skipped, make that an explicit rule; if invalid entries should stop the process, report the row number and content so the user can correct it. Error messages that identify the source cell are more useful than a generic failure.

For debugging, temporarily print the value and its type immediately before the suspect operation. The following small diagnostic procedure shows the pattern; adapt the variable name to the code being investigated.

Option Explicit

Sub InspectValue()
    Dim v As Variant

    ' Replace this example assignment with the value under investigation.
    v = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value2
    Debug.Print TypeName(v), VarType(v)
End Sub

The Immediate window will show the type information when the procedure runs. Remove temporary diagnostics or keep them only where they provide useful logging. If the variable holds an array, inspect its dimensions and elements rather than treating the array as one scalar value.

Related errors and common misconceptions

A mismatch does not necessarily mean the cell is visibly formatted incorrectly. Number formatting changes how Excel displays a value; it does not reliably change the underlying data type. Likewise, changing a cell’s format to Number does not always convert text that looks like a number into a true numeric value.

Nor is On Error Resume Next a sound general solution. It suppresses runtime errors and allows the procedure to continue, potentially leaving incomplete or incorrect results. Use normal validation for expected cases, and reserve error handling for genuinely exceptional situations. If you do handle an error, check the error number and restore normal handling with On Error GoTo 0 when appropriate.

Finally, do not confuse error 13 with a worksheet formula’s #VALUE! result. A formula error is a value inside a cell; VBA may then raise error 13 if code tries to use that value as an incompatible type. Check the cell with IsError before converting or calculating with it.

Frequently Asked Questions

What does Run-time error 13 mean in Excel VBA?
It means an operation received a value of an incompatible type, such as text where a number is required. Use Debug to find the failing line, then inspect the value and its source.

How do I fix a type mismatch in a cell value?
Read the cell into a Variant, check for errors, blanks, and unexpected text, and convert it only after validation. Confirm that the range contains the number of cells your code expects.

Can a blank cell cause a VBA type mismatch?
Yes. A blank cell, a formula returning an empty string, and a cell containing spaces can behave differently. Check for Empty and blank text according to the needs of your procedure.

Why does VBA say ByRef argument type mismatch?
That is usually a compile-time argument and parameter compatibility issue, not the same as run-time error 13. Compare the declared parameter type with the variable being passed, and consider whether ByVal or a deliberate conversion is appropriate.

Does changing a cell to Number fix a type mismatch?
Not necessarily. Formatting changes the display, but text that looks numeric may remain text. Validate or convert the underlying value in VBA rather than relying on its appearance.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.