VBA "Overflow occurred." Error cause and remedy (runtime error '6′:)

Cause of Error

This error occurs when the data type is not in a format that can be handled, or when the data type exceeds the range of numbers that can be handled.

Let's reproduce the error first.

As shown in the figure above, the "B3The number in the "" cell is the same as the number in the "B4To the "" cell.Code to copyI have assembled a

Sub TEST()
    Dim a As Integer
    a = Range("B3")
    Range("B4") = a
End Sub

When I run this code

Thus, "Overflow occurred." and an error occurred.

This is called "B3The number (33000) entered in the cell "Integer" is in the range of numbers of data type "Integer" (32,767The error occurred because the number of the

Check the table below for the range of values for each data type.

data type	Scope.
Integer	-32,768 ~ 32,767
Long	-2,147,483,648 to 2,147,483,647
Single	-3.40...x1038~3.40...×1038(Fewer)
double	-1.79...x10308~1.79...×10308(Fewer)
Variant	All data goes in.

For the data type of a variable, seeAbout Variable Types Excel(Excel) Macro(VBA)Please refer to this article for a detailed explanation.

How to find the error location

In most cases, we do not know where in the long code the error is occurring.

So, first press "debug" when an error occurs.

Then you can see where the error is occurring like thisYellow highlightsThe following is an example of the use of the "M" in the "M" in the "M".

Yellow highlightsIf you check the range of the "B3" cell, you will see that an error occurs when assigning the value of the "B3" cell to the variable "a", which is declared as an Integer type.

We then checked the "B3" cell and found that it contains the number "33000".

Let's take another look at the range of numbers that can be stored in the data type.

data type	Scope.
Integer	-32,768 ~ 32,767
Long	-2,147,483,648 to 2,147,483,647
Single	-3.40...x1038~3.40...×1038(Fewer)
double	-1.79...x10308~1.79...×10308(Fewer)
Variant	All data goes in.

Integer" can store a maximum of32,767It turns out that the integer is up to "33,000". Therefore, we can hypothesize that an error occurred while trying to store 33,000.

So, let's test the previous code by changing the number to "32,000" for the data range.

Then, as shown in the figure above, the value could be copied to the "B4" cell without generating an error. This confirmed the cause of the error.

Fix errors

Since this error occurred because the target number was larger than the range of numbers that can be stored, changing the data type to "Long," which has a wider data range, should eliminate the error.

So, we can thus replace "Integer" with "Integer".Longand run this code again.

Sub TEST()
    Dim a As Long
    a = Range("B3")
    Range("B4") = a
End Sub

Then, the same value as the "B3" cell was entered into the "B4" cell without any error.

Measures to prevent errors from occurring

Change the data type

Check to see if the data type or range can be handled

If it is a number, try "long" (long integer).

If it is a small number, try "Double" (long minority).

If that does not work, try "string" (character string).

If that does not work, then "variant" is a data type that can contain anything.

If the "overflow" still occurs

If it does not work for any data type, the formatting may be bad.

For example, if a cell is formatted as a date, and a serial value (2958466 or higher) for a date after January 1, 10000 AD is entered into the cell, it will be "######".

Even though the number "2958466" is a numeric range of the "Long" data type, executing the code in this state would result in an "overflow." The result is "Overflowed".

In this case, even if I set the data type to "Variant," which should be able to contain any type of data, I still got an "Overflowed. error.

In this case, changing the cell formatting to "standard" or "numeric" will eliminate the error.

Try to copy and paste the format of the cells that are displayed correctly.

Right-click on the "B4" cell and click "Copy" from the menu that appears.

Right-click on the "B3" cell and select "Formatting" from the menu that appears to paste the format.

Remove sheet protection because it cannot be corrected properly if sheet protection is applied.

If you are unable to cancel theForgot Password] How to remove sheet protection Excel (Excel).