Run-time error '6' Overflow

[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

 

[Dave Peterson]

I'd try dimming the variables as Longs or as doubles--not as variants.

Dim var1 as double 'long
Dim var2 as double 'long
....

 

[Dave Peterson]

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

 

[joeu2004]

I'd try dimming the variables as Longs
or as doubles--not as variants.

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 -----

 

[Dave Peterson]

Yeah, I had second thoughts about that, too. That's why I posted the follow up
about disabling events.

 

[joeu2004]

Yeah, I had second thoughts about that, too.
That's why I posted the follow up about disabling
events.

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 -----

 

[Dave Peterson]

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).

 

[iamnu]

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).

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...

 

[Dave Peterson]

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:

 

[joeu2004]

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.

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????

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 -----

 

[Dave Peterson]

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>.)