Numerical Methods - First derivative using Excel VBA code

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
' http://mechanical-design-handbook.blogspot.com
'
' First Derivative - Numerical Methods
' ================================================

Function f(x As Double) As Double
  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
fDeriv = (-f(x + 2 * h) + 8 * f(x + h) - 8 * f(x - h) + f(x - 2 * h)) / 12 / h
End Function

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)³-4x²-sin(2x) is 15(x-3)²-8x-2cos(2x). Therefore the first derivative of f(x) where x = 7 must be 15(7-3)²-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.

password: mechanical-design-handbook.blogspot.com
FREE DOWNLOAD EXAMPLE EXCEL FILE