Cube Root of a Negative Number fails in VBA?

[jksmurf]

I'm stumped. I have a simple calculation in a Macro which results in a -ve number.

Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)

I did a watch on a and x, they are 191 and -21.

The error is " Run-time error '5' Invalid procedure call or argument "

Oddly, if I perform the SAME calc in the Worksheet it works FINE. It just fails in VBA?

Any other ... um ... hints?

k.

1. I already had a thought to send it back to the worksheet and then kuludging it back to the macro.
2. Also have implemented a -ve if statement check and multiplied by -1 if <0, then cube root that and return it to a -ve. Still a kludge though.

[NickHK]

jksmurf,
I haven't checked your equation, but Excel and VBA have different operator
precedence, wrt Negation and Exponentiation.
Maybe the reason why Excel works, but VBA fails.

NickHK

"jksmurf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm stumped. I have a simple calculation in a Macro which results in a
> -ve number.
>
> Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)
>
> I did a watch on a and x, they are 191 and -21.
>
> The error is " Run-time error '5' Invalid procedure call or argument "
>
> Oddly, if I perform the SAME calc in the Worksheet it works FINE. It
> just fails in VBA?
>
> Any ..um ... hints?
>
> k.
>
>
> --
> jksmurf
> ------------------------------------------------------------------------
> jksmurf's Profile:
http://www.excelforum.com/member.php...o&userid=29564
> View this thread: http://www.excelforum.com/showthread...hreadid=492644
>

[Patrick Molloy]

break it down

Sub xx()
Dim gr As Double
Dim a As Double
Dim x As Double
a = 191
x = -21
gr = (1 - 2 / a) / (1 + x * Sqr(2 / (a - 4)))
gr = (gr) ^ (1 / 3)

End Sub

the first value for gr is -0.844474797208743
the root of a negative number takes us into the realms of imaginary
numbers... very high-brow maths..so maybe your function needs adapting


"jksmurf" wrote:

>
> I'm stumped. I have a simple calculation in a Macro which results in a
> -ve number.
>
> Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)
>
> I did a watch on a and x, they are 191 and -21.
>
> The error is " Run-time error '5' Invalid procedure call or argument "
>
> Oddly, if I perform the SAME calc in the Worksheet it works FINE. It
> just fails in VBA?
>
> Any ..um ... hints?
>
> k.
>
>
> --
> jksmurf
> ------------------------------------------------------------------------
> jksmurf's Profile: http://www.excelforum.com/member.php...o&userid=29564
> View this thread: http://www.excelforum.com/showthread...hreadid=492644
>
>

[Philip]

Hi,

As it works fine using the Worksheet function, why not use the worksheet
function in VBA:

Application.Worksheetfunction. ....

BTW - which functions did you use in the worksheet?

HTH

Philip

"Patrick Molloy" wrote:

> break it down
>
> Sub xx()
> Dim gr As Double
> Dim a As Double
> Dim x As Double
> a = 191
> x = -21
> gr = (1 - 2 / a) / (1 + x * Sqr(2 / (a - 4)))
> gr = (gr) ^ (1 / 3)
>
> End Sub
>
> the first value for gr is -0.844474797208743
> the root of a negative number takes us into the realms of imaginary
> numbers... very high-brow maths..so maybe your function needs adapting
>
>
> "jksmurf" wrote:
>
> >
> > I'm stumped. I have a simple calculation in a Macro which results in a
> > -ve number.
> >
> > Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)
> >
> > I did a watch on a and x, they are 191 and -21.
> >
> > The error is " Run-time error '5' Invalid procedure call or argument "
> >
> > Oddly, if I perform the SAME calc in the Worksheet it works FINE. It
> > just fails in VBA?
> >
> > Any ..um ... hints?
> >
> > k.
> >
> >
> > --
> > jksmurf
> > ------------------------------------------------------------------------
> > jksmurf's Profile: http://www.excelforum.com/member.php...o&userid=29564
> > View this thread: http://www.excelforum.com/showthread...hreadid=492644
> >
> >

[kounoike]

check the results of two formulas in Worksheet

=(-2)^(1/3)
and
=(2)^(1/3)

i think this is why your formula works in Worksheet but not in VBA.

keizi

"jksmurf" <[email protected]> wrote in message
news:[email protected]...
>
> I'm stumped. I have a simple calculation in a Macro which results in a
> -ve number.
>
> Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)
>
> I did a watch on a and x, they are 191 and -21.
>
> The error is " Run-time error '5' Invalid procedure call or argument "
>
> Oddly, if I perform the SAME calc in the Worksheet it works FINE. It
> just fails in VBA?
>
> Any ..um ... hints?
>
> k.
>
>
> --
> jksmurf
> ------------------------------------------------------------------------
> jksmurf's Profile:
http://www.excelforum.com/member.php...o&userid=29564
> View this thread: http://www.excelforum.com/showthread...hreadid=492644
>

[Dana DeLouis]

Don't have an answer, just observations...

