For engineers who already know the math—but still lose projects. For the last few years, I’ve been sharing technical guides here on Mechanical Design Handbook —how to size a motor, how to calculate fits, and (as you recently read) how to choose between timing belts and ball screws. But after 25 years in industrial automation, I realized something uncomfortable: Projects rarely fail because the math was wrong. They fail because: The client changed the scope three times in one week. A critical vendor lied about a shipping date (and no one verified it). The installation technician couldn’t fit a wrench into the gap we designed. University taught us the physics. It didn’t teach us the reality. That gap is why I wrote my new book, The Sheet Mechanic . This is not a textbook. It is a field manual for the messy, political, and chaotic space between the CAD model and the factory floor. It captures the systems I’ve used to survive industrial projec...
In the previous post, Numerical Methods - First derivative using Excel formula, we learned how to calculate the first derivative of functions using standard formulas in the spreadsheet grid.
While effective, that method can be cumbersome to set up for repeated use. In this post, let's see how we can simplify the process by creating a reusable custom function using Excel VBA (Visual Basic for Applications). Instead of downloading a pre-made file, follow the steps below to build this powerful tool yourself.
Advertisement
The VBA Code Solution
The following code implements the Five-Point Stencil method for high precision. It defines the mathematical function f(x) and a derivative function fDeriv(x).
Step 1: Open Excel and press Alt+F11 to open the VBA Editor.
Step 2: Go to Insert > Module.
Step 3: Copy and paste the code below into the module window.
' ================================================ ' Created by Suparerg Suksai ' Mechanical Design Handbook ' First Derivative - Numerical Methods ' ================================================ Function f(x As Double) As Double ' Define your function here. ' Example: 5(x-3)^3 - 4x^2 - sin(2x) f = 5 * (x - 3) ^ 3 - 4 * x ^ 2 - Sin(2 * x) End Function Function fDeriv(x As Double) As Double Const h = 0.00001 ' Step size for numerical precision ' 5-Point Stencil Formula for First Derivative fDeriv = (-f(x + 2 * h) + 8 * f(x + h) - 8 * f(x - h) + f(x - 2 * h)) / (12 * h) End Function
The section inside Function f(x) is the equation we want to differentiate. You can simply replace that line with any mathematical function you need to solve. The derivative logic remains the same.
Verification: Checking the Accuracy
To prove this works, let's verify the code against an analytical calculation.
The function used in the code above is:
f(x) = 5(x-3)3 - 4x2 - sin(2x)
Using calculus rules, the exact first derivative is:
f'(x) = 15(x-3)2 - 8x - 2cos(2x)
Let's test this at x = 7:
f'(7) = 15(7-3)2 - 8(7) - 2cos(14)
f'(7) = 15(16) - 56 - 2(0.1367)
f'(7) ≈ 183.7265
Advertisement
Using the Function in Excel
Now, go back to your spreadsheet. In any cell, simply type:
=fDeriv(7)
Figure 1: The custom function returns the highly accurate result (183.7265) directly in the cell.
The result matches our manual calculation perfectly.
This technique is a building block for more complex engineering tools. In fact, we will use this exact fDeriv function to power the Newton-Raphson Root Finding Method in our next tutorial.
Comments