Course contents · Macros and VBA

User-defined functions

Open the practice in the trainer →

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

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.

Tasks in the VBA editor

  1. The function VAT(amount) returns 20% of the amount — column C recalculates by itself.
  2. The function DISCOUNT(amount): 10% of the amount if it is at least 1000, otherwise 0.
  3. The function INITIALS(fullName): "John Smith" → "J.S." (use Left, Mid, InStr).
  4. The function SUMEVEN(rng): the sum of the even numbers in a range (For Each and Mod). The result is in H1.

Solve the tasks in the trainer →

Check yourself

  1. How does a VBA function return its result?
  2. Where are your functions in the Insert Function dialog?