Dim x As Double
x = -125

'ok
r = -125 ^ (1 / 3)

' Error, as a variable..
r = x ^ (1 / 3)

'Works ok
r = WorksheetFunction.Power(x, 1 / 3)

'With vba reference to atp, this returns the principle value...
r = ImPower(x, 1 / 3)

( 2.5+4.33012701892219i )
--
Dana DeLouis
Win XP & Office 2003


"jksmurf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm stumped. I have a simple calculation in a Macro which results in a
> -ve number.
>
> Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)
>
> I did a watch on a and x, they are 191 and -21.
>
> The error is " Run-time error '5' Invalid procedure call or argument "
>
> Oddly, if I perform the SAME calc in the Worksheet it works FINE. It
> just fails in VBA?
>
> Any ..um ... hints?
>
> k.
>
>
> --
> jksmurf
> ------------------------------------------------------------------------
> jksmurf's Profile:
> http://www.excelforum.com/member.php...o&userid=29564
> View this thread: http://www.excelforum.com/showthread...hreadid=492644
>

[Dana DeLouis]

Don't have an answer, just observations...

Dim x As Double
x = -125

'ok
r = -125 ^ (1 / 3)

' Error, as a variable..
r = x ^ (1 / 3)

'Works ok
r = WorksheetFunction.Power(x, 1 / 3)

'With vba reference to atp, this returns the principle value...
r = ImPower(x, 1 / 3)

( 2.5+4.33012701892219i )
--
Dana DeLouis
Win XP & Office 2003


"jksmurf" <[email protected]> wrote in
message news:[email protected]...
>
> I'm stumped. I have a simple calculation in a Macro which results in a
> -ve number.
>
> Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)
>
> I did a watch on a and x, they are 191 and -21.
>
> The error is " Run-time error '5' Invalid procedure call or argument "
>
> Oddly, if I perform the SAME calc in the Worksheet it works FINE. It
> just fails in VBA?
>
> Any ..um ... hints?
>
> k.
>
>
> --
> jksmurf
> ------------------------------------------------------------------------
> jksmurf's Profile:
> http://www.excelforum.com/member.php...o&userid=29564
> View this thread: http://www.excelforum.com/showthread...hreadid=492644
>

[jksmurf]

Thanks to all who have repled, much food for thought!

Excel and VBA have different operator
precedence, wrt Negation and Exponentiation

Ta! You could be right.

the root of a negative number takes us into the realms of imaginary
numbers..

Sure it does ... but if the Worksheet can do it, why can't VBA? It works if I kludge the sign ... as per my point 2 in the edietd post??

Application.Worksheetfunction. ....
BTW - which functions did you use in the worksheet?

Just X^(1/3). I will try the built in function POWER, you gave me a good idea there, along with DANA.

check the results of two formulas in Worksheet
=(-2)^(1/3)
and
=(2)^(1/3)
i think this is why your formula works in Worksheet but not in VBA.

The results I get are
-1.25992105
and
1.25992105

Cheers kuonoike. This is what I get, and expect.

.. POWER...

Ooohh.. the POWER thing might work. I'll give it a whirl!

Thanks again to all.
k.

[Dana DeLouis]

Perhaps as a workaround with negative cube roots...

Dim r, a, x
a = 191
x = -21

r = ((1 - 2 / a) / (1 + x * Sqr(2 / (a - 4))))
If r < 0 Then
r = -(-r) ^ (1 / 3)
Else
r = r ^ (1 / 3)
End If

HTH
--
Dana DeLouis
Win XP & Office 2003


"Dana DeLouis" <[email protected]> wrote in message
news:%[email protected]...
> Don't have an answer, just observations...
>
> Dim x As Double
> x = -125
>
> 'ok
> r = -125 ^ (1 / 3)
>
> ' Error, as a variable..
> r = x ^ (1 / 3)
>
> 'Works ok
> r = WorksheetFunction.Power(x, 1 / 3)
>
> 'With vba reference to atp, this returns the principle value...
> r = ImPower(x, 1 / 3)
>
> ( 2.5+4.33012701892219i )
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "jksmurf" <[email protected]> wrote in
> message news:[email protected]...
>>
>> I'm stumped. I have a simple calculation in a Macro which results in a
>> -ve number.
>>
>> Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)
>>
>> I did a watch on a and x, they are 191 and -21.
>>
>> The error is " Run-time error '5' Invalid procedure call or argument "
>>
>> Oddly, if I perform the SAME calc in the Worksheet it works FINE. It
>> just fails in VBA?
>>
>> Any ..um ... hints?
>>
>> k.
>>
>>
>> --
>> jksmurf
>> ------------------------------------------------------------------------
>> jksmurf's Profile:
>> http://www.excelforum.com/member.php...o&userid=29564
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=492644
>>
>
>

[jksmurf]

Cheers Dana,
Works a treat. Still a bit of an VBA oddity though :-)

k.

[Tom Ogilvy]

r = -125 ^ (1 / 2)
? r
-11.1803398874989

