Re: Run-time error '6' Overflow

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Mar 7, 1:44 pm, Dave Peterson <peter...@xxxxxxxxxxxxxxxx> 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.

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

On Mar 7, 1:44 pm, Dave Peterson <peter...@xxxxxxxxxxxxxxxx> 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.  

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





joeu2004 wrote:

On Mar 7, 10:05 am, Dave Peterson <peter...@xxxxxxxxxxxxxxxx> wrote:
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 -----

On Mar 7, 10:05 am, Dave Peterson <peter...@xxxxxxxxxxxxxxxx> wrote:
Yeah, I had second thoughts about that, too.  That's why I posted the follow up
about disabling events.

joeu2004 wrote:

On Mar 7, 7:30 am, Dave Peterson <peter...@xxxxxxxxxxxxxxxx> wrote:
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 -----

On Mar 7, 7:30 am, Dave Peterson <peter...@xxxxxxxxxxxxxxxx> wrote:
I'd try dimming the variables as Longs or as doubles--not as variants.

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

iamnu wrote:

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

--

Dave Peterson

--

Dave Peterson
.

Relevant Pages

  • Re: Anti-science creationist posters
    ... And if I could do the hyperincursive math, I wouldnt be here arguing, ... cells, which in turn influence the state of the cell itself). ... behavior in their recursive calculation. ... cannot be transformed to recursion." ...
    (talk.origins)
  • Re: Recursive Algorithm
    ... To add the points to the grid ive generated a recursive algorithm to loop over the points and calculate their grid cells and then place them within the grid cell. ... Maximum recursion limit of 500 reached. ...     %% calculate the cell X and Y values for each point ...
    (comp.soft-sys.matlab)
  • Re: Attn: Mr. Mook
    ... energy into the atmosphere, so as to maintain an artificially inflated ... dramatically reducing the cost of PV materials. ... the PV cell at a rate of 110 watts per square centimeter. ...
    (sci.energy.hydrogen)
  • Re: Chez Watt Re: Common ancestor between man and ape
    ... 'Cell' is a word with a broad range of applications, ... the word "ape" by scientists in their own scientific context. ... substances placed in an electrolyte. ...
    (talk.origins)
  • Re: Chez Watt Re: Common ancestor between man and ape
    ... 'Cell' is a word with a broad range of applications, ... the word "ape" by scientists in their own scientific context. ... substances placed in an electrolyte. ...
    (talk.origins)