The Overflow Error is one you are likely to encounter when coding with VBA, like in the video above. This article will demonstrate how to solve the Overflow Error in VBA in various cases.
What Is an 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
The quickest way to open the VBA code editor in Excel is to use the keyboard shortcut:
- Press Alt + F11 to open the Microsoft Visual Basic window.
- Then press Insert > Module to open a blank module.
How to Solve an 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, Let’s look at some examples with different data types.
Example 1 – Overflow Error with the 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 will occur when you try to store a value outside this 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 an Overflow Error.
Solve the issue by simply changing the 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 SubExample 2 – Overflow Error with the Integer Data Type
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 will occur when you try to store a value outside this 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). You will face an Overflow Error for sure.
The remedy 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
Example 3 – Overflow Error with the 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. Whenever you attempt to store or calculate a value outside of this range, an overflow error will occur.
Like in the previous examples, 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 SubExample 4 – Overflow Error with the Integer Data Type in a For Loop
Consider a scenario in which you deploy a For Loop to calculate a monthly revenue based on some given quantity and price. However, you declare your datatype as Integer. You will definitely encounter an Overflow Error once your calculation exceeds the -32,768 to 32,767 range.
To solve this problem, change the data type from 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 SubCode 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.
The output will look like the image below.
Read More: Excel VBA Error Handling in Loop
Frequently Asked Questions
- 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 when 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. Changing the variable type as a whole might also be handy, if you can live with the reduction in memory space.
Download Practice Workbook
