12.13 User-Defined Functions: a Delivery Fee Function
In short: A Function returns a value and can be used in worksheet formulas like a built-in function.
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
| Formula | Result |
|---|---|
=DeliveryFee(64) |
30 |
=DeliveryFee(270) |
15 |
=DeliveryFee(270, TRUE) |
25 |
=DeliveryFee(1299, TRUE) |
0 |
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)).
Ravindra Bagale's Tip
Many students try to change other cells' formats or values from a UDF – a function called from the worksheet can't do that; it can only return a value. Assign the value to the function's own name inside the function (DeliveryFee = fee), otherwise the result is 0. And a UDF can be slower than worksheet functions – use it carefully on lakhs of rows.
Ravindra Bagale's Tip – मराठी
UDF मधून इतर cells चे format किंवा values बदलण्याचा प्रयत्न बरेच students करतात – worksheet मधून call केलेल्या function ला ते करता येत नाही, फक्त value return करता येते. Function चं नाव function मधेच value ला assign करा (DeliveryFee = fee), नाहीतर result 0 येतो. आणि UDF worksheet functions पेक्षा slow असू शकतं – लाखो rows वर जरा जपून वापरा.
Ravindra Bagale's Tip – हिंदी
UDF से दूसरी cells के format या values बदलने की कोशिश बहुत से students करते हैं – worksheet से call किया गया function ऐसा नहीं कर सकता, वह सिर्फ़ value return कर सकता है. Function के नाम को function के अंदर ही value assign करो (DeliveryFee = fee), वरना result 0 आता है. और UDF worksheet functions से slow हो सकता है – लाखों rows पर ज़रा संभलकर इस्तेमाल करो.
Practice task
Write a UDF IsLate(mins, Optional limit = 15) returning TRUE/FALSE, and a UDF FinancialYear(d) returning labels like "FY 2026-27". Use both in tblOrders.