Excel VBA Derivative: Calculate a Function’s Rate of Change

VBA & macros · Updated November 21, 2026

Excel VBA has no built-in function that symbolically derives any formula. If by “derive” you mean find a calculus derivative, you can write a VBA user-defined function (UDF) that estimates the rate of change numerically, or code the exact derivative yourself when you know its algebraic form.

What “derive” means in Excel VBA

In calculus, a derivative describes how quickly a function’s output changes as its input changes. Excel’s built-in worksheet functions do not include a general-purpose symbolic differentiator, and VBA does not add one. VBA can, however, evaluate a function at nearby input values and estimate its derivative. It can also calculate an exact derivative if you write the derivative expression directly.

These approaches answer different needs. A numerical derivative is useful when you can calculate the original function but do not want to work out its derivative by hand. An exact derivative is usually faster and more accurate, but you need to know the calculus and code the derivative correctly. Neither approach can automatically infer a general symbolic expression from an arbitrary worksheet formula without additional parsing and algebra software.

The example below uses the function t cubed plus 2t. Its exact derivative is 3t squared plus 2. At an input of 2, the derivative is 14, meaning the function’s instantaneous rate of change there is 14 units of output per unit of input.

How the numerical method works

A common numerical technique is the central difference. It evaluates the function a small distance to the right of the input and the same distance to the left, then divides the change in output by the distance between those two sample points. That gives an estimate of the slope near the input. In contrast, a forward difference samples the input and a point just to its right; central differences are generally more accurate for a smooth function when both neighboring inputs are valid.

The distance between sample points is often called the step size, or h. If it is too large, the estimate averages behavior over too broad a region and may miss local changes. If it is too small, floating-point rounding can make the subtraction of nearly equal values unreliable. The example uses a small step that scales with the size of the input, but this is a practical starting point, not a universal optimum.

Numerical differentiation also depends on the function being reasonably smooth around the point. At a sharp corner, jump, or point where the function is undefined, there may be no ordinary derivative to estimate. A numerical result at or near such a point can be misleading even if VBA returns a number.

Create a VBA derivative function

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module and paste the code below into the new standard module. This example defines both a numerical derivative and an exact derivative for the same sample function, so you can compare the results.

Option Explicit

Public Function DerivativeAtX(ByVal x As Double) As Double
    Dim h As Double

    ' Scale the step with the input while keeping it nonzero.
    h = 0.000001 * (1# + Abs(x))
    DerivativeAtX = (SampleFunction(x + h) - SampleFunction(x - h)) / (2# * h)
End Function

Public Function DerivativeExact(ByVal x As Double) As Double
    ' Exact derivative of t cubed plus 2t.
    DerivativeExact = 3# * x ^ 2 + 2#
End Function

Private Function SampleFunction(ByVal t As Double) As Double
    ' Example original function: t cubed plus 2t.
    SampleFunction = t ^ 3 + 2# * t
End Function

SampleFunction is the original function being evaluated. To use the numerical method for a different function, replace the expression assigned to SampleFunction with your own calculation, then check that the new function is valid at both x+h and x-h. If you change the original function, also change DerivativeExact to its correct derivative—or remove that function if you do not need the exact comparison.

Use the UDF in a worksheet

With an input value in cell A2, enter =DerivativeAtX(A2) in another cell to calculate the numerical estimate. For the sample function, if A2 contains 2, the result will be close to 14. The tiny difference from 14, if displayed with enough decimal places, comes from numerical approximation and floating-point rounding.

With the same input value in A2, enter =DerivativeExact(A2) to calculate the exact derivative coded for this particular example. At 2, it returns 14. These formulas work because the VBA functions are public and reside in a standard module. A function placed in a worksheet’s code module or marked Private may not be available as a normal worksheet UDF.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) to retain the VBA code. When reopening it, Excel may ask whether to enable macros; only enable macros in workbooks you trust. On Mac, Excel also supports VBA UDFs, but the Visual Basic Editor shortcut can depend on the keyboard and function-key settings. Use the Developer tab’s Visual Basic command if Alt+F11 is not available.

Exact derivatives versus numerical estimates

For a simple, known function, an exact derivative is usually the better choice. It avoids the step-size trade-off, usually evaluates faster, and returns the algebraic derivative directly. In the example, DerivativeExact calculates 3x squared plus 2; it is exact for the defined polynomial, apart from the normal limits of computer number representation.

A numerical derivative is more adaptable when the original function is complicated, comes from a model, or is available only as a calculation procedure. However, this sample UDF does not accept an arbitrary Excel formula as an argument. It calls a particular VBA function named SampleFunction. To use a different model, edit that function or write another set of functions for the model. Passing formula text and evaluating it dynamically is a separate design problem and can introduce formula-context, error-handling, and security concerns.

For data in a table rather than a known function, the task is often estimating slopes between observations, not differentiating a formula. You can calculate finite differences in worksheet cells or write code that reads the data, but unevenly spaced input values require dividing each output change by its corresponding input change. Do not treat the row-to-row change in output alone as a derivative unless the input spacing is one unit.

Accuracy and choosing a step size

The step size in the sample is deliberately visible in the code so you can adjust it for your model. A step that works well for inputs near 1 may not work well for inputs near millions, or for a function whose output changes on a very small scale. Compare estimates using several plausible step sizes and inspect whether they settle toward a stable value. Also compare against a known exact derivative at selected points when one is available.

Central differences require valid function values on both sides of the input. If your function is defined only for nonnegative values, for example, an input very close to zero could make x-h invalid. In that situation, a one-sided difference may be needed at the boundary, with appropriate code and an understanding that its error behavior differs. Likewise, functions with noise, conditional jumps, or iterative approximations may produce unstable derivatives.

Think about units when interpreting the result. If the input is measured in seconds and the output in meters, the derivative is a rate in meters per second. A large derivative is not automatically an error; it may reflect the units or a steep part of the function. Check the model, input scale, and expected units before drawing conclusions from the result.

Common problems and checks

  • The worksheet shows #NAME? Confirm that the code is in a standard module, the function is Public, and the workbook containing it is open with macros permitted by your organization’s settings.
  • The function returns an error or will not compile. In the Visual Basic Editor, choose Debug > Compile VBAProject and fix any highlighted syntax issue. Confirm that the expression in SampleFunction returns a number for both nearby inputs.
  • The estimate is unexpectedly different from the exact result. Check that SampleFunction and DerivativeExact describe the same original function. Then test a different step size and verify that the input is not at a boundary, discontinuity, or point where the function is undefined.
  • The result does not seem to update. Check Excel’s calculation mode under Formulas > Calculation Options. Re-enter or recalculate the formula if necessary. This UDF is not volatile, which is appropriate because its result depends on its argument rather than on changing external state.
  • The UDF is being used as if it were a macro. A worksheet UDF returns a value to a cell; it is not meant to edit other cells or workbook structure. Use a Sub procedure for actions that change the workbook.

VBA safety and workbook behavior

The two functions shown here only calculate and return numbers; they do not modify cells, files, or settings. Still, VBA code can perform actions beyond calculation, so review unfamiliar code before enabling macros. A derivative UDF is not a security feature and should not be used to run untrusted code.

If you adapt the example into a macro that changes worksheet data, save a copy of the workbook before running it. Macro actions generally cannot be reversed with Ctrl+Z, and Excel clears the normal undo history when VBA changes workbook contents. On Mac, the equivalent undo shortcut is Command+Z, but that does not restore changes made by a macro either.

Frequently Asked Questions

Does Excel have a built-in derivative function?

No. Excel does not provide a general worksheet function that symbolically differentiates an arbitrary formula. You can calculate an exact derivative by entering its expression, estimate one with VBA or worksheet calculations, or use specialist mathematical software for symbolic algebra.

Can VBA derive any Excel formula automatically?

Not with the example shown. It estimates the derivative of the function coded in SampleFunction. Automatically parsing arbitrary Excel formulas and generating symbolic derivatives requires substantially more logic than a simple UDF and must account for Excel syntax and functions.

Why is the VBA result not exactly 14?

DerivativeAtX uses nearby sample values, so its result is an approximation affected by the chosen step size and floating-point arithmetic. DerivativeExact returns 14 at an input of 2 for this specific sample function.

Can I use the VBA function in Excel for the web?

Excel for the web can open macro-enabled workbooks, but it does not run VBA code. The UDF will not calculate there as it does in the desktop app. Open the workbook in desktop Excel to use the VBA function, or replace it with a worksheet-based calculation that the web version supports.

Do I need to enable macros just to use a VBA UDF?

Yes. Excel must allow the workbook’s VBA code to run for the custom worksheet function to calculate. Follow your organization’s macro policies, and enable code only in workbooks from a trusted source.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.