Besides macros (Sub), a module can hold functions (Function). A function is called right in a sheet formula, like SUM or IF: =VAT(B2).
Function VAT(amount)
VAT = amount * 0.2 ' the result is assigned to the function name
End Function
- The result of a function is the value assigned to its name.
- An argument can be a range:
Function SUMEVEN(rng As Range), and inside —For Each c In rng. - A worksheet function must not change other cells or formatting — it only returns a value.
- Find your functions in the Insert Function dialog: Shift+F3 → category User Defined.
In the trainer the formulas with your functions are already on the sheet and show #NAME?. As soon as a function appears in the module they recalculate by themselves — you don't even have to click Run.
Without VBA: in Excel for Microsoft 365 you can define your own function with a LAMBDA formula in the Name Manager — e.g. the name VAT = =LAMBDA(x, x*0.2). Such a workbook stays .xlsx.