16. Practice Exercises with Answer Hints
16.11 Module 12: Macros and VBA
- Record a macro that formats the header row bold with a green fill; run it on another sheet.
Hint: Developer › Record Macro; save as
.xlsm. - Write a macro that shows the last used row of the Orders sheet in a MsgBox.
Hint:
ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. - Loop through column C and trim every city name.
Hint:
For r = 2 To lastRow: ws.Cells(r, 3).Value = Trim(ws.Cells(r, 3).Value): Next r(see 12.12 B). - Delete all Cancelled rows.
Hint: loop backwards:
For r = lastRow To 2 Step -1. - List all sheet names on a new sheet.
Hint:
For Each ws In ThisWorkbook.Worksheets. - Write a UDF
GSTAmount(amount, rate)that returns amount × rate, rounded to 2 decimals. Hint:Function GSTAmount(amount As Double, rate As Double) As Double…GSTAmount = Round(amount * rate, 2). - Add error handling so the macro shows a friendly message if the sheet "Orders" doesn't exist.
Hint:
On Error GoTo ErrHandler…ErrHandler: MsgBox Err.Description.
Ravindra Bagale's Tip
Many students write code and Run it directly, and can't tell where it went wrong. Run it one line at a time with F8, watch the variables in the Locals window, and always take a backup of the file before running a macro – Ctrl + Z doesn't work after a macro.
Ravindra Bagale's Tip – मराठी
बरेच students code लिहून थेट Run करतात आणि चूक कुठे झाली ते कळत नाही. F8 ने एक एक line चालवा, Locals window मध्ये variables बघा, आणि नेहमी file चा backup घेऊन macro चालवा – macro नंतर Ctrl + Z चालत नाही.
Ravindra Bagale's Tip – हिंदी
बहुत से students code लिखकर सीधे Run कर देते हैं और पता नहीं चलता गलती कहाँ हुई. F8 से एक-एक line चलाओ, Locals window में variables देखो, और हमेशा file का backup लेकर macro चलाओ – macro के बाद Ctrl + Z नहीं चलता.
Worked example — module 12 macros and vba
Why
Priya Deshmukh in Pune and Emma Clarke abroad both need a clear, repeatable way to use module 12 macros and vba on a NovaCraft Tools sheet without breaking totals.
What
Module 12 Macros And Vba is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for module 12 macros and vba.
- Enter a small made-up value for Priya Deshmukh using € where money appears.
- Add a second value for Emma Clarke using £ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once module 12 macros and vba is correct on one row, copy the same pattern down the column.
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 module 12 macros and vba on a blank sheet for Orbit Logistics.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Rohan Patil 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 James O'Neill.
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 module 12 macros and vba to a 5-row made-up Orbit Logistics 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 module 12 macros and vba.
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 OrbitLogist_practice.xlsx.
Hint: Use a clear file name for class.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
