12.13 User-Defined Functions: a Delivery Fee Function
User-defined function (DeliveryFee) with example — in 30 seconds
What: A user-defined function is your own worksheet function written in VBA. You use it like SUM or XLOOKUP.
Why: The fee rules live in one place. Every sheet that calls DeliveryFee gets the same answer.
Tiny example: =DeliveryFee(270) gives 15. On a festival evening =DeliveryFee(270, TRUE) adds ₹10 and gives 25.
User-defined function (DeliveryFee) with example — 30 सेकंदात
काय: User-defined function म्हणजे VBA मध्ये लिहिलेलं तुमचं स्वतःचं worksheet function. ते SUM किंवा XLOOKUP सारखं वापरता येतं.
का: Fee चे नियम एकाच ठिकाणी राहतात. DeliveryFee वापरणाऱ्या प्रत्येक sheet ला एकच उत्तर मिळतं.
छोटं उदाहरण: =DeliveryFee(270) उत्तर 15 देतो. सणाच्या संध्याकाळी =DeliveryFee(270, TRUE) ₹10 जोडतो आणि 25 देतो.
User-defined function (DeliveryFee) with example — 30 सेकंड में
क्या: User-defined function यानी VBA में लिखा आपका अपना worksheet function. इसे SUM या XLOOKUP की तरह इस्तेमाल करते हैं.
क्यों: Fee के नियम एक ही जगह रहते हैं. DeliveryFee इस्तेमाल करने वाली हर sheet को एक ही जवाब मिलता है.
छोटा उदाहरण: =DeliveryFee(270) जवाब 15 देता है. त्योहार की शाम =DeliveryFee(270, TRUE) ₹10 जोड़कर 25 देता है.
First look at the data (before → after):
Expected result — Calls in the worksheet
| Formula | Result (₹) |
|---|---|
| =DeliveryFee(64) | 30 |
| =DeliveryFee(150) | 25 |
| =DeliveryFee(270) | 15 |
| =DeliveryFee(270, TRUE) | 25 |
| =DeliveryFee(1299, TRUE) | 0 |
About this data: Teaching sample for a fictional shop “QuickCart Delivery”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on user defined functions a delivery fee function.
Before table and formula as text
Before — Fee rules (fictional)
| Order amount | Fee (₹) |
|---|---|
| below 99 | 30 |
| 99 to 198 | 25 |
| 199 to 498 | 15 |
| 499 and above | 0 |
| Festival | +10 on paid fees |
About this data: Demo table only — not real customer or company data. Built to practise user defined functions a delivery fee function.
Formula / click path
Public Function DeliveryFee(ByVal orderAmount As Double, Optional ByVal isFestival As Boolean = False) As Double
Select Case … (slabs) … If isFestival And fee > 0 Then fee = fee + 10
Do it step by step:
- Press Alt + F11 and click Insert › Module.
- Paste the DeliveryFee function from this section.
- Save the workbook as Excel Macro-Enabled Workbook (*.xlsm).
- In a cell, type
=DeliveryFee(270)and press Enter. - Try
=DeliveryFee(270, TRUE)and=DeliveryFee(1299, TRUE). Check 25 and 0.
स्टेप बाय स्टेप करून बघा:
- Alt + F11 दाबा आणि Insert › Module वर click करा.
- या section मधलं DeliveryFee function paste करा.
- Workbook Excel Macro-Enabled Workbook (*.xlsm) म्हणून save करा.
- एका cell मध्ये
=DeliveryFee(270)type करा आणि Enter दाबा. =DeliveryFee(270, TRUE)आणि=DeliveryFee(1299, TRUE)करून बघा. 25 आणि 0 check करा.
स्टेप बाय स्टेप करके देखें:
- Alt + F11 दबाएँ और Insert › Module पर click करें.
- इस section का DeliveryFee function paste करें.
- Workbook को Excel Macro-Enabled Workbook (*.xlsm) के रूप में save करें.
- किसी cell में
=DeliveryFee(270)type करें और Enter दबाएँ. =DeliveryFee(270, TRUE)और=DeliveryFee(1299, TRUE)करके देखें. 25 और 0 check करें.
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 |
About this data: Made-up rows so you can copy the pattern for user defined functions a delivery fee function. Currencies may use ₹, £, € or $.
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 tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of user defined functions a delivery fee function on a blank sheet for NovaCraft Tools.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Aamir Khan and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Liam Murphy.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply user defined functions a delivery fee function to a 5-row made-up NovaCraft Tools table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with user defined functions a delivery fee function.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as NovaCraftTo_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Total order value (Easy): Add up Order value (£) in C2:C11.
- Total fees (Easy): Add up Fee (£) in E2:E11.
- Due rows (Easy): How many rows have Status "Due"?
- Farthest order (Easy): Largest Distance km in D2:D11.
- Pune rows (Easy): How many rows have City "Pune"?
- Due fees total (Medium): Total Fee (£) where Status is "Due".
- Distance over 8 (Medium): How many orders have Distance km > 8?
- Fee of O-101 (Medium): Fee (£) for Order "O-101".
- London value (Medium): Total Order value (£) where City is "London".
- Share Due fees (Hard): Due fees / all fees, 3 decimals.
- Avg fee (Hard): Average Fee (£), 2 decimals.
- Far flag (Hard): "Far" if D2 > 8 else "Near".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
