Error 6, “Overflow”, means a value is too big for its data type. Integer holds only -32,768 to 32,767, so Dim i As Integer fails as soon as a loop passes row 32,767. Declare whole numbers as Long, which goes past 2.1 billion, and decimals as Double.
The type of the result variable isn’t enough on its own. With Dim total As Long, the line total = 200 * 200 still overflows: VBA multiplies the two numbers as Integers first, and 40,000 is too big before it ever reaches total. Convert one of them, as in total = CLng(200) * 200. The same happens with two Integer variables.
0 / 0 also gives error 6 rather than “Division by zero”, which is what you’ll see when two empty cells are divided. For money, Currency holds values up to about 922 trillion with four decimal places.
To find the cause, click Debug on the error message and hover over the variables on the highlighted line. One of them, or the result of the calculation, is outside its type’s range.
What is Overflow Error in VBA?
In VBA, Overflow (Error 6) is a run-time error that occurs when you assign a number to a variable that is out of the range of numbers which that data type can take.
In simple words, this error occurs when you go out of the range for a variable’s type.
Let’s say you are using the Integer data type that can take values ranging from -32,768 to 32,767. When you specify a value that is out of this range, you get the Overflow run-time error.

In the above example, you can see that we have used the integer data type for the iNum variable but while specifying the value, we have used “10000000”, which is way more than the range, and when you run the code, the Overflow run-time error occurs.

Sub myMacro() Dim iNum As Integer iNum = 10000000 End Sub
To fix this code, declare iNum as Long, which can hold values up to 2,147,483,647.
Common Causes of Overflow Error 6
Microsoft lists three causes for this error: the result of an assignment, calculation or data type conversion is too big for the variable, a value assigned to a property is more than the property can accept, or a calculation with whole numbers gives a result that is too big for an Integer. In real code, it usually shows up in one of these ways:
- Integer math with numbers: when you multiply two small whole numbers like 2000 and 365, VBA calculates the result as an Integer, even if the variable that stores it is a Long (code below this list).
- Row numbers in an Integer: a worksheet has 1,048,576 rows, so storing the last row or a loop counter in an Integer fails as soon as your data goes past row 32,767.
- Byte variables: a Byte can only hold 0 to 255, so a value of 256 or a negative number gives the error.
- Conversion functions: CInt, CByte and CLng give the same error when the value you convert is out of their range, like CInt(40000).
- Zero divided by zero: 0 / 0 in VBA gives the Overflow error, while any other number divided by zero gives the “Division by zero” error (Error 11).
In the following code, the variable is a Long, but you still get the Overflow error, because 2000 * 365 is calculated as an Integer first:
Sub OverflowWithLong()
Dim x As Long
x = 2000 * 365 ' Overflow error
End Sub
To fix it, convert one of the numbers to a Long with CLng, so the whole calculation is done as a Long:
Sub NoOverflow()
Dim x As Long
x = CLng(2000) * 365 ' Returns 730000
End Sub
And here’s the row number case. If your data goes beyond row 32,767, the first line gives the error, so always use Long for row numbers and counters:
Sub LastRow()
Dim lastRow As Long ' Not Integer
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox lastRow
End Sub
How to Deal with Overflow (VBA Error 6)
The way to deal with this error is to have a complete understanding of the VBA Data Types that you need to use while declaring a variable.
You need to keep the range of values in mind when you are using a data type to store a numeric value in a variable.
So, you need to examine the range of the result that you want to store in the variable.
Understanding of data types and the range of values they can store can help you correct this error. It can also help you avoid writing code that runs into the Overflow error.
Choosing the Appropriate Data Type
As we have learned, the common reason for overflow is using a data type that is too small to hold the required value.
For example, use a Long instead of an Integer if you expect the value to exceed 32,767 or be less than -32,768. If you expect even larger values, consider using Double.
Here are the ranges of the numeric data types in VBA, so you can pick the right one:
- Byte: 0 to 255.
- Integer: -32,768 to 32,767.
- Long: -2,147,483,648 to 2,147,483,647.
- LongLong: about -9.2 quintillion to 9.2 quintillion, available only in 64-bit versions of Office.
- Currency: about -922 trillion to 922 trillion, with four decimal places.
- Single: up to about 3.4 x 10^38 (positive or negative).
- Double: up to about 1.8 x 10^308 (positive or negative).
For counters and row numbers, Long is the safest choice, as it covers every row on a worksheet.
Preventing Overflow
Before performing operations that might lead to overflow, test the results:
If num1 > 32767 / num2 Then MsgBox "Operation would cause overflow." Else result = num1 * num2 End If
Test and Validate the Code in Real Situations
Test your code under the conditions you expect it to run in, which helps ensure that any overflow issues are caught during testing.
Use Error Handling
Use error handling routines using On Error statements to manage unexpected overflows:
On Error GoTo ErrorHandler Dim result As Long result = 32767 * 2 Exit Sub ErrorHandler: If Err.Number = 6 Then MsgBox "Overflow error occurred. Please use larger data type." Resume Next End If
In this code, the result variable is a Long, but 32767 * 2 is calculated as an Integer, so the Overflow error occurs and the error handler shows the message. Error handling only tells you that the overflow happened. The real fix is still to change the data type (or use CLng in the calculation).
Take the Help of External Libraries
In cases where VBA’s native capabilities are insufficient, consider calling external libraries (such as using DLL calls) that can handle large numbers.
Before you go that way, try the larger data types VBA already has. Currency works well for money values, LongLong works for very large whole numbers in 64-bit Office, and the Decimal subtype (a Variant converted with CDec) can hold up to 29 digits.
How to Find the Line That Causes the Overflow Error
- First of all, when the error message appears, click the Debug button. VBA highlights the line where the error occurred in yellow.
- After that, hover your mouse over each variable on that line to see its current value, or type ? followed by the variable name in the Immediate Window and press Enter.
- Next, check the data type of each variable in its Dim statement and compare the value with the range of that data type.
- In the end, if the line has a calculation with whole numbers, check if the result is more than 32,767, as that calculation is done as an Integer.
Frequently Asked Questions
Why do I get an Overflow error when my variable is a Long?
Because VBA calculates the right side of the line before it stores the result. If both numbers in a calculation are small whole numbers, the calculation is done as an Integer, and a result over 32,767 gives the error. Use CLng on one of the numbers to fix it.
What is the difference between Error 6 and Error 11?
Error 6 (Overflow) means a value is too big for its data type. Error 11 (Division by zero) means you divided a number by zero. The one exception is 0 / 0, which gives Error 6 in VBA.
Can I ignore the Overflow error with On Error Resume Next?
You can, but the variable won’t get the new value, so your code carries on with a wrong result. It’s better to fix the data type, and use error handling only to show a clear message.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.