How to Solve Overflow Error in VBA (4 Easy Methods)

Have you ever been stuck in the middle of your code where you are continuously trying to make your algorithm better but do not run eventually due to the dreadful error? Like many other VBA Errors, Overflow Error is one of them which can make your code miserable enough. However, there is no need to be worried about it once you learn how to deal with such errors. This article will demonstrate how to solve the Overflow Error in VBA.

What Is Overflow Error in VBA?

In VBA, an overflow error occurs when the result of a calculation or operation exceeds the maximum value that can be stored in a data type.

For example, the Integer data type can store values between -32,768 and 32,767 in VBA. An overflow error will occur if you try to assign a value outside this range to an Integer variable or perform an operation that results in a value outside this range.

How to Launch VBA Editor in Excel

To open the VBA code editor in Excel you can utilize the keyboard shortcut. Let’s see the process.

• Press Alt + F11 to open your Microsoft Visual Basic.
• Then press Insert > Module to open a blank module.

How to Solve Overflow Error in VBA: 4 Types of Problems with Solutions

Overflow errors in VBA occur when the result of a mathematical calculation exceeds the maximum value that can be stored in a particular data type, like when we do financial calculations, data analysis, or scientific research. In this article, we will cover all possible ways of occurring Overflow Error and its solution.

1. Overflow Error with Byte Data Type

The Byte data type in VBA is used to store integer values between 0 and 255, and it requires 1 byte of memory. An overflow error with the Byte data type can occur when you try to store a value outside the range.

For Example, you want to add two values and declare your variable datatype as Byte. Now, if the sum value of the two numbers exceeds the Byte data type limit, then a Run-time error ‘6’ will occur due to the Overflow Error.

However, you can solve the issue by simply changing your data type from Byte to Long or Double if necessary.

Sub Overflow_Byte_Data()
Dim Number As Double
num1 = 200
num2 = 56
Number = num1 + num2
MsgBox Number
End Sub

2. VBA Overflow Error with Integer Data Type

“How can I solve the VBA overflow caused by the 32765 limit?” – If this question had ever come to your mind then, by the end of this section you will know the answer. In VBA, the Integer data type is used to store whole numbers between -32,768 and 32,767, and it requires 2 bytes of memory. An overflow error with the Integer data type can occur when you try to store a value outside the range.

For Example, you are performing a calculation that results in a value outside the range of -32,768 to 32,767 (say, 32000+800 = 32800). Then, you have to face the Overflow Error for sure.

So the remedy for the given scenario is as same as before. Just change the variable data type from Integer to Double.

Sub Overflow_Integer_Data()
Dim Number As Double
num1 = 32000
num2 = 800
Number = num1 + num2
MsgBox Number
End Sub

Read More: How to Handle Excel VBA On Error GoTo 0 

3. VBA Overflow Error with Long Data Type

The Long data type in VBA is used to store whole numbers within a range of -2,147,483,648 to 2,147,483,647 and it requires 4 bytes of memory. When you attempt to store or calculate a value outside of this range, an overflow error may occur.

Like the previous example, the solution to this problem is changing the data type to Double.

Sub Overflow_Long_Data()
Dim Number As Double
num1 = 2147483647
num2 = 1
Number = num1 + num2
MsgBox Number
End Sub

4. Overflow Error with Integer Data Type in For Loop

Now consider a real case scenario in which you deploy a For Loop to calculate your monthly revenue based on some given quantity and price. However, you declare your datatype as Integer. So you will definitely be encountered the Overflow Error once your calculation exceeds the -32,768 to 32,767 range.

To solve this problem, change the datatype Integer to Double to allot more digit space in your memory.

Sub For_Loop_with_Integer_Data()
Dim revenue As double
Set rng1 = Selection
For Each cell In rng1
Count = Count + 1
revenue = cell.Value * rng1.Cells(Count, 2).Value
rng1.Cells(Count, 3).Value = revenue
Next cell
End Sub

Therefore, your output will look as given below.

Read More: Excel VBA Error Handling in Loop

Frequently Asked Question

• How do I resolve a VBA error?

Using ‘On Error Resume Next‘ in your code allows any errors to be ignored. But you should be cautious when using this Error Handling technique. As it completely disregards any errors that occur. So it becomes difficult to identify which errors need to be fixed.

• Is Overflow an Error or Fault?

In the context of software development, an overflow is typically considered an error rather than a fault. As it is typically caused by a mistake in the programmer’s code rather than a flaw in the software itself.

• Is Overflow a Runtime Error?

Yes, The Overflow is a type of runtime error that occurs when a program tries to process a value that is outside the range of its assigned data type. The Overflow error typically occurs during the execution of the program, at runtime, rather than during the compilation or debugging stages.

• How can we avoid overflow while dealing with large amounts of data?

To catch overflow errors while dealing with a large amount of data, use error handling techniques such as the On Error statement. This will allow you to identify the error and handle it appropriately, rather than allowing it to crash your program. Besides changing the variable type as a whole might also be handy if you are ok with your memory space.

Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.

Conclusion

In conclusion, dealing with overflow errors in VBA can be frustrating, but with the right approach, it is possible to solve them. By choosing an appropriate data type or by breaking down large calculations into smaller pieces, you can avoid overflow errors in your code. Hope this article gave you a clear idea of How to Solve Overflow Error in VBA. However, If you have any queries from the above discussion, feel free to comment below and we will get back to you soon.

Related Articles

• On Error Resume Next: Handling Error in Excel VBA
• Excel VBA: Turn Off the “On Error Resume Next”
• Excel VBA Error Handling Best Practice