Excel VBA Overflow Error: Causes, Examples, and Fixes

VBA & macros · Updated November 20, 2026

An Excel VBA overflow error is Run-time error 6: VBA tried to store or calculate a number that is outside the range supported by the value’s data type. The usual fixes are to use a suitable type, such as Long instead of Integer, or to check and scale values before converting or doing arithmetic.

What the overflow error means

VBA assigns each variable a data type, which determines what values it can represent. An overflow happens when an assignment, conversion, or calculation produces a value that the destination type cannot hold. The error may appear while a macro is running, often on the line that assigns the value or performs the calculation.

For example, VBA’s Integer type holds whole numbers from -32,768 through 32,767. Assigning 40,000 to an Integer causes an overflow. A worksheet cell can display 40,000 without difficulty; the limit belongs to the VBA variable, not the cell.

This is different from the worksheet error #NUM!. That is an error value in a cell formula. VBA Run-time error 6 stops or interrupts VBA code unless the macro handles the error. A macro can also encounter a cell error value when reading a range, but that is a separate issue from a VBA numeric overflow.

Know the ranges of common VBA types

Choosing a type that matches the data is the first preventive step. These are useful reference points for VBA in Microsoft 365 on Windows:

  • Byte: whole numbers from 0 to 255.
  • Integer: whole numbers from -32,768 to 32,767.
  • Long: whole numbers from -2,147,483,648 to 2,147,483,647.
  • Single and Double: floating-point types for numbers with fractional parts or wider magnitude. They have finite ranges and approximate precision, so they are not exact representations of every decimal value.
  • Currency: a fixed-point type with four digits after the decimal point and a range of approximately -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
  • Variant: can hold values of different subtypes, but it does not make every value unlimited. A Variant containing an Integer, Long, or another numeric subtype still has that subtype’s limits.

In VBA, Long remains a 32-bit integer in both 32-bit and 64-bit Office. LongLong is a 64-bit integer available only in 64-bit VBA. LongPtr changes size with the Office bitness and is intended for pointers and handles in Windows API declarations, not as a general replacement for numeric variables. For ordinary counts and row numbers, Long is usually the practical choice.

Common causes in Excel macros

Overflow is not limited to assigning a single large literal. It can arise anywhere the code converts or combines values. Common sources include:

  • A small variable type: a row count, loop counter, or total is declared as Integer even though it may exceed 32,767. This is especially common in older VBA examples.
  • A calculation exceeds the result type: two individually valid values can produce a product or sum too large for the type used in the expression or destination.
  • A conversion is out of range: functions such as CInt and CLng convert to specific numeric types. If the converted value cannot fit, VBA raises an error.
  • Unexpected worksheet data: a cell that used to contain a small count may now contain a very large number, a numeric string, or a value with decimals.
  • A loop counter reaches its limit: an Integer counter cannot safely count through a range that goes beyond 32,767.
  • Values are multiplied before being stored: changing only the receiving variable may not be enough if the intermediate calculation itself overflows.

That last point matters when fixing a formula in code. If a multiplication is evaluated using narrow operand types, assigning its result to a wider variable afterward may be too late. Convert an operand before the operation, or use an appropriate floating-point type when approximation is acceptable. For exact whole-number arithmetic, check that the expected result fits the chosen integer type.

How to locate the line causing it

When the error dialog appears, choose Debug if that option is available. VBA highlights the line where execution stopped. Inspect the variables on that line and trace where their values came from; the highlighted line may be the point where an earlier bad value becomes impossible to store, not where the data originally entered the workbook.

In the Visual Basic Editor, use View > Locals Window to inspect variables in the current procedure, or hover over a variable while execution is paused. Step through the procedure with F8 on Windows to see how values change. On Mac, the Visual Basic Editor can be opened from the Developer tools; the keyboard shortcut may require Option+F11 or Fn+Option+F11, depending on the keyboard. Menu availability and shortcut behavior can vary by Mac setup.

For a repeatable investigation, check these details:

  1. Record the highlighted statement and the values of its inputs.
  2. Check each variable declaration, including loop counters and temporary variables.
  3. Inspect the actual worksheet value with Debug.Print or the Locals window, rather than assuming it matches the displayed formatting.
  4. Try the same inputs in smaller pieces to identify whether the failure occurs during conversion, addition, multiplication, or assignment.
  5. Compile the project using Debug > Compile VBAProject after making changes. Compilation can catch certain code problems, though it does not prove that runtime values will stay within range.

