Practical VBA workshop

Goal: test prime numbers efficiently and explain why the square-root limit is sufficient.

A learning exercise validates identifiers that must be prime, while demonstrating early exits and loop optimization.

What you will build

• A complete procedure that works without Select or Activate.
• Test data and expected behaviour you can verify before using real files.
• Error cases and production checks that prevent silent damage.

Complete VBA example

Option Explicit
Public Function IsPrime(ByVal n As Long) As Boolean
    Dim divisor As Long
    If n < 2 Then Exit Function
    If n = 2 Then IsPrime = True: Exit Function
    If n Mod 2 = 0 Then Exit Function
    divisor = 3
    Do While divisor <= Sqr(n)
        If n Mod divisor = 0 Then Exit Function
        divisor = divisor + 2
    Loop
    IsPrime = True
End Function

How the solution works

• Numbers below two are not prime; handle that boundary before entering the loop.
• After testing two, only odd divisors need to be checked.
• If n has a factor larger than its square root, the paired factor is smaller than the square root and would already have been found.

Run it step by step

1. Download and unzip the practice pack below. Open the matching CSV or copy it into a new macro-enabled workbook.
2. Press Alt + F11, choose File → Import File, and import this chapter’s .bas module.
3. Rename the sample worksheet to the name used in the code, or change the worksheet name in one clearly marked line.
4. Run the procedure with F5. Compare the result with the expected values and boundary cases in the CSV.
5. Undo is not available after a macro changes a workbook. Test on a copy, save only after checking totals, and keep the original source file.

Common mistakes

• Looping from 2 to n-1 works but becomes unnecessarily slow as values grow.
• Treating one as prime is a common mathematical error.
• Long overflows above roughly 2.1 billion; use LongLong only in supported 64-bit VBA and still validate the input.

Make it production-ready

Add Option Explicit, qualify every workbook and worksheet, validate the last row and input types, and use a single cleanup section to restore ScreenUpdating, EnableEvents and calculation settings after an error. Avoid hard-coded regulatory rates or colour-only business rules. Record the source, effective date and assumptions in a visible Notes sheet.

Practice challenge

Read integers from column A, write TRUE or FALSE in column B, and record the smallest divisor for composite numbers in column C.

Test normal data, blanks, text in numeric columns, duplicate values and the exact threshold boundaries. A useful macro should fail clearly rather than produce a plausible but wrong answer.