VBA Overflow Error (Error 6)

- Written by Puneet

excelchamps-free-courses-puneet-gogia

What is Overflow Error in VBA?

In VBA, Overflow (Error 6) is a run-time error that occurs when you specify a number to the 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 so when you specify a value that is out of this range you get the Overflow run time error.

vba-overflow-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 Overflow run-time error occurs.

Sub myMacro()
Dim iNum As Integer
iNum = 10000000
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 deal with a range of values when you are using a data type to store a numeric value in the variable.

So, you need to examine the range of the result that you want to store in the variable.

Understanding of data types and their range of values to store can help you correct this error. It can also help you to not to write a code in way which runs in 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.

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

Testing and Validation the Code in Real Situation

Test your code under conditions you expect it to run, which helps ensure that any overflow issues are caught during running.

Use Error Handling

Use error handling routines using On Error statements to manage unexpected overflows:

On Error GoTo ErrorHandler

Dim result As Long
result = 32768 * 2

Exit Sub

ErrorHandler:
If Err.Number = 6 Then
    MsgBox "Overflow error occurred. Please use larger data type."
    Resume Next
End If

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.

Last Updated: April 15, 2024