Excel Macro Type Mismatch: Causes and Fixes

An Excel macro “Type mismatch” is VBA run-time error 13: code tried to use a value as a different data type than VBA can accept. Find the highlighted line, inspect the value and variable types, then validate or convert the value explicitly; suppressing the error does not fix its cause.

What “Type mismatch” means in VBA

VBA assigns types to variables and values. A variable declared as Long, for example, is intended to hold a whole number, while String holds text, Date holds a date or time, and an object variable such as Range refers to an Excel object. A Variant can hold several kinds of values, but it does not make every operation valid: asking VBA to treat an error value as text or an array as a single number can still fail.

Error 13 occurs when the operation on a value does not fit its actual type. A common case is reading a cell containing “Pending” into a numeric variable and then performing arithmetic. The same macro might work on one row and stop on another because the cells do not all contain the same kind of data. The error message identifies the general problem, not the exact cell or conversion that needs attention.

Not every message containing “type mismatch” is the same issue. A compile-time message such as “ByRef argument type mismatch” appears when VBA checks a procedure call; run-time error 13 appears while code is executing. Both involve incompatible types, but the fix may be different.

Find the exact line and value

When the error appears, choose Debug if the dialog offers it. The VBA editor highlights the statement that failed. Check the value being read or passed on that line, its declared variable type, and the operation being attempted. If the line contains multiple expressions or a procedure call, temporarily split it into simpler statements so you can see which part fails.

For example, a line that reads a cell and converts it can be separated into a read, a check, and a conversion. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11 and use F8 to step through code one statement at a time. On a Mac, the VBA editor is available through Tools > Macro > Visual Basic Editor; keyboard shortcuts can differ by keyboard and macOS settings. If the editor is not available, confirm that you are using desktop Excel: Excel for the web can open a workbook containing VBA but cannot create, run, or edit VBA macros.

Inspect the cell directly in the worksheet, including whether it contains text, a formula error such as #N/A, a blank, or a value that only looks numeric. The VBA editor’s Immediate window can also help: press Ctrl+G in the Windows editor, then use a statement such as Debug.Print TypeName(variableName) to display a variable’s current type. Replace the example name with the variable used in your code.

Why worksheet cells cause type problems

A cell does not always supply the kind of value a macro expects. A cell that looks blank may be truly empty, contain a formula returning an empty string, or contain spaces. A number stored as text may look like a number on the sheet. A formula may return a Boolean value, text, or an Excel error. VBA receives a value based on the cell’s contents and the property used to read it, not simply on its number format or appearance.

When code reads a single cell, the result can be assigned to a Variant and checked before conversion. Reading a multi-cell range is different: its value is generally a two-dimensional array. Code that expects one scalar value may therefore fail later when it treats that array as a string or number. If the macro is meant to process a range, loop through its cells or handle the array dimensions deliberately.

For dates, Excel stores dates as serial numbers, while VBA may represent a cell value as a date subtype. The Value2 property returns date and currency-formatted values as their underlying numeric values rather than VBA’s Date and Currency subtypes. That can be useful when a macro needs raw values, but it also means the code must interpret a date serial correctly if it needs a calendar date.

Convert only after checking the value

Use a conversion function that matches the intended data: CLng for a whole number, CDbl for a floating-point number, CStr for text, and CDate for a date. Conversions can fail when the input is not suitable, so test first. IsNumeric can help screen a value before converting it to a number; IsDate can screen a value intended as a date; and IsError detects an Excel error value.

Be careful with blanks and formula errors. Check IsError before trying to convert or compare a cell value. Distinguish an empty cell from a formula returning "" if that difference matters to the macro. Avoid assuming that a cell’s number format guarantees its underlying value is numeric. Also remember that text-to-number and text-to-date conversion can depend on regional settings, including decimal and date separators. If the workbook is shared across locales, define an expected input format rather than relying on ambiguous text such as 01/02/2026.

Handle mixed data safely

This example reads one cell, checks for common non-numeric cases, and only then converts it to a number. Change the worksheet name and cell address to match your workbook. Before running a macro, save a separate copy of the workbook: macros cannot be undone with Ctrl+Z.

