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. I
just fails in VBA?

Any ..um ... hints?

k

 

[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

 

[Guest]

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

 

[Guest]

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

 

[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

 

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

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]

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

 

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

That's the thing, AFAIK there is no built in function like SQRT
function for X^(1/3) ?

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.

Thanks again to all.
k.

 

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

 

[Guest]

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]

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]

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