Practical VBA workshop

Goal: combine multiple tests with And, Or and Not without creating hidden logic errors.

A transaction is ready to post only when it is approved, has a valid account and is not blocked; urgent items may follow a separate review route.

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 PostingStatus(ByVal approved As Boolean, ByVal accountOK As Boolean, ByVal blocked As Boolean, ByVal urgent As Boolean) As String
    If blocked Then
        PostingStatus = "Blocked"
    ElseIf approved And accountOK Then
        PostingStatus = "Ready to post"
    ElseIf urgent And (Not approved Or Not accountOK) Then
        PostingStatus = "Urgent review"
    Else
        PostingStatus = "Pending"
    End If
End Function

How the solution works

• Use parentheses even when precedence would produce the same result; they document the rule for the next maintainer.
• VBA evaluates both sides of And and Or. It does not short-circuit, so never put a risky array or object access on the second side expecting the first test to protect it.
• Name Boolean variables positively—accountOK is easier to reason about than accountNotInvalid.

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

• Mixing And and Or without parentheses changes meaning and is difficult to review.
• Using bitwise-looking numeric flags instead of Boolean values hides invalid states.
• Negating a long expression with Not often creates double negatives; extract it into a clearly named variable.

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

Create a truth table for all 16 combinations and compare it with the function output in the Immediate window.

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.