How to take the cube root of a negative number in VBA for Excel?

I am trying to compute the cube root of a negative number in VBA for Excel. Taking the cube root of a negative number in an Excel worksheet works fine:

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

However the same concept coded into VBA:

(-2)^(1/3)

gives a #VALUE!error when returning the value to the worksheet. I suspect VBA is parsing the 1/3 to its decimal representation 0.3333... and therefore causing a mathematical error since negative numbers can't be rai开发者_JS百科sed to non-integer powers in the real domain. Is there a workaround?

Try using the POWER() function:

POWER(-2, 1/3)

I think CarlF's/your answer is the best, but it's worth mentioning that you can use Application.Evaluate to return the result of a literal Excel formula:

Range("A1") =  Application.Evaluate("=(-2)^(1/3)")

For me, it didn't work with (-2)^(1/3) as you stated.

But it worked with -2^(1/3).

I have no idea why is this.

If x is a positive real number then the cube root of -x is simply the negation of the cube root of x, i.e., -(x^(1/3).