12. Macros and VBA

12.13 User-Defined Functions: a Delivery Fee Function

By Ravindra Bagale · Last updated: 25 September 2026

A Function returns a value and can be used in worksheet formulas like a built-in function. It must be in a standard module.

Public Function DeliveryFee(ByVal orderAmount As Double, _
                            Optional ByVal isFestival As Boolean = False) As Double
    ' Fictional slabs: <99 = 30, 99-198 = 25, 199-498 = 15, 499+ = free
    Dim fee As Double
    Select Case orderAmount
        Case Is >= 499: fee = 0
        Case Is >= 199: fee = 15
        Case Is >= 99: fee = 25
        Case Else: fee = 30
    End Select
    ' Festival surcharge of Rs 10 on paid deliveries (e.g. Diwali evenings)
    If isFestival And fee > 0 Then fee = fee + 10
    DeliveryFee = fee
End Function

It appears in Formulas › Insert Function under User Defined. The workbook must be .xlsm; to use it in every workbook, save it in an add-in (.xlam) or the Personal Macro Workbook (then call =PERSONAL.XLSB!DeliveryFee(G2)).