16. Practice Exercises with Answer Hints

16.11 Module 12: Macros and VBA

By Ravindra Bagale · Last updated: 25 September 2026

1. 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.
2. 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.
3. 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).
4. Delete all Cancelled rows. Hint: loop backwards: For r = lastRow To 2 Step -1.
5. List all sheet names on a new sheet. Hint: For Each ws In ThisWorkbook.Worksheets.
6. 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).
7. 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.