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

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