Skip to main content
New · Free engineering tools Spring calculator and shaft power drive sizing. Animated, with charts and PDF export. Open tools →

Why I Wrote The Sheet Mechanic (And Why Calculations Aren’t Enough)

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...

Stop trying to be a hero. Start being a mechanic.

Get the book →

The Sheet Mechanic on Amazon. Affiliate link, I earn from qualifying purchases.

Calculate First Derivatives with Numerical Method Using Excel VBA

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)

Screenshot of Excel VBA First Derivative Calculation Result
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

Popular posts from this blog

Dowel Pins & Locating Pins: The Basics of Fixture Design

Dowel pins are precision cylindrical pins used for accurate part alignment in assemblies. They control position, not clamping force. This guide explains tolerances, fits, sizing rules, and design best practices. Figure 1: A typical fixture setup. Notice how dowel pins (silver) provide precise location, while bolts (not shown here) provide the clamping force. In the world of Precision Engineering , the difference between a high-quality product and a scrap part often comes down to microns. While bolts hold parts together, they are terrible at positioning them. This is where Dowel Pins and Locating Pins become essential components in industrial tooling . Advertisement What is a Dowel Pin? Dowel pins are precision-ground fasteners used to secure the relative position of two parts. They are typically machined to extremely tight tolerances (often within 0.0001 inches) and are available in materials like: Hardened Steel: For high-wea...

Hoeken's Linkage: Kinematics and Walking Robot Design

Figure 1: Animated simulation of the Hoeken’s Linkage showing the characteristic "tear-drop" coupler curve. 🚀 New Design Guide Available Don't just read about it—build it. Check out our new tutorial: How to Design a Hoeken’s Linkage in Excel (with Free VBA Simulator) » Introduction to the Hoekens Linkage The Hoekens linkage is a specialized four-bar mechanism designed to convert rotational motion into an approximate straight-line motion. While it serves a similar purpose to other straight-line generators, its unique coupler curve—a "tear-drop" shape—makes it exceptionally useful for intermittent motion and walking machines. One of the most fascinating aspects of kinematic theory is the concept of "Cognates." The Hoekens linkage is actually a cognate linkage of the Chebyshev Straight-line Mechanism . This means that while the physical structure and link lengths differ, they can generate...

Overhung Load (OHL) Calculation: Why Motor Shafts Break

The Failure Scenario: You specify a 50 HP AC induction motor for an industrial shredder. To achieve the required reduction ratio, you mount a very small diameter roller chain sprocket directly onto the motor shaft. Six months later, the solid steel motor shaft snaps completely off, flush with the bearing housing. The Cause: You exceeded the motor's maximum Overhung Load (OHL) . The motor was perfectly sized for the torsional load (the twisting force), but the mechanical geometry created a massive radial load (a bending force) that destroyed the shaft through high-cycle fatigue. Power transmission is not just about matching horsepower. The physical connection method (chains, V-belts, or gears) drastically alters the stress applied to the motor bearings. This guide explains the physics of OHL, how to calculate it, and how to engineer your way out of radial failure. Table of Contents 1. The Physics: Torsion vs. Bending Moment 2. The OHL Cal...