Hi, I'm currently working on a UDF (user-defined function) in MS excel, which enables the user to determine what values of x satisfy an equation. I've seen code like this previously on websites such as https://newtonexcelbach.wordpress.com/2011/01/13/solving-higher-order-polynomials/ but the variables used in this code aren't explanatory and it is very difficult to track their method. So far, I have created a function where the user selects a 4x1 range containing 3 co-efficients and a constant (cubic), and inputs a value that they wish to be solved for. I have completed the quadratic and linear code, but the cubic code is not yet completed and creates errors in the presence of more than one root. How could I improve this code to find all solutions of a cubic equation, and even high-order polynomials such as quartics etc.?
Code:
Public Function solvePOLY(ByRef rng As Range, ByVal NumX As Double) As Variant
Dim CoEffA As Double, CoEffB As Double, CoEffC As Double, ConstD As Double
Dim ERRORmsgA As VbMsgBoxResult, ERRORmsgB As VbMsgBoxResult, ERRORmsgC As VbMsgBoxResult, ERRORmsgD As VbMsgBoxResult
Dim deltaCHNG As Double, NoSol As String, xINT(1 To 2) As Double
Dim pCubic As Double, qCubic As Double, rCubic As Double, xintCUBIC As Double, xintPORTION As Double
Application.Volatile
If VBA.IsNumeric(rng.Cells(1, 1).Value) = True Then
CoEffA = rng.Cells(1, 1).Value
Else
ERRORmsgA = MsgBox("The x³ co-efficient must be numeric", vbApplicationModal + vbCritical + vbOKOnly, "Error")
End
End If
If VBA.IsNumeric(rng.Cells(1, 2).Value) = True Then
CoEffB = rng.Cells(1, 2).Value
Else
ERRORmsgB = MsgBox("The x² co-efficient entry must be numeric", vbApplicationModal + vbCritical + vbOKOnly, "Error")
End
End If
If VBA.IsNumeric(rng.Cells(1, 3).Value) = True Then
CoEffC = rng.Cells(1, 3).Value
Else
ERRORmsgC = MsgBox("The x co-efficient entry must be numeric", vbApplicationModal + vbCritical + vbOKOnly, "Error")
End
End If
If VBA.IsNumeric(rng.Cells(1, 4).Value) = True Then
ConstD = rng.Cells(1, 4).Value
Else
ERRORmsgD = MsgBox("The constant entry must be numeric", vbApplicationModal + vbCritical + vbOKOnly, "Error")
End
End If
deltaCHNG = (CoEffC ^ 2) - (4 * CoEffB * (ConstD - NumX))
NoSol = "No Solutions"
If rng.Cells(1, 1).Value = 0 Then
If rng.Cells(1, 2).Value = 0 Then
solvePOLY = Application.Transpose(Array((NumX - ConstD) / CoEffC, " ", " "))
Else
On Error GoTo NoSolutions
xINT(1) = (-CoEffC - Sqr(deltaCHNG)) / (2 * CoEffB)
xINT(2) = (-CoEffC + Sqr(deltaCHNG)) / (2 * CoEffB)
NoSolutions: solvePOLY = Application.Transpose(Array(NoSol, " ", " "))
If deltaCHNG = 0 Then
solvePOLY = Application.Transpose(Array(xINT(1), " ", " "))
End If
If deltaCHNG > 0 Then
solvePOLY = Application.Transpose(Array(xINT(1), xINT(2), " "))
End If
End If
Else
pCubic = (-CoEffB) / (3 * CoEffA)
qCubic = (pCubic ^ 3) + ((CoEffB * CoEffC) - (3 * CoEffA * (ConstD - NumX))) / (6 * (CoEffA ^ 2))
rCubic = CoEffC / (3 * CoEffA)
xintPORTION = Sqr((qCubic ^ 2) + ((rCubic - (pCubic ^ 2)) ^ 3))
xintCUBIC = ((qCubic + xintPORTION) ^ (1 / 3)) + ((qCubic - xintPORTION) ^ (1 / 3)) + pCubic
solvePOLY = Application.Transpose(Array(xintCUBIC, "", ""))
[B]'Solve Cubic needs continuation[/B]
End If
End Function
