A Function in a standard module can be called from a cell like a built-in function. Great for reusable business logic; limited because it can only return a value.

Code

=Double(A1)   ' after writing Function Double in a module

• Call from a cell

  =Double(A1) ' after writing Function Double in a module → The function runs in the cell

  The module must be a standard module.

• Business rule

  Function FiscalYear(d As Date) As Long FiscalYear = Year(d) + IIf(Month(d) >= 7, 1, 0) End Function → =FiscalYear(A2) gives the July-start fiscal year

  Business rules in one place.

• Volatile UDF

  Application.Volatile → Placed inside a UDF, recalculates it on every change

  For functions depending on things Excel cannot see.

• UDFs cannot change other cells or formatting; they only return a value.
• UDFs cannot change other cells, formatting, or sheets; they only return.
• Pass ranges as Range parameters and read .Value inside.
• Errors inside a UDF show as #VALUE! in the cell.

• Slow UDFs multiplied over thousands of cells drag the workbook.
• Users with macros disabled see #NAME?.