calculation of geometric mean from values in textfields: error

[registoni]

Hi,


I have a simple code which looks up at prev. 4 year financials which are stored in unbound textfields (tb0, tb1, tb2, tb3; all as strings) in the current opened form and calculates geometric mean from them:

' calculate geometric growth averages rates


 Dim xtemp01 As Double
 Dim xtemp02 As Double
 Dim xtemp03 As Double
 Dim xtemp04 As Double
 Dim xtemp05 As Double
 


 xtemp01 = ((Me(tb1).Value / Me(tb0).Value - 1))
 
 xtemp02 = ((Me(tb2).Value / Me(tb1).Value - 1))
 
 xtemp03 = ((Me(tb3).Value / Me(tb2).Value - 1))
 
 xtemp04 = (xtemp01 * xtemp02 * xtemp03)
 
 xtemp05 = (xtemp04) ^ (1 / 3)
 
  
 Me(textboxname).Value = Round(xtemp05, 0)

on this line xtemp05 = (xtemp04) ^ (1 / 3) it report an error RUNTIME ERROR 5: Invalid procedure call or argument.
What I am doing wrong?
UPDATE: it turns out one of fields have negative value, but I am taking cubic root, not a square root. What is wrong with cubic root from negative amount?

[registoni]

found solution (taken from here)

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.

 
 Dim xtemp01 As Double
 Dim xtemp02 As Double
 Dim xtemp03 As Double
 Dim xtemp04 As Double
 Dim xtemp05 As Double
 Dim sign As Integer
 
 xtemp01 = ((Me(tb1).Value / Me(tb0).Value))
 
 xtemp02 = ((Me(tb2).Value / Me(tb1).Value))
 
 xtemp03 = ((Me(tb3).Value / Me(tb2).Value))
 
 xtemp04 = (xtemp01 * xtemp02 * xtemp03)
 
 sign = IIf(xtemp04 >= 0, 1, -1)


 xtemp05 = (sign * Abs(xtemp04) ^ (1 / 3)) -1