Every linear motion design starts with the same choice: How do you convert rotary motor motion into linear travel? The two most common answers are the Lead Screw (simple, cheap, friction-based) and the Ball Screw (complex, expensive, rolling-based). Making the wrong choice here is costly. Use a lead screw where you need precision, and you get backlash. Use a ball screw in a vertical lift without a brake, and your load crashes to the floor. In this guide, we compare them side-by-side. Table of Contents 1. The Physics: Sliding vs. Rolling 2. Efficiency & The "Back-Driving" Danger 3. Accuracy and Backlash 4. Selection Table Advertisement 1. The Physics: Sliding vs. Rolling The fundamental difference is friction. Lead Screws rely on Sliding Friction . The nut (often bronze or plastic) slides directly against the steel screw threads. This generates heat and wear. Ball Screws re...
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