So. It’s come to this. You have developed so many formulas that you’re sick of having to use the same combination over and over again to give you the same outcome for different datasets. Did you know that you can make your own custom formulas in Excel? I’m sure that this is what people would have been doing in Excel before the Python add-in was made available towards the end of 2023.

One particular pain point that I frequently come across is having to type out formulas that I use time and time again, i.e., how to solve quadratic equations and accounting for all the different types of cases. Let’s see if I can make this a bit more obvious and start with some simpler examples:

Example 1: Fahrenheit to Celsius Converter

Ignoring the fact that Excel and Google Sheets already contain the extremely powerful CONVERT function, we’re going to create our own Fahrenheit to Celsius converter in Excel using Visual Basic for Applications (VBA). For a brief history of Visual Basic, click this YouTube link. For those who actually clicked that video link, is that not the weirdest collaboration of cultures?

This setup will be Mac specific:

1. Open a new Excel workbook,

2. Click Developer on the ribbon,
3. Click Visual Basic,

4. Click Insert > Module,

5. Enter the following code:

Public Function FtoC(Tf As Double) As Double

Dim Tc As Double

Tc = (Tf – 32) * (5 / 9)

FtoC = Round(Tc, 2)

End Function

6. Click View Microsoft Excel,

7. Create this table:

8. Enter the newly created formula name form the VBA script: =FtoC(),

9. Select A4,

10. Extend the formula down:

Example 2: Volume of a Cylinder

I’m going to repeat the same steps above, and enter the following code:

Public Function CylinderVol(r As Double, h As Double) As Double

Dim pi As Double

pi = Application.WorksheetFunction.pi()

v = pi * r ^ 2 * h

CylinderVol = Round(v, 2)

End Function

Let’s check 3 different volume dimensions:

Radius: [3, 5, 2]; Height: [4, 9, 150]

Let’s double-check our answer against a manual formula:

Looking nice. Let’s take it to another level though. Instead of making a user-defined formula, let’s create a mini program to solve quadratic equations.

Example 3: Quadratic Equation Solver

If you don’t happen to remember, this is a quadratic formula:

x = (-b +/- sqrt(b^2 – 4ac))/2a

Source: https://byjus.com/maths/quadratic-formula/

We’re essentially looking where a parabolic curve either: cuts through the x-axis two times, once, or not at all. We can break this equation down into parts, but let’s look specifically at the portion under the square root (sqrt). It is called the Discriminant.

Discriminant (d): b^2 – 4ac

We have 3 possible states (or cases) that the discriminant (d) can exhibit:

1. When d is positive, two real solutions (or there are two locations along the x-axis where the curve cuts),
2. When d is zero, one real solution (there is one point, the turning point, that is touching the x-axis), and
3. When d is negative, complex (or no) solution. These are the imaginary number solutions.

Examples:

1. 5x^2 + 6x + 1 = 0
2. x^2 + 2x + 1 = 0
3. x^2 – 4x + 6.25 = 0

First, set up an Excel file:

Open Visual Basic:

Move this Visual Basic screen to the side and create a Button from the Developer tab:

I’m going to type Solve:

I’m going to make another button; Reset:

Next, right click on Solve and click either ‘New’ or ‘Edit’ to write the macro:

You may have to create a new module (but you already know how to do that). Enter the following code to generate the solver for the problem:

VBA Code 1

Sub Solve_Click()

Dim a, b, c, det, x1, x2 As Single

a = Cells(3, 10)

b = Cells(4, 10)

c = Cells(5, 10)

det = (b ^ 2) – (4 * a * c)

If det < 0 Then

Cells(8, 10) = “No Real Solution”

Cells(9, 10) = “”

ElseIf det = 0 Then

Cells(8, 10) = “One Root; x1=x2”

Cells(9, 10) = x2

Else

x1 = (-b + Sqr(det)) / (2 * a)

x2 = (-b – Sqr(det)) / (2 * a)

Cells(8, 10) = Round(x1, 2)

Cells(9, 10) = Round(x2, 2)

End If

End Sub

VBA Code 2

Then enter this following code to clear all of the cells:

Sub Reset_Click()

Range(“J3:J5”).ClearContents

Range(“J8:J9”).ClearContents

End Sub

Afterwards, navigate back to the Excel worksheet, right-click the Reset button and make sure it is named ‘Reset’, then assign the macro (also a right-click option).

Let’s test it using those previous test cases:

Test Case 1

5x^2 + 6x + 1 = 0

a = 5,

b = 6,

c = 1.

I’ll hit reset (to remove all of the value) then plug in the next example:

Test Case 2

x^2 + 2x + 1 = 0

a = 1,

b = 2,

c = 1.

Nice. Now, for the last case.

Test Case 3

x^2 – 4x + 6.25 = 0

a = 2,

b = -4,

c = 6.25.

Looking good.

How did you go following along? Can you make your own? Comment below to detail some macros that you’d like to see or make!