How to Find the Roots of Cubic Equations in Excel Using Custom VBA Functions

Introduction

Understanding and solving cubic equations can be an intricate task, but leveraging Excel with its VBA (Visual Basic for Applications) capabilities simplifies this process immensely. This guide will walk you through creating a custom function to find the roots of cubic equations within Excel.

Step-by-Step Guide to Create a Custom VBA Function for Cubic Equation Roots

Step 1: Open the VBA Editor

First, you need to open the VBA editor where you will write your custom function.

Open your Excel workbook. Press ALT F11 to open the VBA editor. Click on Insert > Module to create a new module.

Step 2: Write the Function

Next, you will write the code to define the function that calculates the roots of the cubic equation.

Function FindCubicRoots(a As Double, b As Double, c As Double, d As Double) As Variant
    Dim roots(1 To 3) As Double
    Dim delta0 As Double, delta1 As Double, delta As Double
    Dim C As Double, sqrtDelta As Double
    Dim u As Double, uCubed As Double
    Dim i As Integer
    ' Calculate the coefficients for the cubic
    delta0  b ^ 2 - 3 * a * c
    delta1  2 * b ^ 3 - 9 * a * b * c - 27 * a ^ 2 * d
    delta  delta1 ^ 2 - 4 * delta0 ^ 3
    ' Calculate the roots
    If delta  0 Then
        ' Three real roots
        sqrtDelta  Sqr(delta)
        C  (delta1   sqrtDelta) / 2 ^ (1 / 3)
        roots(1)  (-1 / 3) * a * b   C - delta0 / C
        roots(2)  (-1 / 3) * a * b - (C   (sqrtDelta / 2)) - delta0 / C
        roots(3)  (-1 / 3) * a * b - (C - (sqrtDelta / 2)) - delta0 / C
    ElseIf delta  0 Then
        ' One real root and a double root
        roots(1)  -b / 3 * a
        roots(2)  roots(1)
        roots(3)  roots(1)
    Else
        ' One real root and two complex roots
        uCubed  delta1 / (2 * Sqr(Sqr(-delta0) ^ 3))
        u  Atn(uCubed  1000000000 #)
        roots(1)  -2 * Sqr(-delta0) * Cos(u / 3) - b / 3 * a
        roots(2)  -2 * Sqr(-delta0) * Cos((u   2 * Pi) / 3) - b / 3 * a
        roots(3)  -2 * Sqr(-delta0) * Cos((u - 2 * Pi) / 3) - b / 3 * a
    End If
    FindCubicRoots  roots
End Function

Step 3: Save and Close the VBA Editor

Once the function is written, save your workbook as a macro-enabled workbook (.xlsm).

Click File Save As in the VBA editor. Select the location where you want to save the file. Choose the Macro-Enabled Workbook option and click Save.

Step 4: Use the Function in Excel

Now that the function is ready, you can use it in your Excel sheet.

Enter the coefficients of your cubic equation into separate cells (e.g., A1 for a, B1 for b, C1 for c, D1 for d). Use the following formula in a cell to find the roots:

FindCubicRoots(a, b, c, d)

For example, for the equation x^3 - 6x^2 11x - 6 0 (where a1, b-6, c11, d-6) you can type:

FindCubicRoots(1, -6, 11, -6)

Since the function returns an array of roots, you may need to select multiple cells and enter the formula as an array formula by pressing CTRL SHIFT ENTER.

Important Notes

The function returns an array of roots. You may need to select multiple cells (e.g., a 3-cell range) and enter the formula as an array formula by pressing CTRL SHIFT ENTER. The function handles real and complex roots.

By following these steps, you can effectively find the roots of cubic equations in Excel using a custom VBA function!