Option Explicit

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

    ' Read the cell's underlying value; change the sheet and cell as needed.
    rawValue = ThisWorkbook.Worksheets("Data").Range("B2").Value2

    If IsError(rawValue) Then
        MsgBox "B2 contains an Excel error value."
    ElseIf IsEmpty(rawValue) Then
        MsgBox "B2 is empty."
    ElseIf Len(Trim$(CStr(rawValue))) = 0 Then
        MsgBox "B2 is blank or returns an empty string."
    ElseIf IsNumeric(rawValue) Then
        amount = CDbl(rawValue)
        MsgBox "The amount is " & CStr(amount)
    Else
        MsgBox "B2 does not contain a value VBA can treat as numeric."
    End If
End Sub

The variable begins as a Variant because the cell may contain different kinds of values. The code checks for an Excel error, an empty cell, and blank text before testing whether conversion is reasonable. It does not silently replace unexpected text with zero, which could conceal incorrect source data. The final CDbl conversion is only attempted after IsNumeric passes.

Other common causes to check

Object versus value: A Range variable represents an object. Assign an object reference with Set, such as Set target = Worksheets("Data").Range("A1"). To read the cell’s contents, use a value property such as target.Value or target.Value2.
Excel error values: A cell containing #VALUE! or #N/A is not ordinary text. Test it with IsError before comparing or converting it.
Unexpected procedure arguments: Check the type and order of arguments passed to a Sub or Function. A ByRef argument must be compatible with the parameter type; a ByVal parameter can accept a value without requiring the caller’s variable to be passed by reference.
Arrays and collections: A range of multiple cells returns an array-like value, not one cell’s scalar value. Keep the result in a suitable variable and process its elements rather than converting the whole result as if it were one number.
Implicit conversion: VBA sometimes converts compatible values automatically, but relying on that behavior makes macros fragile. Explicit checks and conversions make assumptions visible and easier to troubleshoot.

Prevent the error in future macros

Declare variables with Option Explicit at the top of each module. This requires variables to be declared and catches misspelled names, which otherwise may become unexpected Variants. Use specific types where the data is predictable, and use a Variant when the source can legitimately return multiple kinds of values, such as a worksheet cell that needs validation.

Validate data at the point where it enters the macro. For a column expected to contain quantities, decide how to handle blanks, text, negative values, and formula errors before doing calculations. If invalid rows should be skipped, make that choice explicit and report how many were skipped. If invalid input should stop the process, show the cell address and reason so the user can correct the source.

Keep error handling specific. A broad On Error Resume Next can hide error 13 and allow later statements to use an invalid or unchanged value. If you use error handling for a particular operation, check Err.Number, handle the expected case, and restore normal handling with On Error GoTo 0. Error handling should complement type checks, not replace them.

Type mismatch compared with nearby errors

“Object variable or With block variable not set” generally means an object reference is missing, while “Subscript out of range” often points to a worksheet name, array index, or collection item that does not exist. “Overflow” means a value is outside the range a numeric type can store. These errors can appear in the same macro, but changing a variable to Variant is not a universal remedy. Identify the highlighted statement and address the specific cause.

Likewise, a value that looks numeric in Excel may be text without producing an error until the macro performs a conversion or calculation. Formatting a cell as Number changes its display, not necessarily its underlying contents. If the source is imported data, clean or validate it deliberately rather than assuming formatting has corrected the type.

Frequently Asked Questions

What does Run-time error 13 mean in Excel VBA?
It means VBA encountered a value whose type is incompatible with the operation being performed. A common example is trying to use text from a worksheet cell as a number.

Why does my macro work on some rows but fail on another?
The rows may contain different data types: one row could have a number, while another has text, a blank, or a formula error. Check the value on the highlighted line and validate each row before conversion.

Will changing the variable to Variant fix a type mismatch?
Not necessarily. A Variant can hold different kinds of values, but invalid operations on those values can still raise error 13. Use it to receive variable input, then test and convert the value explicitly.

How do I handle a cell that contains #N/A?
Read the cell into a Variant and test it with IsError before converting, comparing, or doing arithmetic. Decide whether the macro should skip the row, report the error, or stop for correction.

Can I undo a macro that caused a type mismatch?
Ctrl+Z generally cannot undo changes made by a VBA macro. Save a separate copy before running code that changes workbook data, and use a backup or version history if you need to recover earlier contents.

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.