Excel Function Macro

[ghoshwin]

I am very new to excel macro. What i need to write a function to find the root of a Cubic function. But the function did not return the proper value. I tried but not able to find out the mistake. It return when H<0 (checked with a=2, b=-4, c=-22, d=34) but not for other cases when H>0 (eg. a=24, b=-4, c=-22, d=34). Please help. The function is as below.


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function ReturnRoot(a, b, c, d)
'
'
F = ((3 * c / a) - ((b) ^ 2 / (a) ^ 2)) / 3
G = ((2 * (b) ^ 3 / (a) ^ 3) - (9 * b * c / (a) ^ 2) + (27 * d / a)) / 27
H = ((G) ^ 2 / 4) + ((F) ^ 3 / 27)
'
'
If F = 0 And G = 0 And H = 0 Then GoTo A1:
If H <= 0 Then GoTo A2:
If H > 0 Then GoTo A3:
'
'''''''''''''''''''''''''''''''''''''''''''
A1:

X1 = (d / a) ^ (1 / 3) * (-1)

GoTo a:

'''''''''''''''''''''''''''''''''''''''''''
A2:

L = (-1) * (((G) ^ 2 / 4 - H) ^ (1 / 2)) ^ (1 / 3)
M = Cos((WorksheetFunction.Acos(-G / (2 * (((G) ^ 2 / 4 - H) ^ (1 / 2))))) / 3)
N = (3) ^ (1 / 2) * Sin((WorksheetFunction.Acos(-G / (2 * (((G) ^ 2 / 4 - H) ^ (1 / 2))))) / 3)
P = (b / (3 * a)) * (-1)
'
'
X1 = 2 * ((((G) ^ 2 / 4 - H) ^ (1 / 2)) ^ (1 / 3)) * Cos((WorksheetFunction.Acos(-G / (2 * (((G) ^ 2 / 4 - H) ^ (1 / 2))))) / 3) - (b / (3 * a))

GoTo a:

''''''''''''''''''''''''''''''''''''''''''
A3:

R = (-1) * (G / 2) + H ^ (1 / 2)
S = (R) ^ (1 / 3)
T = (-1) * (G / 2) - H ^ (1 / 2)
U = (T) ^ (1 / 3)
'
'
X1 = (S + U) - (b / (3 * a))

GoTo a:

''''''''''''''''''''''''''''''''''''''''''
a:

ReturnRoot = X1

End Function

 

[Rijnsent]

Hi ghoshwin,
your code crashes here:
S = (R) ^ (1 / 3) -> when R < 0, this crashes as you can't get the root of a negative number (that's an irrational number). If you want to find this issue: if you run Debug.Print ReturnRoot(24, -4, -22, 34) , don't push "play"/F5, but F8 to go step by step. Every time you press F8, the code executes one line of code. In that way you'll see the macro crashing on that S= line.

And I took the liberty to beautify your code a bit, with the main points:

• add comments
• Dim the variables
• remove the GoTo, it's normally only used to skip errors, not as IF-THEN logic

Cheers,
Koen

Function ReturnRoot(a As Integer, b As Integer, c As Integer, d As Integer) As Double

Dim F As Double, G As Double, H As Double, X1 As Double

'Determine factors F, G and H
F = ((3 * c / a) - ((b) ^ 2 / (a) ^ 2)) / 3
G = ((2 * (b) ^ 3 / (a) ^ 3) - (9 * b * c / (a) ^ 2) + (27 * d / a)) / 27
H = ((G) ^ 2 / 4) + ((F) ^ 3 / 27)

If F = 0 And G = 0 And H = 0 Then
    X1 = (d / a) ^ (1 / 3) * (-1)
ElseIf H <= 0 Then
    L = (-1) * (((G) ^ 2 / 4 - H) ^ (1 / 2)) ^ (1 / 3)
    M = Cos((WorksheetFunction.Acos(-G / (2 * (((G) ^ 2 / 4 - H) ^ (1 / 2))))) / 3)
    N = (3) ^ (1 / 2) * Sin((WorksheetFunction.Acos(-G / (2 * (((G) ^ 2 / 4 - H) ^ (1 / 2))))) / 3)
    P = (b / (3 * a)) * (-1)
    
    X1 = 2 * ((((G) ^ 2 / 4 - H) ^ (1 / 2)) ^ (1 / 3)) * Cos((WorksheetFunction.Acos(-G / (2 * (((G) ^ 2 / 4 - H) ^ (1 / 2))))) / 3) - (b / (3 * a))
ElseIf H > 0 Then
    '
    R = (-1) * (G / 2) + H ^ (1 / 2)
    S = (R) ^ (1 / 3)
    T = (-1) * (G / 2) - H ^ (1 / 2)
    U = (T) ^ (1 / 3)
    
    X1 = (S + U) - (b / (3 * a))
Else
    'Should be impossible
    X1 = 0
End If

'Return value
ReturnRoot = X1

End Function