It works as a constant because exponentiation takes precedence over negation
in VBA.

so in essence you are doing

r = -(125^(1/3))

--
Regards,
Tom Ogilvy


"Dana DeLouis" <[email protected]> wrote in message
news:%[email protected]...
> Don't have an answer, just observations...
>
> Dim x As Double
> x = -125
>
> 'ok
> r = -125 ^ (1 / 3)
>
> ' Error, as a variable..
> r = x ^ (1 / 3)
>
> 'Works ok
> r = WorksheetFunction.Power(x, 1 / 3)
>
> 'With vba reference to atp, this returns the principle value...
> r = ImPower(x, 1 / 3)
>
> ( 2.5+4.33012701892219i )
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "jksmurf" <[email protected]> wrote in
> message news:[email protected]...
> >
> > I'm stumped. I have a simple calculation in a Macro which results in a
> > -ve number.
> >
> > Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)
> >
> > I did a watch on a and x, they are 191 and -21.
> >
> > The error is " Run-time error '5' Invalid procedure call or argument "
> >
> > Oddly, if I perform the SAME calc in the Worksheet it works FINE. It
> > just fails in VBA?
> >
> > Any ..um ... hints?
> >
> > k.
> >
> >
> > --
> > jksmurf
> > ------------------------------------------------------------------------
> > jksmurf's Profile:
> > http://www.excelforum.com/member.php...o&userid=29564
> > View this thread:
http://www.excelforum.com/showthread...hreadid=492644
> >
>
>

[Jerry W. Lewis]

Not that odd, 1/3 has no exact binary representation, and so must be
approximated. The best approximation to 1/3 would result in a complex result
for your calculation; hence the error.

Excel attempts to simplify life for novices by assuming that binary numbers
which are "close enough" to common numbers (1/3 in this case) were actually
intended to be be that common number. That assumption introduces additional
inaccuracy to calculations when Excel guesses wrong. VBA assumes that the
programmer knows what they are doing, and hence does not try to guess. Hence
the VBA Round() funcion, also will produce results that you may not expect
when used to round to a particular decimal place.

Jerry

"jksmurf" wrote:

>
> Cheers Dana,
> Works a treat. Still a bit of an VBA oddity though :-)
>
> k.
>
>
> --
> jksmurf
> ------------------------------------------------------------------------
> jksmurf's Profile: http://www.excelforum.com/member.php...o&userid=29564
> View this thread: http://www.excelforum.com/showthread...hreadid=492644
>
>

[jksmurf]

Thanks Jerry,

Actually by odd I just meant I still don't understand (as a novice) why Excel can do it in the Worskheet, but not in VBA...

k.

[kounoike]

"jksmurf" <[email protected]> wrote in message
news:[email protected]...
> Actually by odd I just meant I still don't understand (as a novice) why
> Excel can do it in the Worskheet, but not in VBA...

I misunderstood your first article. Actually, as you said, this is very odd and
seems
to be unresanable to me.

but it seems VBA do not accept negative number to the power of any number.
when I tried debug.print (-8)^(1/3) in imemdiate window, it return error message.

in cube root

Function mytest(ByVal a As Range, ByVal x As Range) As Double
Dim sign As Integer
sign = IIf((1 - 2 / a) / (1 + x * Sqr(2 / (a - 4))) >= 0, 1, -1)
mytest = sign * Abs((1 - 2 / a) / (1 + x * Sqr(2 / (a - 4)))) ^ (1 / 3)
End Function

will work but not always in the power of any number.
in such case, no idea other than using power function come up to me.

Function mytest1(ByVal a As Range, ByVal x As Range) As Double
mytest1 = Application.Power((1 - 2 / a) / (1 + x * Sqr(2 / (a - 4))), (1 / 3))
End Function

keizi

[jksmurf]

Thank you Keizi.
The power function does the trick too.
Rgds

k.

[Tushar Mehta]

From the VBA help file for the ^ operator:
--- begin quote ---
result = number^exponent

....

Remarks

A number can be negative only if exponent is an integer value...
--- end quote ---

My guess is that, like so many other functions, this was implemented
using the first method some programmer found in an introductory
textbook on the subject.

x^n = exp(n*ln(x))

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> I'm stumped. I have a simple calculation in a Macro which results in a
> -ve number.
>
> Gr = ((1 - 2 / a) / (1 + x * sqr(2 / (a - 4)))) ^(1 / 3)
>
> I did a watch on a and x, they are 191 and -21.
>
> The error is " Run-time error '5' Invalid procedure call or argument "
>
> Oddly, if I perform the SAME calc in the Worksheet it works FINE. It
> just fails in VBA?
>
> Any ..um ... hints?
>
> k.
>
>
> --
> jksmurf
> ------------------------------------------------------------------------
> jksmurf's Profile: http://www.excelforum.com/member.php...o&userid=29564
> View this thread: http://www.excelforum.com/showthread...hreadid=492644
>
>