VBA II — Logic, Functions & Events

Learning objectives

By the end you can:

1. Branch cleanly with If…ElseIf…Else…End If and choose Select Case (including Case Is, Case x To y, and Case Else) when a single value is tested against many bands.
2. Write all three loop families from memory, For…Next (with Step and Exit For), For Each…Next over a collection, and Do While / Do Until (top- and bottom-tested), and pick the right one for the job.
3. Declare and use static and dynamic arrays, grow a dynamic array with ReDim Preserve, and read a whole range into a Variant array for speed.
4. Use a Scripting.Dictionary for fast keyed lookups, de-duplication, and counting, using .Exists, .Add, .Item, .Keys, and .Count, and control key case-sensitivity with CompareMode.
5. Take apart and rebuild text with Left, Right, Mid, InStr, InStrRev, Split, Join, Replace, and Trim.
6. Write your own worksheet functions (UDFs) with typed arguments: e.g. =GSTNet(amount, rate), put them in a standard Module, and call them from the grid; and state the two rules a UDF must obey (return to its own cell only; avoid needless volatility).
7. Wire up workbook and worksheet events (Worksheet_Change and Workbook_Open) and prevent an event from firing itself recursively by toggling Application.EnableEvents.
8. Handle errors robustly with On Error GoTo, use On Error Resume Next narrowly and deliberately, read the Err object, and reset with On Error GoTo 0.
9. Debug like a professional: set a breakpoint, step with F8, watch a variable, and print to the Immediate window with Debug.Print.

Prerequisites & connections

Builds on. EX6.01 (VBA I. Recorder to Real Code) is the hard prerequisite: you need the Visual Basic Editor tour, the Sub procedure, the Range/Cells/Worksheet/Workbook object model, With blocks, variable declaration with Dim, the Option Explicit law, and the .Select anti-pattern, this module assumes every one of them without re-teaching it. You also lean on the spreadsheet thinking of the whole EX1 belt: the banding logic of EX1.02's slab-tax and commission tables reappears here as a Select Case and as a UDF, and the exact-match discipline of lookups reappears as Dictionary.Exists. From EX0.02, the IF/IFS worksheet logic is the grid-formula cousin of the If/Select Case you now write in code.

Feeds forward. EX6.03 (VBA III. UserForms, Files & Speed) puts a real input dialog in front of these routines, loops over a folder of workbooks with the same For Each you learn here, and turns the "read a range into an array" trick into a genuine performance discipline (arrays over cells, ScreenUpdating off). EX6.04 (VBA IV. Automation Capstones) assembles all of it into one-click report generators and a model-checker add-in. On the analyst track, this is the machinery behind automation you will want everywhere: a UDF library for repeated finance formulas (EX7.01's LAMBDA-and-UDF reusable logic), a Worksheet_Change guard that keeps a model's inputs valid, and a Dictionary-driven consolidation that stacks many broker files into one (EX7.02's portfolio tracker). The first-udf badge you earn on this module's lab marks the moment you stopped being limited to the functions Microsoft shipped.

If and Select Case

First principles. A program branches when it does different things depending on what it finds. The general-purpose branch is If:

`` If sales >= 2000000 Then rate = 0.08 ElseIf sales >= 1000000 Then rate = 0.06 ElseIf sales >= 500000 Then rate = 0.04 Else rate = 0.02 End If ``

Read the rules that make this correct. Excel tests each condition top to bottom and stops at the first True, which is exactly why the bands are written largest threshold first. Reverse them and everyone would match the >= 500000 line and every high earner would be paid 4%. The Else catches everything that fell through. The block form (If … Then on its own line, closed by End If) is the one to use for anything beyond a trivial one-liner; the single-line form If x > 0 Then y = 1 has no End If and does not scale to two statements without becoming unreadable.