Practical VBA workshop

Goal: use ordered Case Is ranges for banded calculations without presenting illustrative rates as current tax advice.

A training model calculates a fee from sample bands. The structure is reusable for commissions, rebates and service charges; the numbers are deliberately illustrative.

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 IllustrativeBandFee(ByVal amount As Double) As Double
    Select Case amount
        Case Is < 0
            Err.Raise 5, , "Amount cannot be negative"
        Case Is <= 10000
            IllustrativeBandFee = 0
        Case Is <= 25000
            IllustrativeBandFee = (amount - 10000) * 0.1
        Case Is <= 50000
            IllustrativeBandFee = 1500 + (amount - 25000) * 0.2
        Case Else
            IllustrativeBandFee = 6500 + (amount - 50000) * 0.3
    End Select
End Function

How the solution works

• Test thresholds in ascending order because the first matching Case is used.
• Separate the fixed amount from the marginal amount so each band connects continuously to the previous band.
• Keep real statutory rates in a dated table rather than hard-coding them; laws and effective dates change.

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

• Treating an old example rate table as current law can produce a materially wrong result.
• Using < on one boundary and <= on another can create gaps or double treatment.
• Rounding each intermediate band introduces drift; round the final result according to the documented policy.

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

Move the thresholds and rates into a worksheet table and write a lookup-based version that can be updated without editing VBA.

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.