To open the editor on Windows, press Alt+F11, or choose Developer > Visual Basic if the Developer tab is available. On Mac, use the Visual Basic command from the Developer tab or the applicable Tools menu. The exact menu presentation differs from Windows.

Choose a suitable type and validate conversions

For a count, use Long unless there is a specific reason not to. This is a common improvement for counters that might otherwise exceed the Integer limit. A worksheet has far more rows than an Integer can count, so an Integer loop variable is a poor fit for iterating over large row ranges.

Use Double for calculations that may have fractional values or whose magnitude may exceed the Long range, while remembering that floating-point results can have tiny representation differences. Use Currency where its fixed four-decimal-place scale and range fit the task. Neither type should be selected blindly: choose based on the expected values and the precision the calculation requires.

When converting user input or cell contents, validate it before conversion. Check that it is numeric and within the intended bounds, and decide what to do with fractions rather than letting a conversion function make that decision implicitly. CLng rounds to the nearest whole number, with midpoint values rounded to the nearest even number; it does not simply discard the decimal portion. If the desired rule is truncation or a specific rounding policy, apply that policy explicitly and test boundary values.

Be cautious with Variant. It can simplify code that must accept more than one kind of input, but it can also hide type changes until runtime. Declaring a result as Variant does not guarantee that a calculation will never overflow, because VBA still applies numeric subtypes and conversion rules while evaluating expressions.

Example: replace an Integer counter

This short example demonstrates a counter that can safely hold 40,000. Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. The procedure only displays a value, but keeping a backup is a sound habit before running unfamiliar or modified code.

Sub ShowLargeCount()
    Dim count As Long ' Long supports values larger than Integer.

    count = 40000
    MsgBox count
End Sub

If count were declared as Integer, the assignment would cause Run-time error 6. Declaring it as Long fixes this particular case because 40,000 is within the Long range. It does not mean every calculation is automatically safe: a sum or product can still exceed the Long limit.

For example, if two Long values are multiplied, consider the largest possible product before storing it. If the expected product may exceed the Long range, use a wider suitable calculation type or redesign the calculation to avoid creating an out-of-range intermediate value. A Double can represent much larger magnitudes, but it does not preserve exact integer precision for arbitrarily large values.

Handle expected bad input without hiding bugs

If a macro reads data from a user or workbook that may be invalid, give the user a useful message and keep the validation close to the input. Do not use On Error Resume Next as a blanket fix: it suppresses many errors, can allow incorrect results to continue through the procedure, and does not make a value fit its type.

A targeted error handler can be useful when a conversion might fail for data that cannot be fully validated in advance. Handle the specific operation, inspect Err.Number, and report or log the original value. Clear or reset error handling before continuing with unrelated code. For errors that should never happen, it is often better to let VBA stop in the debugger during development instead of silently substituting a value.

Also distinguish a numeric overflow from an invalid worksheet cell. A cell containing #VALUE! or another Excel error is not an ordinary number. Check for an error value before treating a cell as numeric, and define how the macro should handle that row: skip it, report it, or stop with a clear message. Skipping silently can conceal missing or corrupted source data.

Frequently Asked Questions

What is Run-time error 6 in VBA?
It is VBA’s overflow error. A value or calculation cannot be represented by the numeric type being used, or a conversion to that type is outside its supported range.

Will changing Integer to Long always fix an overflow?
No. It fixes cases where a value is above the Integer limit but still within the Long range. A value or calculation beyond the Long limit can still overflow, and floating-point or conversion issues may need a different solution.

Why does my macro overflow when the cell displays a valid number?
Cell formatting controls how a value looks, not the range of the VBA variable receiving it. The cell may hold more digits than the variable can store, or a calculation may exceed the variable’s range. Inspect the underlying value and the variable declaration.

Is an overflow error the same as #NUM! in Excel?
No. #NUM! is a worksheet formula error value. Run-time error 6 is raised by VBA while a macro runs. A macro can encounter both kinds of problem, but they require different diagnosis.

Can I fix overflow by declaring every variable as Variant?
That is not a dependable fix. A Variant can contain different subtypes, but numeric operations and conversions still have limits, and the less explicit type can make code harder to understand. Use a type chosen for the expected range and validate data at its source.

Related

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.