Overflow error in Progress Bar
 

I got an "Overflow" error in one of my Progress Bar. When debugging the
code, I found out that I am able to reproduce the problem any time. The bar
width is 300 points and I want to show progress 200 times. The sample code
below reproduce the problem all the time. Can someone tell me what is wrong?

By the way, I am using Ms-Office Excel 2003 SP3.


Sub CheckOverflow()
Dim i As Integer
Dim r As Integer
r = 200
On Error Resume Next
For i = 1 To r
Err.Clear
Cells(i, 1) = 300 * (i - 1) / r
If Err Then Cells(i, 1) = Error(Err)
'Call MyMacro()
Next i
End Sub

Overflow error in Progress Bar
 

Try using

Dim i As Long

The overflow occurs when computing the new cell value beyond the limit of
the integer range for i.
--

Regards,
Nigel




"René" wrote in message
...
I got an "Overflow" error in one of my Progress Bar. When debugging the
code, I found out that I am able to reproduce the problem any time. The
bar
width is 300 points and I want to show progress 200 times. The sample
code
below reproduce the problem all the time. Can someone tell me what is
wrong?

By the way, I am using Ms-Office Excel 2003 SP3.


Sub CheckOverflow()
Dim i As Integer
Dim r As Integer
r = 200
On Error Resume Next
For i = 1 To r
Err.Clear
Cells(i, 1) = 300 * (i - 1) / r
If Err Then Cells(i, 1) = Error(Err)
'Call MyMacro()
Next i
End Sub

Overflow error in Progress Bar
 

Works fine
Thanks Nigel

"Nigel" wrote:

Try using

Dim i As Long

The overflow occurs when computing the new cell value beyond the limit of
the integer range for i.
--

Regards,
Nigel




"René" wrote in message
...
I got an "Overflow" error in one of my Progress Bar. When debugging the
code, I found out that I am able to reproduce the problem any time. The
bar
width is 300 points and I want to show progress 200 times. The sample
code
below reproduce the problem all the time. Can someone tell me what is
wrong?

By the way, I am using Ms-Office Excel 2003 SP3.


Sub CheckOverflow()
Dim i As Integer
Dim r As Integer
r = 200
On Error Resume Next
For i = 1 To r
Err.Clear
Cells(i, 1) = 300 * (i - 1) / r
If Err Then Cells(i, 1) = Error(Err)
'Call MyMacro()
Next i
End Sub

Overflow error in Progress Bar
 

The solution to declare the variables as "Long" works fine.
But
I've tried 2 new and very basic tests and I still get the error.

Test #1
Sub CheckOverflow2()
MsgBox 300 * (111 - 1) / 200
End Sub

Test#2
Sub BasicTest()
MsgBox CheckOverflow3()
End Sub

Function CheckOverflow3() As Long
CheckOverflow2 = 300 * (111 - 1) / 200
End Function



"Nigel" wrote:

Try using

Dim i As Long

The overflow occurs when computing the new cell value beyond the limit of
the integer range for i.
--

Regards,
Nigel




"René" wrote in message
...
I got an "Overflow" error in one of my Progress Bar. When debugging the
code, I found out that I am able to reproduce the problem any time. The
bar
width is 300 points and I want to show progress 200 times. The sample
code
below reproduce the problem all the time. Can someone tell me what is
wrong?

By the way, I am using Ms-Office Excel 2003 SP3.


Sub CheckOverflow()
Dim i As Integer
Dim r As Integer
r = 200
On Error Resume Next
For i = 1 To r
Err.Clear
Cells(i, 1) = 300 * (i - 1) / r
If Err Then Cells(i, 1) = Error(Err)
'Call MyMacro()
Next i
End Sub

Overflow error in Progress Bar
 

