Run-time error '6' Overflow

  • Thread starter Thread starter iamnu
  • Start date Start date
I

iamnu

The following code is giving me the Run-time error '6' Overflow, and
specifically at the code "ans = var1 * var2".

And I don't understand why, when stepping through this code, it keeps
jumping in and out of the procedure.

I obviously don't know what I'm doing.

FYI, cells D3 and F3 have the following code in them: =RANDBETWEEN
(1,9)

I hope someone can explain...


Private Sub Worksheet_Change(ByVal Target As Range)
Dim var1, var2, ans, tgt
If Target.Column = 8 Then
tgt = Target.Value
var1 = Range("D3").Value
var2 = Range("F3").Value
ans = var1 * var2
Range("D4").Value = var1
Range("F4").Value = var2
Range("H4").Value = tgt
If tgt = var1 * var2 Then
Range("I4").Value = "Correct"
Else
Range("I4").Value = "Wrong"
End If
End If
Calculate
End Sub
 
I'd try dimming the variables as Longs or as doubles--not as variants.

Dim var1 as double 'long
Dim var2 as double 'long
....
 
And I'd stop the worksheet_change event from calling itself:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim var1 as double
dim var2 as double
dim ans as double
dim tgt as double
If Target.Column = 8 Then
tgt = Target.Value
var1 = Range("D3").Value
var2 = Range("F3").Value
ans = var1 * var2
application.enableevents = false
Range("D4").Value = var1
Range("F4").Value = var2
Range("H4").Value = tgt
If tgt = var1 * var2 Then
Range("I4").Value = "Correct"
Else
Range("I4").Value = "Wrong"
End If
application.enableevents = true
End If
'do you really need to calculate?
'stop any calculate event from firing
application.enableevents = false
Calculate
application.enableevents = true
End Sub
 
I'd try dimming the variables as Longs
or as doubles--not as variants.
Click to expand...

I agree that's more efficient. But why would it correct the problem?

In fact, I failed to reproduce the OP's problem when I cut-and-pasted
the original macro.


----- original posting -----
 
Yeah, I had second thoughts about that, too. That's why I posted the follow up
about disabling events.
 
Yeah, I had second thoughts about that, too.
That's why I posted the follow up about disabling
events.
Click to expand...

And I agree that disabling events is needed in order to make the macro
behave in a sane fashion.

But without, I still do not get an overflow error, as the OP claims.

On the other hand, without disabling events, I do not understand why
the recursive invoking of the macro ever stops. I presume it is
because I hit an iteration or recursive-call limit.

I am using Excel 2003. Are you suggesting that the "overflow" error
results from hitting the recursive-call limit in some other Excel
revisions?

But if that's the case, why does the OP say that the error occurs on
the multiplication statement? I would expect it on or after the
statement that assigns to H4 or on the Sub statement.

Well, maybe the "Greg House Rule" applies here :-).


----- original posting -----
 
But we don't know what the value is that is being updated in the cell. I
thought maybe the recursion caused that calculation to get pretty large.

Or maybe there's a _calculation event that's aggrevating the situation????

And excel does have some limit on how many times the event will call itself
(self protection???).

FWIW, I didn't get the overflow error in my limited testing either. But the two
obvious problems needed to be addressed (in my thinking).
 
But we don't know what the value is that is being updated in the cell.  I
thought maybe the recursion caused that calculation to get pretty large.  

Or maybe there's a _calculation event that's aggrevating the situation????

And excel does have some limit on how many times the event will call itself
(self protection???).  

FWIW, I didn't get the overflow error in my limited testing either.  But the two
obvious problems needed to be addressed (in my thinking).
Click to expand...

Well, I appreciate the conversation regarding this problem. While I
don't understand most of it, I can report that the procedure that Dave
Peterson provided does in fact, solve the problem. Thanks so much for
your help. The "Disabling of Events" was the problem, I guess, and I
did learn something from this, because as I said, the procedure just
calling itself over and over and over, and I didn't know how to stop
that.

Thanks again...
 
Glad the problem went away.

Can you share the value you typed into column H (and its address) that caused
the error?

And did you have a _calculate event that was firing?

(just curious)



iamnu wrote:
 
But we don't know what the value is that is being
updated in the cell. I thought maybe the recursion
caused that calculation to get pretty large.
Click to expand...

You could be right. Initially, I took the OP at his/her word; now I
am suspicious. But assuming the OP is right ....

The OP said the overflow error occurred on the statement "ans = var1 *
var2". That is effectively D3*F3. It has nothing to do with the
target cell that caused the change event.

The OP said that D3 and F3 each contain =RANDBETWEEN(1,9). So D3 and
F3 should each contain an integer between 1 and 9.

As for the target cell, I inferred that it should (but might not)
contain the formula =D3*F3. This is based on the fact that "if tgt =
var1 * var2" returns "correct" when true.

I opined that the OP is trying to figure out why the cell with =D3*F3
(I call it H3; it must be in column 8) does not always equal D3 and
F3. (If that's the case, all he/she needs to do is ask. I'm sure
either one of us can explain it to him.)
Or maybe there's a _calculation event that's
aggrevating the situation????
Click to expand...

But how could that cause an overflow error on the statement "ans =
var1 * var2" per se? (If we assume that the OP is right about that.)

That's a rhetorical question. Given the fact that you changes
remedied the problem, I think it is safe to conclude that there was
some misinformation in the original posting.

This discussion was valuable to me because I had never worked with
event macros before. Thanks for you indulgence -- and the solution.


----- original posting -----
 
I misremembered the details. I thought that tgt was a factor in the line that
blew up.

(But you're right about the conclusion, too <vbg>.)
 
Back
Top