Ravindra BagaleCourses & study guides

16. Practice Exercises with Answer Hints

16.11 Module 12: Macros and VBA

By Last updated:

  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.

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.