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

--
jksmur
-----------------------------------------------------------------------
jksmurf's Profile: http://www.excelforum.com/member.php...fo&userid=2956
View this thread: http://www.excelforum.com/showthread.php?threadid=49264

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" wrote in
message ...

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

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

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

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" wrote in message
...

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

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" wrote in
message ...

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

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" wrote in
message ...

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

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" wrote in message
...
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" wrote in
message ...

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

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

NickHK Wrote:

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

Ta! You could be right.

Patrick Wrote:

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

Philip Wrote:

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

kounoike Wrote:
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
------------------------------------------------------------------------
jksmurf's Profile: http://www.excelforum.com/member.php...o&userid=29564
View this thread: http://www.excelforum.com/showthread...hreadid=492644

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

k

--
jksmur
-----------------------------------------------------------------------
jksmurf's Profile: http://www.excelforum.com/member.php...fo&userid=2956
View this thread: http://www.excelforum.com/showthread.php?threadid=49264

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" wrote in message
...
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" wrote in
message ...

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

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

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.


--
jksmurf
------------------------------------------------------------------------
jksmurf's Profile: http://www.excelforum.com/member.php...o&userid=29564
View this thread: http://www.excelforum.com/showthread...hreadid=492644

"jksmurf" wrote in message
...
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

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

k.


--
jksmurf
------------------------------------------------------------------------
jksmurf's Profile: http://www.excelforum.com/member.php...o&userid=29564
View this thread: http://www.excelforum.com/showthread...hreadid=492644

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