Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 37 ยท Projects and Practice

VBA Practice Exercises with Solutions

Twelve VBA practice exercises across Excel, Word, Outlook, files, forms and error handling, with hints, solution outlines and runnable logic checks in JavaScript.

Reading about VBA is not enough: you learn by writing macros and watching them break. This page collects twelve exercises that cover the whole course, from ranges and formulas to forms, files and APIs. Each has a goal, a hint and a solution outline. Try the exercise before reading the outline.

VBA cannot run on this site, so type the solutions into the VBA editor of your own Excel (Alt+F11, Insert > Module). The JavaScript TryIts let you check the logic of two exercises in your browser first.

Warm-ups: ranges and loops

1. Highlight the big ones. In Data, color every cell in column D red when its value is above 1000. Hint: find the last row with End(xlUp), then loop, or read an array and use conditional formatting for the display. Solution outline: For r = 2 To lastRow, test ws.Cells(r, 4).Value2 > 1000, set .Font.Color = vbRed. Better: add a conditional format once.

2. Fill in totals. Write a macro that adds a Total column equal to Qty * Price for every row and a grand total below. Hint: assign one formula to the whole block (Range("D2:D20").Formula = "=B2*C2").

3. Remove blank rows. Delete every row where column A is empty. Hint: loop backwards from the last row so row numbers do not shift.

VB
For r = lastRow To 2 Step -1
    If Len(ws.Cells(r, 1).Value2) = 0 Then ws.Rows(r).Delete
Next r

Workbooks, sheets and data

4. One sheet per region. From a Data sheet with a Region column, create a sheet for every distinct region and copy that region's rows to it. Hint: use a Dictionary to collect distinct regions, AutoFilter or a loop to copy rows; check SheetExists before adding a sheet.

5. Combine workbooks. Loop over all .xlsx files in a folder and copy the first sheet of each into one summary workbook, adding the file name in column A. Hint: Dir loop, Workbooks.Open ReadOnly:=True, close each file when done.

6. Frequency table. Count how many times each word appears in a column and write a sorted table. Hint: dictionary counting, then write keys and values, then sort the output range.

Word, Outlook and documents

7. Form letters. From a list of customers in Excel, create one Word document per customer from a template with and placeholders, saved as PDF. Hint: CreateObject("Word.Application"), Find.Execute Replace:=wdReplaceAll, ExportAsFixedFormat, and Quit Word at the end.

8. Mail the reports. Create (with .Display, not .Send) one e-mail per row with the right attachment. Hint: check that the attachment exists with Dir before adding it.

Forms, events and errors

9. A data-entry form. Build a UserForm with name, quantity and category fields, full validation and an Add button that writes to the next empty row. Pressing Enter should add; Esc should cancel. Hint: cmdAdd.Default = True, cmdCancel.Cancel = True, collect all validation messages in one string.

10. A change log. Use Worksheet_Change to record the time, user and old/new value of any edit in a Log sheet. Hint: you need the old value: store a copy in Worksheet_SelectionChange, and use Application.EnableEvents = False when writing to the log.

11. Robust importer. Write ImportCsv(path) that reports an error if the file is missing, skips blank lines, and always closes the file, even after errors. Hint: On Error GoTo, Close #f in the clean-up block, Dir(path) = "" check first.

Challenge

12. Mini invoice generator. A Products sheet and an Orders sheet feed a macro that builds a formatted invoice sheet (customer, line items with quantity times price, VAT and total), exports it as PDF named Invoice_<number>.pdf and logs it in a Log sheet. Use constants for the VAT rate and column numbers, and handle a missing product with a readable message.

Check the logic first

Two exercises have logic you can test before writing VBA.

JS Exercise 3: delete rows backwards

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Exercise 6: word frequency table

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Self-review checklist

After each exercise, ask:

  • Does it start with Option Explicit and use typed variables?
  • Does it avoid Select/Activate, and use arrays for big data?
  • What happens with empty data, one row, text in a number column?
  • Are ScreenUpdating, EnableEvents and DisplayAlerts restored after an error?
  • Would a colleague understand the names and comments? (See best practices.)

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. Why loop backwards when deleting rows?

    After deleting row r, the old row r+1 becomes r, and a forward loop would skip it.
  2. 2. What is a good way to test code that e-mails customers?

    Displaying the message lets you check it before any customer receives it.
  3. 3. What is the best way to count occurrences of each distinct value?

    A dictionary updates each count in one pass.

Summary

  • Practice with real tasks: highlighting, totals, sheet splitting, combining files, letters, e-mails, forms and logs.
  • Delete in loops from the end; use dictionaries for counting and grouping.
  • Test with Display, small data and edge cases, and restore application settings after errors.
  • After the exercises, review your code against the best-practices checklist.

Next: VBA track overview