PDA

View Full Version : VBA Overflow error



Abhijeet
02-28-2011, 05:50 AM
Hi

I am trying to run this VBA code...but getting overflow error:


Sub xyz()

Dim i As Integer
Dim j As Integer
Dim Count As Double


For i = 0 To 100
For j = 0 To 10000
Count = (i * i) + (j * j) + Application.WorksheetFunction.Power((i * i) + (j * j), 0.5)
Next j
Next i

MsgBox Count
End Sub

On debugging i found that its getting overflowed inside the second for loop at the Count = statement.
Also the values at the time of overflow using watch was : Count = 32942, i=0,j=182....which basically means 181 iterations in the inner loop has been through and it gets stuck at 182nd iteration....I am not able to understand wat is overflowing...can someone plz help with the reason and the remedy?

Thanks
Abhijit

mdmackillop
02-28-2011, 06:18 AM
Your loop is not contributing to Count as it is overwritten each time. Only the final loop is used.

Sub xyz()

Dim i As Long
Dim j As Long
Dim Count As Double
For i = 0 To 100
For j = 0 To 10000
Count = (i * i) + (j * j) + Application.WorksheetFunction.Power((i * i) + (j * j), 0.5)
Next j
Next i
MsgBox Count
End Sub

Abhijeet
02-28-2011, 06:35 AM
Ya i know that..i have to implement something similar elsewhere...so was testing with this snippet...Count may be overwritten...but why is there an overflow? The problem is with the overflow at that step..

GTO
02-28-2011, 07:57 AM
It is because of (j * j), which exceeds an Integer's capability.

Use Longs like Malcom suggested.

Abhijeet
02-28-2011, 10:26 AM
Thanks a lot for pointing it out..just wanna know if there is a way to handle numbers exceeding the Long datatype. Is a larger datatype available in VBA? If so how can it be used...most of the computations I am trying to implement have numbers exceeding the long datatype and hence ending in overflows...is there a way out for that in VBA?

Paul_Hossler
02-28-2011, 10:47 AM
You can use the Currency data type possibly


Sub BigNumbers()
'Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging
'in value from -2,147,483,648 to 2,147,483,647
Dim L As Long

'Currency variables are stored as 64-bit (8-byte) numbers in an integer format,
'scaled by 10,000 to give a fixed-point number with 15 digits to the left of the
'decimal point and 4 digits to the right. This representation provides a range
'of -922,337,203,685,477.5808 to 922,337,203,685,477.5807

Dim C As Currency
C = 922337203685476#
C = C + 1
MsgBox Format(C, "#,##0")
End Sub


Paul

mdmackillop
02-28-2011, 11:35 AM
Data Type Summary


The following table shows the supported data types, including storage sizes and ranges.

Data type Storage size Range
Byte 1 byte 0 to 255
Boolean 2 bytes True or False
Integer 2 bytes -32,768 to 32,767
Long
(long integer) 4 bytes -2,147,483,648 to 2,147,483,647
Single
(single-precision floating-point) 4 bytes -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Double
(double-precision floating-point) 8 bytes -1.79769313486231E308 to
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Currency
(scaled integer) 8 bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal 14 bytes +/-79,228,162,514,264,337,593,543,950,335 with no decimal point;
+/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is
+/-0.0000000000000000000000000001
Date 8 bytes January 1, 100 to December 31, 9999
Object 4 bytes Any Object reference
String
(variable-length) 10 bytes + string length 0 to approximately 2 billion
String
(fixed-length) Length of string 1 to approximately 65,400
Variant
(with numbers) 16 bytes Any numeric value up to the range of a Double
Variant
(with characters) 22 bytes + string length Same range as for variable-length String
User-defined
(using Type) Number required by elements The range of each element is the same as the range of its data type.



Note Arrays of any data type require 20 bytes of memory plus 4 bytes for each array dimension plus the number of bytes occupied by the data itself. The memory occupied by the data can be calculated by multiplying the number of data elements by the size of each element. For example, the data in a single-dimension array consisting of 4 Integer data elements of 2 bytes each occupies 8 bytes. The 8 bytes required for the data plus the 24 bytes of overhead brings the total memory requirement for the array to 32 bytes.

A Variant containing an array requires 12 bytes more than the array alone.

Note Use the StrConv function to convert one type of string data to another.

mdmackillop
02-28-2011, 11:54 AM
BTW j*j is not being assigned to an Integer thype but to a Double, which is certainly large enough. I don't see the reason for the error.
Changing the data slightly and the overflow does not occur
Sub abc()

Dim j As Integer
Dim Count As Double

j = 10000

Count = (10 ^ 4) * (10 ^ 4)
'or even
Count = (10 ^ 154) * (10 ^ 154)

'but this fails
Count = j * j

End Sub

Frosty
02-28-2011, 03:21 PM
It's so simple to reproduce, this is probably a bug at the core of VBA.

Notice that if you change the line to

Count = cdbl(j) * cdbl(j)

you will not get the error.

Looks like it has something to do with the order of operations and how variables get assigned. Machines don't necessarily think the same way as we do... maybe a real programmer will say "oh, obviously-- the first variable in a mathematical operation is assigned the entire value before that value is passed through the equation."

Who knows... pass it along to microsoft, and either use bigger data types or convert the data types in the particular equation.

Interesting little bug.

mancubus
02-28-2011, 03:47 PM
very interesting indeed...

below all produce RTE 6 whereas single (up to double's highest) not.

Sub lll()

Dim j As Long
Dim Count As Double

j = 2147483000
Count = j + j
MsgBox Count

End Sub

Sub bbb()

Dim j As Byte
Dim Count As Double

j = 250
Count = j + j
MsgBox Count

End Sub

Sub ccc()

Dim j As Currency
Dim Count As Double

j = 922337203685477#
Count = j + j
MsgBox Count

End Sub

Frosty
02-28-2011, 04:00 PM
A little internet research shows that mathematical operations are carried out using the largest memory space utilized "somewhere" in the operation.

So integerVariable * integerVariable still can not be greater than a single integer variable... even if you subsequently assign the results of the operation to a larger data-type.

So even the following in the immediate window will give you an error:
?10000*33
but
?100000*33
will not.

(because the first operation involves two integers, but the result is 33,000, which is above the allowable values for an integer-- but the second one involves a long times an integer... and the result is still within the ability for a long to hold.

In the absence of explicit declaration, VBA will assign the smallest necessary data type to a number, but it won't figure out what is needed for the result of the entire equation. Interesting limitation.

So the take-away here is that you need at least one "big" datatype in your equation if you don't want to run the risk of having an overflow error, and that after integer you've got (in order) long, single, double and currency (in VBA).

Good little lesson.