In the previous post, Numerical Methods - First derivative using Excel formula, we know how to calculate the first derivative of functions using formula in Microsoft Excel. In this post, let's see how we can simplify it by using Excel VBA code.
The VBA is just like this.
' Created by Suparerg Suksai
' Mechanical Design Handbook
' First Derivative - Numerical Methods
Function f(x As Double) As Double
f = 5 * (x - 3) ^ 3 - 4 * x ^ 2 - Sin(2 * x)
Function fDeriv(x As Double) As Double
Const h = 0.00001 ' Step size
fDeriv = (-f(x + 2 * h) + 8 * f(x + h) - 8 * f(x - h) + f(x - 2 * h)) / 12 / h
The highlighted (yellow) text is the function that we want to calculate for the first derivative. We can simply replace it with new function. The remaining code still remains the same.
We have to put this code into the Microsoft Visual Basic Editor in Microsoft Excel (Alt+F11). Now we're going to test this Derivative code. Because the first derivative of the function in this example can be determined by direct method. Then we can use it to recheck the calculation result.
The first derivative of 5(x-3)3-4x2-sin(2x) is 15(x-3)2-8x-2cos(2x). Therefore the first derivative of f(x) where x = 7 must be 15(7-3)2-8(7)-2cos(2(7)) = 183.7265256
Here how to use
In excel spreadsheet, enter =fDeriv(7) and see the result.
We'll use this First Derivative Function in the Newton-Raphson Root Finding Method later.
FREE DOWNLOAD EXAMPLE EXCEL FILE