VBA Bug or Programming Error?
merjet
error arises.
HTH.
Merjet
Jay Petrulis
I get the error when I add the 1* outside term. The 1* is
not necessary and the function works if you remove it.
funcy1 = 1 + (1 + 1 / (1 + 1 / x ^ 2))
Is this part of a longer recursive series? If this is the
final equation, it is very confusing to read (to me). I
find it much better to restate the equation.
funcy1 = 1+(2*x^2+1)/(x^2+1)
It is longer, but much easier to read as there is only one
division. Both returned 2.5 for funcy1(1).
I don't know why the 1*(expression) is throwing the
error. Since funcy3(1) works, it appears that Excel is
misinterpreting the x data type when you dim as double.
This is really weird.
Regards,
Jay
>-----Original Message-----
>Is there a bug in VBA or a programming error in the
following:
>
>Function funcy1(x As Double) As Double
>funcy1 = 1 + 1 * (1 + 1 / (1 + 1 / x ^ 2))
>End Function
>
>Function funcy2(x As Double) As Double
>funcy2 = 1 + 1 * (1 + 1 / (1 + 1 / (x * x)))
>End Function
>
>Function funcy3(x As Variant) As Double
>funcy3 = 1 + 1 * (1 + 1 / (1 + 1 / x ^ 2))
>End Function
>
>The results are:
>funcy1(1)=#VALUE
>funcy2(1)=2.5
>funcy3(1)=2.5
>
>Can someone explain the funcy1 result?
>.
>
J.E. McGimpsey
three functions - no overflow, whether called from the immediate
window, another VBA function, or the worksheet...
In article <3hsfauk0vof0b1cs9...@4ax.com>, berger
<ber...@gtemail.net> wrote:
> Jay,
>
> I changed all the constants in the expression I was evaluating to 1s
> to illustrate the problem, so, unfortunately, removing the 1* wouldn't
> solve the problem.
>
> Changing x^2 to x*x works as does dim x as variant. If funcy1 is
> evaluated in the immediate window, it produces an overflow error.
>
> I agree. This is really weird.
>
> Thanks for your comments.
Chip Pearson
category of "bug". The problem is that you're getting an overflow error. You
*shouldn't* get an overflow, because the all the intermediate calculation should
be carried out as doubles (since x is itself declared as a double).
Oddly, by omitting the first "1 +" allows the function to work. I.e.,
funcy1 = 1 * (1 + 1 / (1 + 1 / x ^ 2))
Debug.Print TypeName(1 * (1 + 1 / (1 + 1 / x ^ 2)))
works fine. This evaluates correctly as a double, and so explicit coercion to a
a double *should* work. E.g., the following should work (though it the CDbl's
shouldn't be required)
funcy1 = CDbl(1) + CDbl(1 * (1 + 1 / (1 + 1 / x ^ 2)))
Strangely, this doesn't work either. The same problems arose in VB6 as well as
VBA.
I think this goes in the "bug" category.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"berger" <ber...@gtemail.net> wrote in message
news:0oifau0ejult02a1t...@4ax.com...
TroyW
With:
funcy1 = 1 * (1 + 1 / (1 + 1 / x ^ 2)) + 1
-OR-
funcy1 = 1 * (1 + 1 / (1 + 1 / x ^ 2))
funcy1 = funcy1 + 1
- OR -
Function funcy1(x As Double) As Double
Dim dblRes As Double
dblRes = x ^ 2
dblRes = 1 / dblRes
dblRes = 1 + dblRes
dblRes = 1 / dblRes
dblRes = 1 + dblRes
dblRes = 1 * dblRes
dblRes = 1 + dblRes
funcy1 = dblRes
End Function
I expect one of those three options will eliminate the overflow error. It
would be helpful to see the real numbers you are using. But, another thing
to watch for is sometimes you have to qualify integer values as 3# instead
of as 3, when they are used in a formula that is going to evaluate as a
double. Strange, but I've seen it result in an overflow error in VBA. Good
luck.
Troy
"berger" <ber...@gtemail.net> wrote in message
news:6engauo6qgcnes9ma...@4ax.com...
>
> Excel 2000 SR-1 for Windows.
Bob O`Bob
> Strangely, this doesn't work either. The same problems arose in VB6 as well as
> VBA.
>
?? does this mean you were able to duplicate the error in VB6?
I'd like to see that code.
We could probably deconstruct it in order-of-precedence to see
exactly which step blows up. Also with VB6 it should be possible
to disassemble ...
Bob
--
posting from work, but representing only myself
Jake Marx
"Bob O`Bob" wrote:
> ?? does this mean you were able to duplicate the error in VB6?
>
> I'd like to see that code.
This code errors out in VB6 SP4 (run-time 6: overflow):
Private Sub Command1_Click()
Debug.Print funcy1(1)
End Sub
Function funcy1(x As Double) As Double
funcy1 = 1 + 1 * (1 + 1 / (1 + 1 / x ^ 2))
End Function
Regards,
Jake Marx
MS MVP - Excel
Bob O`Bob
> > I'd like to see that code.
>
> This code errors out in VB6 SP4 (run-time 6: overflow):
>
> Private Sub Command1_Click()
> Debug.Print funcy1(1)
> End Sub
>
> Function funcy1(x As Double) As Double
> funcy1 = 1 + 1 * (1 + 1 / (1 + 1 / x ^ 2))
> End Function
wow. Thanks, Jake.
so it does.
obviously I should have just tried it.
even like this:
funcy1 = 1 + 1 * (1 + 1 / (1 + 1 / x ^ 1))
or this:
funcy1 = 1# + 1# * (1# + 1# / (1# + 1# / x ^ 1#))
let's get really wild:
funcy1 = 0# + 0# * (0# + 0# / (0# + 0# / x ^ 0#))
or
funcy1 = 0# + 0# * (0# + 0# / (0# + 0# / 1 ^ 0#))
OK, now let's even put in some extra parens just to insure
nobody tries to divide by zero:
funcy1 = 0# + 0# * (0# + 0# / (0# + 0# / (0 ^ 0#)))
yep, still blows up.
this way, TOO:
funcy1 = 0 + 0 * (0 + 0 / (0 + 0 / (0 ^ 0)))
So I don't think it actually has anything at all to do
with floating-point math.
Yeah, I don't see how any description other than "bug" fits.
A stack handling error would be my guess. It probably doesn't
even get the first intermediate calculation done.
Rob Bovey
> A stack handling error would be my guess. It probably doesn't
> even get the first intermediate calculation done.
Hi Bob,
Yep, that was my conclusion too, but I was gonna wait for the experts to
pass judgment. BTW, nice to see you hanging out here in the last bastion of
Classic VB. ;-)
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Bob O`Bob" <b...@cluestick.org> wrote in message
news:3CA8F8...@cluestick.org...
Bob O`Bob
>
> > Yeah, I don't see how any description other than "bug" fits.
> > A stack handling error would be my guess. It probably doesn't
> > even get the first intermediate calculation done.
>
> Hi Bob,
>
> Yep, that was my conclusion too, but I was gonna wait for the experts to
> pass judgment. BTW, nice to see you hanging out here in the last bastion of
> Classic VB. ;-)
Well, one of the last few, anyway.
But I think you also know where I picked up the suggestion
to pop in here for a peek at this interesting bug/puzzle ;-)
Harlan Grove
...
>funcy1 = 1 + (1 + 1 / (1 + 1 / x ^ 2))
>
>Is this part of a longer recursive series? If this is the
>final equation, it is very confusing to read (to me). I
>find it much better to restate the equation.
>
>funcy1 = 1+(2*x^2+1)/(x^2+1)
which should be reduced to funcy1 = 2 + x^2 / (x^2 + 1)
...
Picky: the outer parens in the first statement are unnecessary, so
funcy1 = 1 + 1 + 1 / (1 + 1 / x ^ 2)
= 2 + 1 / (1 + 1 / x ^ 2)
= 2 + 1 / (1 + x ^ -2)
The unary minus doesn't count as an operator, so that's 2 add/sub, 1
mult/div, 1 exponentiation, and 1 variable dereference. My reduction
of your second statement has 2 add/sub, 1 mult/div, 2 exponentiation,
and 2 variable dereference. Fewer floating point operations is usually
better unless more operations avoid loss of precision. Unclear you'd
manage that.
Jay Petrulis
I agree to all that you wrote. Guilty as charged, but my
point was really about reading the formula as posted, not
how efficient it was to evaluate. That said, your
suggestion make it even easier to read.
Bye,
Jay
>-----Original Message-----
>"Jay Petrulis" <jpet...@lycosmail.com> wrote...
>....
>>funcy1 = 1 + (1 + 1 / (1 + 1 / x ^ 2))
>>
>>Is this part of a longer recursive series? If this is
the
>>final equation, it is very confusing to read (to me). I
>>find it much better to restate the equation.
>>
>>funcy1 = 1+(2*x^2+1)/(x^2+1)
>
>which should be reduced to funcy1 = 2 + x^2 / (x^2 + 1)
>
>....
>
>Picky: the outer parens in the first statement are
unnecessary, so
>
>funcy1 = 1 + 1 + 1 / (1 + 1 / x ^ 2)
> = 2 + 1 / (1 + 1 / x ^ 2)
> = 2 + 1 / (1 + x ^ -2)
>
>The unary minus doesn't count as an operator, so that's 2
add/sub, 1
>mult/div, 1 exponentiation, and 1 variable dereference.
My reduction
>of your second statement has 2 add/sub, 1 mult/div, 2
exponentiation,
>and 2 variable dereference. Fewer floating point
operations is usually
>better unless more operations avoid loss of precision.
Unclear you'd
>manage that.
>.
>
Tushar Mehta
Microsoft Online' link. It'll get you started down the road to getting
support.
Be prepared for a few hassles. There is a chance that the 'support
professional' will claim -- probably in a very polite manner -- that the
software works as intended but that you don't understand how to use it.
Persevere. Save this NG discussion and attach it if necessary. After
all, the discussion attracted some real heavyweights!
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--
In <9keiausen71b8c2hu...@4ax.com>, berger
<ber...@gtemail.net> wrote
> Thanks everyone for your comments and confirming this is indeed a bug.
> What is the appropriate method to report this to Microsoft?
Bob O`Bob
> Be prepared for a few hassles. There is a chance that the 'support
> professional' will claim -- probably in a very polite manner -- that the
> software works as intended but that you don't understand how to use it.
> Persevere. Save this NG discussion and attach it if necessary.
Given that it still crashes VB using *integers*, and/or with all zeros
in the constants, it seems pretty clearly a bug in the evaluation of
that particular formula construct.
Jay Petrulis
If you do send this to Microsoft, complete with this
thread, also search comp.apps.spreadsheets for a
discussion between Harald Staff and Harlan Grove.
I think this link starts it off there.
http://groups.google.com/groups?hl=en&selm=a89bu0%24o2s%
241%40oslo-nntp.eunet.no
Harlan did a very nice job explaining in detail what might
be the problem, complete with a few expanded functions to
test.
Bye,
Jay
>.
>
Jay Petrulis
If you do send this to Microsoft, complete with this
thread, also search comp.apps.spreadsheets for a
discussion between Harald Staff and Harlan Grove.
I think this link starts it off there.
http://groups.google.com/groups?hl=en&selm=a89bu0%24o2s%
241%40oslo-nntp.eunet.no
Harlan did a very nice job explaining in detail what might
be the problem, complete with a few expanded functions to
test.
Bye,
Jay
>.
>
Tushar Mehta
believe) was wrongly interpreted in a compile-time if statement (#IF).
The support person didn't buy it; thought I didn't understand how
compiler directives worked. Had to attach a bunch of pages from the
help file before the problem was acknowledged as a real bug.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--
In <3CAB48...@cluestick.org>, Bob O`Bob <b...@cluestick.org> wrote