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

Get FREE Advanced Excel Exercises with Solutions!

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

Vba Editor interface

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.

opening of VBA editor in Excel worksheet


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

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.

Rectifying Overflow Error with Byte Data Type

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

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

Rectifying Overflow Error with Integer Data Type

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


3. VBA Overflow Error with Long Data Type

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.

Rectifying Overflow Error with Long Data Type

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.

Code for Error with Integer Data Type in For Loop

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

Formula Breakdown

  • Declares a variable named revenue as a Double data type
  • Sets the range rng1 as the currently selected cells
  • For Each Cell begins a For loop that iterates through each cell in the range rng1
  • Increments the variable Count by 1 for each iteration of the loop
  • Value * rng1.Cells(Count, 2).Value multiplies the value of the current cell by the value in the second column of the same row.
  • Cells(Count, 3).Value = revenue stores the result of the calculation in the third column of the same row.
  • Next Cell repeats the process until all cells in the range have been processed

Therefore, your output will look as given below.

Output of Overflow Error with Integer Data Type in For Loop

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo