Excel VBA Factorial Function: Code, Limits, and Use

VBA & macros · Updated November 21, 2026

Excel already calculates factorials with the FACT worksheet function, but you can also create a VBA function for a reusable custom calculation. Put the code below in a standard VBA module, then enter =FactorialVBA(A1) in a worksheet; it returns the factorial for a nonnegative whole number up to 170 and an Excel #NUM! error for invalid inputs.

What a factorial means

A factorial is the product of a whole number and every positive whole number below it. The notation is an exclamation mark: 5! means 5 × 4 × 3 × 2 × 1, which equals 120. Factorials appear in counting and probability problems, including permutations, combinations, and arrangements where order matters.

Two edge cases matter when writing a function. By convention, 0! equals 1, and 1! also equals 1. A negative number does not have a factorial in the ordinary integer definition used by Excel’s FACT function. The VBA function below treats negative values and non-integers as invalid rather than attempting to calculate them.

Use Excel’s built-in FACT function first

If you only need a factorial in a worksheet, the simplest option is usually =FACT(A1), assuming the number is in A1. This built-in function avoids enabling macros and works in ordinary workbooks. For example, if A1 contains 5, the formula returns 120. If A1 contains 0, it returns 1.

Excel’s FACT function truncates a number with a fractional part to an integer, while the custom VBA function in this article deliberately rejects fractional inputs. For instance, =FACT(5.8) calculates the factorial of 5, whereas =FactorialVBA(5.8) returns #NUM!. This difference is intentional: rejecting a fraction can help catch unexpected data instead of silently changing it.

Excel’s built-in function is also a good choice when sharing a workbook with people who cannot or should not run macros. Use VBA when you need a named custom function, need to adapt the validation or calculation for a larger workflow, or are learning how Excel calls user-defined functions.

VBA factorial function

The following implementation uses a loop rather than recursion. It checks that the input is a nonnegative integer, returns 1 for 0 and 1, and limits the input to 170 because a VBA Double cannot represent the size of 171! within its numeric range. The answer is a floating-point value, so large results are approximate rather than exact integers.

Public Function FactorialVBA(ByVal n As Double) As Variant
    Dim i As Long
    Dim result As Double

    ' Reject negative, fractional, or excessively large inputs.
    If n < 0# Or n <> Fix(n) Or n > 170# Then
        FactorialVBA = CVErr(xlErrNum)
        Exit Function
    End If

    ' Start at 1 so both 0! and 1! return 1.
    result = 1#
    For i = 2 To CLng(n)
        result = result * i
    Next i

    FactorialVBA = result
End Function

The function’s return type is Variant so it can return either a numeric result or an Excel error value created by CVErr. The input is a Double so the function can inspect a fractional value and reject it; declaring the argument as a Long would convert the input before the function could perform that check. The loop counter is a Long, which is sufficient because the largest accepted input is only 170.

Add the function to an Excel workbook

  1. Save a copy of the workbook before running or adding macros. Ctrl+Z cannot undo a macro’s actions, and a backup makes it easier to recover from mistakes.
  2. Save the macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As. A standard .xlsx workbook cannot store VBA code.
  3. Open the Visual Basic Editor. In Excel for Microsoft 365 on Windows, press Alt+F11. If the Developer tab is visible, you can also choose Developer > Visual Basic.
  4. In the editor, choose Insert > Module, paste the function into the new standard module, and close or switch away from the editor.
  5. In a worksheet, enter a whole number in A1 and type =FactorialVBA(A1) in another cell. Press Enter. For A1 equal to 5, the result is 120.

If the Developer tab is not visible in Windows Excel, enable it through File > Options > Customize Ribbon and select Developer. On a Mac, Excel supports VBA, but keyboard shortcuts and some security controls differ; use the Developer tab’s Visual Basic command when available, or open the Visual Basic Editor from Excel’s Tools menu. Excel for the web cannot create or run VBA macros, so use desktop Excel to add and calculate this user-defined function.

Using the function in cells and other VBA code

Once the function is in a standard module, it can be called from worksheet formulas like a built-in function. With the input in A2, enter =FactorialVBA(A2). Fill the formula down to calculate for other rows. If the input cell changes, Excel recalculates the formula when calculation is set to automatic. A UDF should calculate from its arguments; this function does not depend on unrelated cells or change workbook contents.

You can also call a public function from another VBA procedure. For example, a macro can assign the returned value to a variable or write it to a cell. That is useful when a larger VBA process needs the same calculation, although a worksheet formula is easier to inspect and update for a simple table. Keep the function in a standard module rather than placing it in a worksheet’s code module if you want it available as a normal worksheet function.

After editing the code, save the workbook and check the formula result. If the function name is not recognized, confirm that the code compiled, that the module is in the same open workbook, and that macros are enabled for that trusted file. A formula referring to a VBA function cannot be relied on in an environment that does not support VBA.

Precision and numeric limits

The maximum input of 170 is a range limit for a VBA Double, not a promise of exact integer results through 170. A Double has finite precision. Factorials grow so quickly that results above 18! exceed the range in which every integer can be represented exactly by a Double. Therefore, 19! and larger values returned by this function are approximate, even though they remain within the Double range through 170!.

For a sense of scale, 18! is 6,402,373,705,728,000, which is below 253, the largest threshold through which all consecutive integers are exactly representable in the usual binary floating-point format. The next factorial, 19!, is 121,645,100,408,832,000, so it is not represented as an exact integer by a Double. Excel cells may display large results in scientific notation or round the displayed digits; changing the cell’s number format changes the display, not the underlying precision.

If exact large-integer factorials are essential, a standard VBA numeric type is not enough. You would need an arbitrary-precision or big-integer approach, such as representing the result as a string and implementing digit-by-digit multiplication, or using a suitable external library. That is usually unnecessary for ordinary spreadsheet analysis, but it matters for exact combinatorics or educational work where every digit must be correct.

Common errors and safer macro use

  • #NAME?: Excel may not recognize the function if the VBA code is missing, the name is misspelled, macros are unavailable, or the workbook is being used in Excel for the web. Check the function name and open the saved .xlsm file in desktop Excel.
  • #NUM!: The function intentionally returns this for negative inputs, fractional values, or values above 170. Check the source value and the stated limit instead of assuming the error means Excel’s calculation engine failed.
  • Macro warning or disabled code: Excel may block macros based on the file’s source and security settings. Do not enable all macros globally just to use a workbook from an unknown source. Use files you trust and follow your organization’s security rules.
  • Unexpected fraction handling: This function rejects decimals, while FACT truncates them. Decide which behavior suits the data and document it for workbook users.
  • Very large displayed numbers: Scientific notation and rounded display are normal for large results. Formatting a cell cannot restore digits that the numeric type did not store exactly.

Macros cannot be undone with Ctrl+Z. Keep a separate copy before running any macro that changes data, and review code before enabling it. The factorial function shown here only returns a value; it does not edit cells, delete data, or change workbook settings.

When to choose VBA, FACT, or another approach

Choose FACT for a straightforward worksheet calculation, especially when the workbook will be shared broadly or macro-free compatibility matters. Choose the VBA version when the project genuinely needs custom validation, a reusable user-defined function, or a calculation that will be integrated into other VBA code. Avoid VBA simply to reproduce a function Excel already provides unless its different input handling is useful.

For a small factorial, a direct multiplication formula is possible, but it becomes cumbersome and error-prone as the input grows. For combinations, Excel also provides the COMBIN function, which calculates the number of combinations without requiring users to build factorial expressions themselves. Use a function that matches the mathematical question: a factorial counts arrangements of all items, while a combination counts selections where order does not matter.

Remember that worksheet UDFs are not the same as built-in Excel functions. They require the workbook’s VBA project, and they are unavailable in Excel for the web. If collaborators need the results but cannot run macros, replace the UDF formulas with built-in functions where possible, or distribute calculated values with a clear explanation of how they were obtained.

Frequently Asked Questions

Does Excel have a built-in factorial function?
Yes. Use =FACT(A1) when the number is in A1. VBA is optional; it is mainly useful for custom behavior or when integrating the calculation into VBA code.

Why does 0! equal 1?
It is the standard mathematical convention, and it keeps many counting formulas consistent, including formulas for combinations. Both FACT(0) and the VBA function above return 1.

Why does the VBA function return #NUM! for 5.5?
The code is designed to accept whole numbers only. This avoids silently changing an input with a fraction. Excel’s built-in FACT instead truncates a fractional input to an integer.

Can I calculate 171! with this function?
No. The code rejects inputs over 170 because the result exceeds the range of a VBA Double. Even for inputs from 19 through 170, treat the returned value as approximate rather than an exact integer.

Will the function work in Excel for the web?
No. Excel for the web does not run VBA macros or calculate VBA user-defined functions. Open the macro-enabled workbook in desktop Excel, or use the built-in FACT function for a calculation that does not require VBA.

Can I save the code in an .xlsx workbook?
No. Save the workbook as .xlsm to retain VBA code. If you save it as .xlsx, Excel removes the macro project; use a backup and confirm the file type before saving.

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.