VB is strange this way. If **all** the numbers you are calculating with are
Integers (that is, a VB Integer... a number between -32566 and +32567), then
VB will try to put the answer into an Integer... if any part of the
calculation (not the final answer, but the sub-calculations that go into
making the final calculation) exceeds what an Integer can hold, then an
overflow error is generated. Multiplication and Division have the same order
of precedence and, in that case, calculations take place from left to right
for the operations with equal precedence. So, in your sub, the 300 gets
multiplied by the (111 - 1), which is 110, before the division by 200 takes
place... 300 * 110 equals 33000 which is larger than an Integer can hold,
hence, an overflow is generated before the division has a chance to reduce
the calculation to a number that will fit in an Integer. Now, if any one
number is made into a numeric data type other than an Integer (this applies
to hard coded numbers as well as Dim'med variables), then the problem is
avoided (because **all** the numbers are not Integers). You can use the CLng
function to force VB to consider a number that would have been an Integer to
be a Long instead. Try your statement this way...

MsgBox CLng(300) * (111 - 1) / 200

--
Rick (MVP - Excel)


"René" wrote in message
...
The solution to declare the variables as "Long" works fine.
But
I've tried 2 new and very basic tests and I still get the error.

Test #1
Sub CheckOverflow2()
MsgBox 300 * (111 - 1) / 200
End Sub

Test#2
Sub BasicTest()
MsgBox CheckOverflow3()
End Sub

Function CheckOverflow3() As Long
CheckOverflow2 = 300 * (111 - 1) / 200
End Function



"Nigel" wrote:

Try using

Dim i As Long

The overflow occurs when computing the new cell value beyond the limit of
the integer range for i.
--

Regards,
Nigel




"René" wrote in message
...
I got an "Overflow" error in one of my Progress Bar. When debugging the
code, I found out that I am able to reproduce the problem any time.
The
bar
width is 300 points and I want to show progress 200 times. The sample
code
below reproduce the problem all the time. Can someone tell me what is
wrong?

By the way, I am using Ms-Office Excel 2003 SP3.


Sub CheckOverflow()
Dim i As Integer
Dim r As Integer
r = 200
On Error Resume Next
For i = 1 To r
Err.Clear
Cells(i, 1) = 300 * (i - 1) / r
If Err Then Cells(i, 1) = Error(Err)
'Call MyMacro()
Next i
End Sub

Overflow error in Progress Bar
 

If **all** the numbers you are calculating with are Integers (that is a VB
Integer... a number between -32566 and +32567)

Okay, and the correct range for VB Integers is -32768 to 32767.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
VB is strange this way. If **all** the numbers you are calculating with
are Integers (that is, a VB Integer... a number between -32566 and
+32567), then VB will try to put the answer into an Integer... if any part
of the calculation (not the final answer, but the sub-calculations that go
into making the final calculation) exceeds what an Integer can hold, then
an overflow error is generated. Multiplication and Division have the same
order of precedence and, in that case, calculations take place from left
to right for the operations with equal precedence. So, in your sub, the
300 gets multiplied by the (111 - 1), which is 110, before the division by
200 takes place... 300 * 110 equals 33000 which is larger than an Integer
can hold, hence, an overflow is generated before the division has a chance
to reduce the calculation to a number that will fit in an Integer. Now, if
any one number is made into a numeric data type other than an Integer
(this applies to hard coded numbers as well as Dim'med variables), then
the problem is avoided (because **all** the numbers are not Integers). You
can use the CLng function to force VB to consider a number that would have
been an Integer to be a Long instead. Try your statement this way...

MsgBox CLng(300) * (111 - 1) / 200

--
Rick (MVP - Excel)


"René" wrote in message
...
The solution to declare the variables as "Long" works fine.
But
I've tried 2 new and very basic tests and I still get the error.

Test #1
Sub CheckOverflow2()
MsgBox 300 * (111 - 1) / 200
End Sub

Test#2
Sub BasicTest()
MsgBox CheckOverflow3()
End Sub

Function CheckOverflow3() As Long
CheckOverflow2 = 300 * (111 - 1) / 200
End Function



"Nigel" wrote:

Try using

Dim i As Long

The overflow occurs when computing the new cell value beyond the limit
of
the integer range for i.
--

Regards,
Nigel




"René" wrote in message
...
I got an "Overflow" error in one of my Progress Bar. When debugging
the
code, I found out that I am able to reproduce the problem any time.
The
bar
width is 300 points and I want to show progress 200 times. The sample
code
below reproduce the problem all the time. Can someone tell me what is
wrong?

By the way, I am using Ms-Office Excel 2003 SP3.


Sub CheckOverflow()
Dim i As Integer
Dim r As Integer
r = 200
On Error Resume Next
For i = 1 To r
Err.Clear
Cells(i, 1) = 300 * (i - 1) / r
If Err Then Cells(i, 1) = Error(Err)
'Call MyMacro()
Next i
End Sub

Overflow error in Progress Bar
 

Very clear.
Thanks Rick.
Have a good day.


"Rick Rothstein" wrote:

If **all** the numbers you are calculating with are Integers (that is a VB
Integer... a number between -32566 and +32567)

Okay, and the correct range for VB Integers is -32768 to 32767.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
VB is strange this way. If **all** the numbers you are calculating with
are Integers (that is, a VB Integer... a number between -32566 and
+32567), then VB will try to put the answer into an Integer... if any part
of the calculation (not the final answer, but the sub-calculations that go
into making the final calculation) exceeds what an Integer can hold, then
an overflow error is generated. Multiplication and Division have the same
order of precedence and, in that case, calculations take place from left
to right for the operations with equal precedence. So, in your sub, the
300 gets multiplied by the (111 - 1), which is 110, before the division by
200 takes place... 300 * 110 equals 33000 which is larger than an Integer
can hold, hence, an overflow is generated before the division has a chance
to reduce the calculation to a number that will fit in an Integer. Now, if
any one number is made into a numeric data type other than an Integer
(this applies to hard coded numbers as well as Dim'med variables), then
the problem is avoided (because **all** the numbers are not Integers). You
can use the CLng function to force VB to consider a number that would have
been an Integer to be a Long instead. Try your statement this way...

MsgBox CLng(300) * (111 - 1) / 200

--
Rick (MVP - Excel)


"René" wrote in message
...
The solution to declare the variables as "Long" works fine.
But
I've tried 2 new and very basic tests and I still get the error.

Test #1
Sub CheckOverflow2()
MsgBox 300 * (111 - 1) / 200
End Sub

Test#2
Sub BasicTest()
MsgBox CheckOverflow3()
End Sub

Function CheckOverflow3() As Long
CheckOverflow2 = 300 * (111 - 1) / 200
End Function



"Nigel" wrote:

Try using

Dim i As Long

The overflow occurs when computing the new cell value beyond the limit
of
the integer range for i.
--

Regards,
Nigel




"René" wrote in message
...
I got an "Overflow" error in one of my Progress Bar. When debugging
the
code, I found out that I am able to reproduce the problem any time.
The
bar
width is 300 points and I want to show progress 200 times. The sample
code
below reproduce the problem all the time. Can someone tell me what is
wrong?

By the way, I am using Ms-Office Excel 2003 SP3.


Sub CheckOverflow()
Dim i As Integer
Dim r As Integer
r = 200
On Error Resume Next
For i = 1 To r
Err.Clear
Cells(i, 1) = 300 * (i - 1) / r
If Err Then Cells(i, 1) = Error(Err)
'Call MyMacro()
Next i
End Sub

Overflow error in Progress Bar
 

Another way.
Although Type of i and r are interger, You could use a formula like this
without causing error.

Cells(i, 1) = 300 * ((i - 1) / r)

Keiji

René wrote:
Very clear.
Thanks Rick.
Have a good day.


"Rick Rothstein" wrote:

If **all** the numbers you are calculating with are Integers (that is a VB
Integer... a number between -32566 and +32567)
Okay, and the correct range for VB Integers is -32768 to 32767.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
VB is strange this way. If **all** the numbers you are calculating with
are Integers (that is, a VB Integer... a number between -32566 and
+32567), then VB will try to put the answer into an Integer... if any part
of the calculation (not the final answer, but the sub-calculations that go
into making the final calculation) exceeds what an Integer can hold, then
an overflow error is generated. Multiplication and Division have the same
order of precedence and, in that case, calculations take place from left
to right for the operations with equal precedence. So, in your sub, the
300 gets multiplied by the (111 - 1), which is 110, before the division by
200 takes place... 300 * 110 equals 33000 which is larger than an Integer
can hold, hence, an overflow is generated before the division has a chance
to reduce the calculation to a number that will fit in an Integer. Now, if
any one number is made into a numeric data type other than an Integer
(this applies to hard coded numbers as well as Dim'med variables), then
the problem is avoided (because **all** the numbers are not Integers). You
can use the CLng function to force VB to consider a number that would have
been an Integer to be a Long instead. Try your statement this way...

MsgBox CLng(300) * (111 - 1) / 200

--
Rick (MVP - Excel)


"René" wrote in message
...
The solution to declare the variables as "Long" works fine.
But
I've tried 2 new and very basic tests and I still get the error.

Test #1
Sub CheckOverflow2()
MsgBox 300 * (111 - 1) / 200
End Sub

Test#2
Sub BasicTest()
MsgBox CheckOverflow3()
End Sub

Function CheckOverflow3() As Long
CheckOverflow2 = 300 * (111 - 1) / 200
End Function



"Nigel" wrote:

Try using

Dim i As Long

The overflow occurs when computing the new cell value beyond the limit
of
the integer range for i.
--

Regards,
Nigel




"René" wrote in message
...
I got an "Overflow" error in one of my Progress Bar. When debugging
the
code, I found out that I am able to reproduce the problem any time.
The
bar
width is 300 points and I want to show progress 200 times. The sample
code
below reproduce the problem all the time. Can someone tell me what is
wrong?

By the way, I am using Ms-Office Excel 2003 SP3.


Sub CheckOverflow()
Dim i As Integer
Dim r As Integer
r = 200
On Error Resume Next
For i = 1 To r
Err.Clear
Cells(i, 1) = 300 * (i - 1) / r
If Err Then Cells(i, 1) = Error(Err)
'Call MyMacro()
Next i
End Sub