Excel VBA OverFlow Error
Errors are part and parcel of any coding language but finding why that error is coming is what makes you stand apart from the crowd in interviews. Errors are not strange to VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. However, errors are not intentional, so finding the cause for the error is a hard task. In VBA, we have some predefined errors, and knowing about them makes you quickly fix the bug. This article will show you the RUN TIME ERROR 6: Overflow. Follow the full article to learn about the error, the reasons for the VBA “Overflow error,” and how to fix them.
What is Run Time Error 6: Overflow Error in VBA?
When we declare the variable, we assign a data type to them. We should be completely aware of each data type’s pros and cons—this is where “Run Time Error 6: Overflow” comes into the picture. When we overload the data type with a value, which is more than the capacity of the data type, then we will get this error.
You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA OverFlow Error (wallstreetmojo.com)
Number = 240
It is the general overview of the Run Time Error 6: Overflow. Next, we will see some of the examples in detail.
Examples of Run Time Error 6: OverFlow in VBA
Let us see some examples of VBA overflow errors in Excel.
Knowing the pros and cons of the VBA data typeVBA Data TypeData type is the core character of any variable, it represents what is the type of value we can store in the variable and what is the limit or the range of values which can be stored in the variable, data types are built-in VBA and user or developer needs to be aware which type of value can be stored in which data type. Data types assign to variables tells the compiler storage size of the variable.read more we will use is important. For example, look at the below code.
Code:
For the variable “Number,”we have assigned the value as 256. Therefore, we will get the below error when we run this code.
Example 2: VBA OverFlow Error with Integer Data Type
VBA integerVBA IntegerIn VBA, an integer is a data type that may be assigned to any variable and used to hold integer values. In VBA, the bracket for the maximum number of integer variables that can be kept is similar to that in other languages. Using the DIM statement, any variable can be defined as an integer variable.read more is a data type that can hold values from -32768 to 32767. For example, look at the below code.
Sub OverFlowError_Example2()
Dim MyValue As Integer
MyValue = 25656
MsgBox MyValue
End Sub
When we run this code, we will get the variable “MyValue” value in the message box, i.e., 25656.
Now, we will reassign the number to the variable as “45654.”
Sub OverFlowError_Example2()
Dim MyValue As Integer
MyValue = 45654
MsgBox MyValue
End Sub
Now, if I try to run the code, it will cause an error because the data type we have declared can only hold the maximum of 32767 for positive numbers, and for negative numbers, the limit is -32768.
Example 3: VBA OverFlow Error with Long Data Type
The Long data type is the most often used in Excel VBA. This can hold values from –2,147,483,648 to 2,147,486,647. Anything above that will cause an error.
Sub OverFlowError_Example3()
Dim MyValue As Long
MyValue = 5000 * 457
MsgBox MyValue
End Sub
It will cause an overflow error.
We need to use the function CLNG in VBACLNG In VBAVBA CLng or “VBA Convert to Long” is an in-built Excel function that facilitates converting the numerical values or data exceeding the long data type limit into the acceptable data type.read more to fix this issue. Below is an example of the same.
Sub OverFlowError_Example3()
Dim MyValue As Long
MyValue = CLng (5000) * 457
MsgBox MyValue
End Sub
It should work fine.
It is the overview of the Run Time Error 6: Overflow.We must be completely aware of the data types to solve this error. So go back to basics, do the basics right, then everything will fall in place.
You can download this VBA Overflow Error Excel Template here – VBA OverFlow Error Excel Template
Recommended Articles
This article has been a guide to VBA Overflow Error. Here, we learn how Runtime Overflow Error 6 occurs in Excel VBA and how to handle this error, along with practical examples and a downloadable template. Below are some useful Excel articles related to VBA: –
- VBA Pivot TableClear Contents in VBAExcel VBA On Error Goto 0How to Delete Files using VBA Code?
