VBA II: Logic, Functions & Events

Excel Mastery Track · Brown-2 belt · ~7–9 hours · Microsoft 365 on Windows assumed

In VBA I you learned to stop recording and start writing, to open the Visual Basic Editor, read what the macro recorder had produced, and refactor its .Select-riddled sludge into clean object code that talks to Range, Cells, and Worksheet directly. That gave you a language that can do things in sequence. This module gives that language a mind: the ability to decide (If, Select Case), to repeat (For, For Each, Do), to remember many things at once (arrays and the Dictionary), to take apart and reassemble text (Left, Mid, InStr, Split), to grow its own worksheet functions (UDFs), to react to what the user does (events), and to survive its own mistakes (error handling and debugging). A recorded macro runs the same fourteen lines every time no matter what it meets; a real program looks at the data in front of it and chooses. That difference, from a fixed script to a thinking one, is the whole of this module.

The through-line is a single shift in what a procedure is. A beginner's macro is a to-do list: click here, type there, format this. A professional's is an argument: if this condition holds, do this; for each row that qualifies, act; when the user changes that cell, respond; and if anything goes wrong, catch it and leave the workbook clean. Every construct in this module is a piece of that argument. If/Select Case are the branches. Loops are the "for every one of these" quantifier. The Dictionary is the program's short-term memory. A UDF is a named idea you can reuse from any cell. Events are the program's senses. Error handling is its immune system. Master these seven and you can automate not just the task you have in front of you today, but the whole class of task it belongs to.

One promise about scope: you already met the nouns of VBA in EX6.01, objects, properties, methods, With blocks, variables, and the Option Explicit law that forces you to declare every variable. Now you learn the verbs and grammar that string those nouns into reasoning. We will keep leaning on EX6.01's discipline, Option Explicit at the top of every module, no .Select, fully-qualified ranges, because logic built on sloppy object code is a fast way to write a confident bug. And everything here needs a macro-enabled workbook: save as .xlsm (or .xlsb), because a plain .xlsx silently discards all your code on close.

One version note, stated once: everything here assumes Microsoft 365 desktop on Windows. VBA itself has barely changed in twenty years (the code in this module runs identically on Excel 2016 through 365) but menu paths are given for the Windows ribbon and the Windows Visual Basic Editor (open it with Alt+F11, or Developer ▸ Visual Basic). If the Developer tab is missing, turn it on at File ▸ Options ▸ Customize Ribbon ▸ Developer. The Scripting.Dictionary we lean on is a Windows component; on Mac VBA it is unavailable, and you fall back to a Collection, noted where it matters.

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. Everything that follows assumes you have all of them, and none of them gets re-taught. You also lean on the spreadsheet thinking of the whole EX1 belt. The banding logic of EX1.02's slab-tax and commission tables comes back here as a Select Case and as a UDF, and the exact-match habit you built with lookups comes back 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. It loops over a folder of workbooks with the same For Each you learn here, and it turns the "read a range into an array" trick into a standing performance rule (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'll want everywhere. A UDF library for repeated finance formulas runs on it (EX7.01's LAMBDA-and-UDF reusable logic). So does a Worksheet_Change guard that keeps a model's inputs valid, and so does a Dictionary-driven consolidation that stacks many broker files into one (EX7.02's portfolio tracker). The first-udf badge you earn on the lab here marks the moment you stopped being limited to the functions Microsoft shipped.

Core teaching content

String handling

Real data arrives as messy text, like " MUM|West|4200 ", "Mehta, Priya" or "INV-2024-0087". Half of automation is cutting it into clean pieces and gluing them back together. Here's VBA's string toolkit:

• Left(s, n) / Right(s, n) take n characters from an end. Left("INV-2024-0087", 3) returns "INV".
• Mid(s, start, [length]) takes from the middle, and it's 1-based. Mid("INV-2024-0087", 5, 4) returns "2024". Omit length to take the rest.
• Len(s) is the character count. Trim(s) strips leading and trailing spaces, and LTrim/RTrim strip one side. Use Trim on every cell value you compare. A trailing space is the invisible reason two "equal" strings aren't.
• InStr([start,] haystack, needle) returns the 1-based position of the first occurrence, or 0 if it isn't found. It's the standard "does it contain / where is it" test. InStrRev searches from the right, which is handy for file extensions and the last separator.
• Split(s, delimiter) shatters a string into a 0-based array, and Join(arr, delimiter) is its inverse. Split("MUM|West|4200", "|") gives you Array("MUM", "West", "4200"), so element (2) is "4200".
• Replace(s, find, repl) swaps every occurrence, and UCase/LCase change case.

Here's a tiny worked snippet that pulls the last name from "Mehta, Priya":

Dim s As String, p As Long
s = "Mehta, Priya"
p = InStr(s, ",")                 ' p = 6, the comma's position
Debug.Print Left(s, p - 1)        ' "Mehta"
Debug.Print Trim(Mid(s, p + 1))   ' "Priya"  (Trim removes the leading space)

So never assume a string's shape. Test with InStr before you Mid, guard against 0 (not found), and Trim before you compare. Text that "looks right" hides trailing spaces, mixed case, and stray delimiters. A careful parse neutralises them.

UDFs: your own worksheet functions

A Sub does something. A Function returns a value. When a Function is written in a standard module and takes only inputs, Excel lets you call it from a cell, just like SUM or XLOOKUP. That's a User-Defined Function (UDF). It's how you extend Excel's formula language with an idea it doesn't ship: a GST split, a bespoke margin rule, a company-specific tax band. Here's the anatomy:

Option Explicit

Public Function GSTNet(amount As Double, rate As Double) As Double
    ' Returns the ex-GST (net) value of a GST-inclusive amount.
    GSTNet = amount / (1 + rate)
End Function

Three things make this a worksheet function. First, it's Public, so the grid can see it. Second, it goes in a standard Module. Insert one with Insert ▸ Module in the VBE. A UDF placed in a sheet's code module or in ThisWorkbook won't appear in the grid. Third, it returns its result by assigning to its own name (GSTNet = …). Call it from a cell like any function, as =GSTNet(A2, 0.18). Better still, point the rate at an input cell (=GSTNet(A2, $B$1)) so one edit re-prices the column. Typed arguments such as amount As Double give you Excel's argument ScreenTip and a clean type error on garbage input.

A UDF must obey two rules. First, it can only return a value to the cell that called it. It mustn't try to change other cells, insert rows, rename sheets, or format ranges. When Excel calculates, those side-effects are ignored without a word, or they raise an error. A function's job is to compute, and acting is a Sub's job, so put actions there. Second, avoid needless volatility. A normal UDF recalculates only when one of its arguments changes. Write Application.Volatile at the top, or read "hidden" state like ActiveCell or Now, and it recalculates on every change anywhere in the workbook. On a large model that's death by a thousand recalcs. Make a UDF depend only on its declared arguments and it stays fast and predictable. Both rules come back as common mistakes below, because both are tempting and both bite.

Workbook and worksheet events

Everything so far ran when you asked it to. Event procedures run when something happens: the user edits a cell, the workbook opens, a sheet is activated. Excel calls these pre-named procedures automatically, and your job is to fill in the body. Each one belongs in the code module of the object that owns the event, so a standard module is the wrong place for it.

Worksheet_Change fires whenever a cell's value changes on that sheet. It belongs in the sheet's own code module. To open that module, double-click the sheet in the Project Explorer, for example Sheet1 (Register). Its parameter Target is the range that changed:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub  ' only care about column A
    Application.EnableEvents = False           ' <-- prevent recursion (see below)
    Target.Value = UCase(Trim(Target.Value))   ' tidy every code the user types
    Application.EnableEvents = True
End Sub

Workbook_Open fires once, when the file opens. It belongs in the ThisWorkbook code module:

Private Sub Workbook_Open()
    Worksheets("Register").Range("Z1").Value = "Opened " & Format(Now, "yyyy-mm-dd hh:nn")
    MsgBox "Register ready. Enter codes in column A.", vbInformation
End Sub

Events have one signature hazard, the recursion trap. A Worksheet_Change handler that writes to a cell triggers Worksheet_Change again. That writes again, which triggers it again. The infinite cascade either loops forever or overflows the stack. The cure is to switch events off around your own writes with Application.EnableEvents = False, then switch them back on. There's a catch, though. If your code errors between those two lines, events stay off for the rest of the session and the sheet mysteriously "stops reacting". You'd swear Excel had broken. So a well-built handler re-enables events in an error handler, which you'll see in the next section and in the worked mistakes. The companion habit is Intersect(Target, …) Is Nothing. Bail out straight away unless the change touched the range you care about, so your logic doesn't run on every stray edit.

Common mistakes & how experts think differently

1. Leaving On Error Resume Next switched on, the signature warning of error handling. A beginner meets one annoying error and slaps On Error Resume Next at the top of the procedure. The error vanishes. So does every other error for the rest of that procedure. The macro now sails past a missing sheet, a type mismatch, a broken reference, all without a murmur, and it writes confident garbage. The expert's reflex is visceral. On Error Resume Next is a one-or-two-line scalpel, wrapped tightly around a single operation whose failure you test for immediately, and it's always closed by On Error GoTo 0. For anything else, use On Error GoTo ErrHandler so failures are caught, logged, and cleaned up instead of ignored.
2. A UDF that tries to change other cells. Someone writes a Function that returns a value and also colours a cell or writes to the log sheet, then can't work out why nothing happens. A UDF called from the grid can't have side-effects, because Excel forbids a worksheet function from altering anything but its own return value. The expert keeps computation (a Function) and action (a Sub) strictly separate. If you need to do something, write a macro for it and leave the formula to compute.
3. Volatile UDFs that recalc forever. A UDF that reads ActiveCell, Now, Selection, or declares Application.Volatile recalculates on every change anywhere in the workbook. On a large model that's a crippling slowdown, and a mysterious one. The expert makes a UDF a pure function of its declared arguments. Same inputs give the same output, and it recalculates only when an input changes. Volatility is for the rare case that really requires it, and the expert uses it knowingly.
4. Off-by-one in For bounds. For i = 1 To n runs n times, and that includes i = n. Reading a 100-row block as For i = 2 To 100 visits row 100 too. Beginners lose the last row or overrun by one all the time. The expert derives bounds from the data instead. For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row finds the true last row. And whenever the body deletes rows, the expert loops downward (Step -1) so later rows don't shift under the counter.
5. Dictionary key case-sensitivity, which splits groups without telling you. By default "West" and "west" are two different keys, so a de-dup or tally double-counts and nobody notices. When text should match case-insensitively, the expert sets dict.CompareMode = vbTextCompare before adding any keys. The same expert Trims keys too, so a trailing space doesn't fork a group either.
6. Forgetting Set on object variables. Without Set, dict = CreateObject(...) throws an error, and myRange = Range("A1") hands you the cell's value instead of the range. Objects (Range, Worksheet, Dictionary, Workbook) are always assigned with Set. Scalars (numbers, strings, dates) never are. The expert reads the type. If it's a thing with properties and methods, it gets Set.
7. Events firing themselves recursively. A Worksheet_Change that writes a cell re-triggers Worksheet_Change, and the cascade runs forever. The expert wraps every self-write in Application.EnableEvents = False … = True. Events get re-enabled inside an error handler, so a crash halfway through can't leave them off. And the handler opens with Intersect(Target, …) Is Nothing Then Exit Sub, so the logic runs only when the relevant range changed.

All seven come from one habit. A professional's code assumes the world will misbehave, and it stays clean when it does. Beginners write for the happy path, where the file is there, the cell is a number and the event fires once, and then reality surprises them. Experts branch for the edge case, trap the error, guard the recursion, and leave EnableEvents and On Error just as they found them.

Worked examples

Worked example 1: India GST: a =GSTNet(amount, rate) UDF (India, ₹)

The task. An Indian distributor's sales register lists GST-inclusive invoice amounts in ₹, but the accounts team needs the ex-GST (net) value, the taxable base before GST, for every line. GST-inclusive amount = net × (1 + rate), so net = amount ÷ (1 + rate). You'll write a UDF GSTNet(amount, rate) and a companion GSTAmount, call them from the grid, and verify the arithmetic.

Step 1: write the UDFs. Open the VBE (Alt+F11), Insert ▸ Module, and type:

Option Explicit

Public Function GSTNet(amount As Double, rate As Double) As Double
    ' Ex-GST (net) value of a GST-inclusive amount. rate as a decimal, e.g. 0.18 for 18%.
    GSTNet = amount / (1 + rate)
End Function

Public Function GSTAmount(amount As Double, rate As Double) As Double
    ' The GST portion embedded in a GST-inclusive amount.
    GSTAmount = amount - GSTNet(amount, rate)   ' reuse the first UDF
End Function

Step 2: call them from the grid. Put the inclusive amounts in column A, starting at A2, and the GST rate in input cell $B$1, say 18%, which is 0.18:

B2:  =GSTNet(A2, $B$1)        copied down
C2:  =GSTAmount(A2, $B$1)     copied down   (or simply =A2-B2)

Pointing the rate at $B$1 instead of typing 0.18 means one edit re-prices the whole column. It's the same "never hardcode what you can reference" habit you learned with the slab-tax and commission tables on the worksheet.

Step 3, run the register and verify every figure. Two GST rates appear in Indian invoicing here: 18% (the common services/goods rate) and 5% (essentials):

Every net figure is amount / (1 + rate), and net + GST rebuilds the inclusive amount with nothing left over. That invariant is what proves the UDF correct. All four rows were recomputed in Python: 1180/1.18 = 1000.00, 2950/1.18 = 2500.00, 5900/1.18 = 5000.00, 1050/1.05 = 1000.00.

Step 4, read it. The UDF turned a repeated hand-calculation into a named idea. Any cell in any sheet can call it, it shows an argument ScreenTip, and it recalculates only when its inputs change. There's no Application.Volatile and no reading ActiveCell. It depends solely on amount and rate, just as rule two demands. That's the moment Excel stops being a fixed toolbox and becomes yours. One real-world wrinkle is outside the lesson but worth knowing. Reverse-charge and mixed-rate invoices need a rate per line instead of a single $B$1. The UDF already supports that, so point its second argument at a per-row rate column.

Worked example 2: US expense audit: For Each + Dictionary outlier flag (US, $)

The task. A US regional manager has a column of store expense figures in dollars and wants to flag the outliers, meaning any store spending above $10,000. The macro collects them into a Dictionary keyed by store, then reports how many outliers there are and their total. It's the canonical "loop the rows, remember the ones that qualify" pattern.

Step 1, the data. Store names in A2:A10, amounts ($) in B2:B10:

     A          B
1    Store      Amount
2    Austin     4200
3    Boston     12500
4    Chicago    8900
5    Denver     25000
6    El Paso    3100
7    Fresno     15750
8    Gary       9999
9    Houston    41000
10   Irvine     500

Step 2, the macro. This one's a Sub, because it's an action, and an action can't be a UDF. It walks the range with For Each, tags outliers into a Dictionary and writes the summary:

Option Explicit

Public Sub FlagOutliers()
    Const THRESHOLD As Double = 10000
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")   ' late binding: Set required

    Dim c As Range
    For Each c In Worksheets("Expenses").Range("B2:B10")
        If c.Value > THRESHOLD Then
            ' key = store name to the left; item = the amount
            dict(c.Offset(0, -1).Value) = c.Value      ' .Item auto-adds the key
            c.Interior.Color = vbYellow                ' visual flag on the sheet
        End If
    Next c

    Dim total As Double, v As Variant
    For Each v In dict.Items
        total = total + v
    Next v

    Debug.Print "Outlier count: " & dict.Count         ' -> 4
    Debug.Print "Outlier total: " & Format(total, "$#,##0.00")   ' -> $94,250.00
    MsgBox dict.Count & " outliers totalling " & _
           Format(total, "$#,##0.00"), vbInformation
End Sub

Step 3: trace and verify. Walking the rows against the $10,000 threshold:

dict.Count = 4. Total = 12,500 + 25,000 + 15,750 + 41,000 = $94,250.00. A Python check confirms four outliers and a sum of 94250. Keep an eye on Gary. At 9,999 it falls below the strict > threshold, as it should. It's there on purpose, as the boundary case that catches an accidental >=.

Step 4, read it. For Each visited every cell without a counter, so no off-by-one was possible. The Dictionary remembered the qualifying rows and nothing else, keyed by store, so .Count and a sum over .Items answered both questions in one pass. Swap the threshold constant and the whole audit re-runs. Two habits from the core show up here. The Dictionary object is created with Set, and because this routine acts on the sheet by colouring cells, it's a Sub and never a UDF. If you want to go beyond the lesson, try an extension. To catch statistical outliers instead of applying a fixed cutoff, replace the constant with mean + 2 * stdev computed in a first pass. The loop-and-remember structure stays identical.

Practice set

Work each problem before you read its solution. Every solution is a complete, runnable listing. Assume Option Explicit is at the top of the module. Where a routine computes on the sheet, its ranges are fully qualified.

P1 (guided). If…ElseIf grade UDF. Write a UDF LetterGrade(score) returning "A" for ≥ 90, "B" for 80–89, "C" for 70–79, "D" for 60–69, else "F". Call it from the grid.

Solution.

Option Explicit

Public Function LetterGrade(score As Double) As String
    If score >= 90 Then
        LetterGrade = "A"
    ElseIf score >= 80 Then
        LetterGrade = "B"
    ElseIf score >= 70 Then
        LetterGrade = "C"
    ElseIf score >= 60 Then
        LetterGrade = "D"
    Else
        LetterGrade = "F"
    End If
End Function

Call =LetterGrade(A2). The bands are written highest first because Excel stops at the first True. Reverse them and every passing score would match >= 60 and come back as "D". It's a Function that returns a value with no side-effects, so it goes in a standard Module and works from any cell.

P2 (guided). The same, as Select Case. Rewrite LetterGrade using Select Case with range bands, and say why it reads better.

Solution.

Public Function LetterGrade2(score As Double) As String
    Select Case score
        Case Is >= 90:  LetterGrade2 = "A"
        Case 80 To 89:  LetterGrade2 = "B"
        Case 70 To 79:  LetterGrade2 = "C"
        Case 60 To 69:  LetterGrade2 = "D"
        Case Else:      LetterGrade2 = "F"
    End Select
End Function

Select Case names score once and lists its bands, so a six-way split reads like a table instead of a ladder that repeats the variable. Case 80 To 89 is an inclusive range, and Case Else is the catch-all. Reach for it whenever one value is bucketed into many bands: grade, tax slab, size bucket.

P3 (guided). For…Next with Exit For on a sentinel. Sum monthly sales in B2:B1000 but stop at the first negative value (a data-entry sentinel of -1). For 1200, 3400, 2500, -1, 9999, the running total should be 7100. Write the Sub and print the result.

Solution.

Public Sub SumUntilSentinel()
    Dim i As Long, total As Double
    For i = 2 To 1000
        If Cells(i, "B").Value < 0 Then Exit For   ' sentinel: stop summing
        total = total + Cells(i, "B").Value
    Next i
    Debug.Print "Total before sentinel: " & total   ' -> 7100
End Sub

Exit For breaks out the instant the sentinel appears, so the 9999 after it never gets added. That leaves 1200 + 3400 + 2500 = 7100, and a Python check agrees. Without Exit For you'd need a flag. The exit is the clean idiom for "stop as soon as a condition is met."

P4. For Each outlier count. Count how many cells in B2:B10 exceed 10000 (the second worked example's data), using For Each. Print the count.

Solution.

Public Sub CountOutliers()
    Dim c As Range, hits As Long
    For Each c In Worksheets("Expenses").Range("B2:B10")
        If c.Value > 10000 Then hits = hits + 1
    Next c
    Debug.Print "Outliers: " & hits    ' -> 4
End Sub

For Each visits each cell with no counter and no off-by-one risk. Against the second worked example's figures, four values (12,500 / 25,000 / 15,750 / 41,000) clear the strict > cutoff, and Gary's 9,999 doesn't. The count is 4, which Python confirms.

P5. Do Until row count to first blank. Count the filled rows in a contiguous list starting at A2, stopping at the first blank, with Do Until.

Solution.

Public Function CountRows() As Long
    Dim r As Long
    r = 2
    Do Until Cells(r, "A").Value = ""
        r = r + 1
    Loop
    CountRows = r - 2      ' rows counted = (first blank row) - (start row)
End Function

Do Until … = "" tests before each pass and stops at the first empty cell. Advancing r on every pass is what prevents an infinite loop. r - 2 turns the landing row back into a count, since the list starts at row 2. The idiomatic one-liner alternative is Cells(Rows.Count, "A").End(xlUp).Row - 1, but the loop makes the logic explicit.

P6. Dynamic array with ReDim Preserve. Collect the strictly-positive values from 12, -3, 0, 45, -7, 8, 19 (in A2:A8) into a dynamic array, growing it as you go, and print how many you kept and their sum.

Solution.

Public Sub CollectPositives()
    Dim vals() As Double, n As Long, c As Range, s As Double
    n = 0
    For Each c In Range("A2:A8")
        If c.Value > 0 Then
            n = n + 1
            ReDim Preserve vals(1 To n)   ' grow the LAST (only) dimension, keep contents
            vals(n) = c.Value
            s = s + c.Value
        End If
    Next c
    Debug.Print "Kept " & n & " values, sum = " & s   ' -> Kept 4 values, sum = 84
End Sub

ReDim Preserve vals(1 To n) enlarges the array by one each time without wiping what's already stored. That's legal here, because a 1-D array's only dimension is its last. The kept values are 12, 45, 8, 19, so the count is 4 and the sum is 84 (Python-verified).

P7. Dictionary unique count. Count the distinct regions in A2:A8 = West, East, West, South, East, West, North, case-insensitively.

Solution.

Public Function DistinctRegions() As Long
    Dim dict As Object, c As Range
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare        ' "West" = "west"; set BEFORE adding keys
    For Each c In Range("A2:A8")
        If Not dict.Exists(Trim(c.Value)) Then dict.Add Trim(c.Value), 1
    Next c
    DistinctRegions = dict.Count            ' -> 4
End Function

.Exists tests membership without creating the key, so only new regions get added. CompareMode = vbTextCompare folds case so "West"/"west" don't fork, and it has to be set before the first .Add. Trim guards against a trailing-space split. The distinct set is {West, East, South, North}, which gives 4 (Python-verified).

P8. String parse into first/last name. Write a UDF LastName(fullName) that returns the surname from "Mehta, Priya" (format "Last, First"). Guard the not-found case.

Solution.

Public Function LastName(fullName As String) As String
    Dim p As Long
    p = InStr(fullName, ",")             ' 1-based position of the comma; 0 if none
    If p = 0 Then
        LastName = Trim(fullName)        ' no comma: return the whole trimmed string
    Else
        LastName = Trim(Left(fullName, p - 1))
    End If
End Function

InStr finds the comma at position 6 in "Mehta, Priya". Left(fullName, p - 1) takes everything before it, and Trim cleans off stray spaces to leave "Mehta". The If p = 0 guard is the habit to keep. Never Left/Mid on an assumed position. Test with InStr first and handle the miss.

P9. UDF pair GSTNet / GSTAmount. Reproduce the first worked example's UDFs and state how to call them and what GSTNet(1180, 0.18) returns.

Solution.

Public Function GSTNet(amount As Double, rate As Double) As Double
    GSTNet = amount / (1 + rate)
End Function

Public Function GSTAmount(amount As Double, rate As Double) As Double
    GSTAmount = amount - GSTNet(amount, rate)
End Function

Call them from a cell as =GSTNet(A2, $B$1) and =GSTAmount(A2, $B$1). GSTNet(1180, 0.18) = 1180 / 1.18 = ₹1,000.00 and GSTAmount(1180, 0.18) = 180.00. Net + GST = 1,180, the inclusive amount (Python-verified). Both are in a standard Module, take only inputs and have no side-effects, which makes them valid worksheet functions.

P10. Worksheet_Change auto-uppercase with a recursion guard. In the Register sheet, force every code typed into column A to upper case, without triggering infinite recursion, and re-enable events even if an error occurs.

Solution. (Paste this into the Register sheet's code module. Double-click the sheet in the Project Explorer to open it.)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
    On Error GoTo CleanUp
    Application.EnableEvents = False               ' stop our own write re-firing this
    Dim c As Range
    For Each c In Intersect(Target, Me.Range("A:A"))
        If Not IsEmpty(c.Value) Then c.Value = UCase(Trim(c.Value))
    Next c
CleanUp:
    Application.EnableEvents = True                ' ALWAYS re-enable, even on error
End Sub

Intersect(… ) Is Nothing Then Exit Sub runs the logic only when column A changed. Application.EnableEvents = False prevents the c.Value = … write from re-triggering Worksheet_Change forever. The On Error GoTo CleanUp guarantees events are switched back on even if the body errors. Without it, the sheet would "stop reacting" for the rest of the session, and nothing on screen would tell you why.

P11. Error handling with the Err object. Write a Sub that divides two input-cell values (B1 by B2) and writes the result to B3, but on any error writes the error description to B3 instead of crashing.

Solution.

Public Sub SafeDivideToSheet()
    On Error GoTo ErrHandler
    With Worksheets("Calc")
        .Range("B3").Value = .Range("B1").Value / .Range("B2").Value
    End With
    Exit Sub                              ' normal path exits before the handler
ErrHandler:
    Worksheets("Calc").Range("B3").Value = "Error: " & Err.Description
End Sub

Dividing by an empty or zero B2 raises run-time error 11 (division by zero). On Error GoTo ErrHandler diverts to the label, where Err.Description explains the failure in plain text. You can't leave out the Exit Sub before the label. Without it, a successful run would fall through into the error message. That's the structured pattern: try, exit, handle.

P12 (timed: 6 min). A progressive-tax UDF with arrays. Write SlabTax(income) for the FY2024-25 new regime (floors 0/300000/700000/1000000/1200000/1500000, rates 0/5/10/15/20/30%, cumulative-at-floor 0/0/20000/50000/80000/140000), returning the full progressive tax. Return the value for ₹13,50,000.

Solution.

Public Function SlabTax(income As Double) As Double
    Dim floors As Variant, rates As Variant, cum As Variant, i As Long, band As Long
    floors = Array(0, 300000, 700000, 1000000, 1200000, 1500000)
    rates  = Array(0, 0.05, 0.1, 0.15, 0.2, 0.3)
    cum    = Array(0, 0, 20000, 50000, 80000, 140000)
    band = 0
    For i = 0 To UBound(floors)
        If income >= floors(i) Then band = i   ' largest floor not exceeding income
    Next i
    SlabTax = cum(band) + rates(band) * (income - floors(band))
End Function

Call =SlabTax(E2). For ₹13,50,000 the loop settles on band = 4 (floor ₹12,00,000, rate 20%, cumulative ₹80,000), so tax = 80,000 + 0.20 × (13,50,000 − 12,00,000) = 80,000 + 30,000 = ₹1,10,000 (Python-verified). You've met this lower-bound banding before, in the worksheet slab-tax tables. Here it's rebuilt as a reusable UDF. The loop finds the band, and the formula adds floor cumulative + rate × excess above floor. Array(...) returns 0-based Variant arrays, which you read with UBound.

Applied mini-project

Build a VBA-driven GST invoice auditor.

Assemble one macro-enabled workbook (.xlsm) that prices an invoice register with your own functions, keeps its inputs clean automatically, and audits itself for outliers. It's a small tool but a complete one, and it exercises every construct you've just learned.

Set up the sheets:

1. Register: a Table (Ctrl+T) with columns InvoiceNo, StoreCode, InclAmount (GST-inclusive ₹), Qty, and empty Net, GST, LineFlag. At least 10 lines.
2. Inputs: a GSTRate cell (e.g. 18%) and an OutlierThreshold cell (e.g. ₹50,000).

Write the code (all in a standard Module unless noted):

1. UDFs: GSTNet(amount, rate) and GSTAmount(amount, rate) from Worked example 1. Fill Net with =GSTNet([@InclAmount], Inputs!$B$1) and GST with =GSTAmount(...). Add a third UDF of your own, such as SlabTax from P12 or a MarginPct, to prove you can extend the formula language at will.
2. A Select Case classifier: a UDF SizeBand(net) returning "Small" / "Mid" / "Large" by net value, driving the LineFlag column.
3. A Worksheet_Change guard on Register. It forces the StoreCode column to upper case, using the EnableEvents-off recursion guard and the On Error re-enable from P10.
4. A Workbook_Open in ThisWorkbook that stamps a "last opened" timestamp and shows a one-line MsgBox reminder of how to run the audit.
5. An audit Sub RunAudit: loop the register with For Each and collect into a Scripting.Dictionary, keyed by InvoiceNo, every line whose InclAmount exceeds Inputs!OutlierThreshold. Colour those rows, then report dict.Count and the outlier total to the Immediate window and a MsgBox. Wrap it in On Error GoTo so a bad cell never leaves a half-run.
6. Robustness pass: point the rate at a blank input cell and confirm your UDFs degrade sensibly instead of spraying #VALUE!. Type a lower-case store code and watch the event tidy it. Run RunAudit and step it once with F8, watching dict.Count climb in the Locals window.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. Its UDF module and audit Sub become the seed of the personal macro library you'll package as an add-in in VBA IV.

Lab: open the VBA II. UDFs & events workbook lab from the Practice ▸ Excel tab on this page and pass its checks.

Reading & resources

Language references (reach for these first):

• Microsoft Learn, the official VBA language reference and the Excel VBA object model pages. Search "Excel VBA reference Microsoft Learn" to find them. They hold the authoritative definitions of every statement, function, and object used here. [Free] [Beginner]
• Microsoft Learn, the Scripting.Dictionary (Windows Script Host) and WorksheetFunction pages. They cover the members, and how to call sheet functions from VBA. [Free] [Intermediate]

Deeper practice and pattern libraries:

• Chip Pearson's site (archived, cpearson.com), the classic reference on arrays, error handling, and events. It's dated in style and unmatched in depth. [Free] [Intermediate]
• Paul Kelly. Excel Macro Mastery (excelmacromastery.com) has outstanding, example-rich write-ups on the Dictionary, error handling, and events. His "VBA Error Handling" and "VBA Dictionary" guides map directly onto what you've learned here. [Free] [Beginner to Intermediate]
• ExcelJet (exceljet.net) and Wise Owl (YouTube) have short, concrete clips on UDFs, For Each, and Worksheet_Change for when you want to see it done. [Free] [Beginner]

For the sceptic's habits (the common-mistakes section, expanded):

• Any reputable "why On Error Resume Next is dangerous" article (search that exact phrase). Read it before you inherit a workbook that leans on it everywhere. [Free] [Beginner]

Bridge forward: the folder-looping and FileSystemObject work of EX6.03 reuses the For Each and Dictionary you drilled here. Skim its intro once you can write the audit Sub from memory.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

VBA I established that generated VBA runs fully trusted and unsandboxed, and it named five patterns worth a stop-and-read. The danger here is narrower and easier to miss. The statement to worry about doesn't destroy anything. It fails without a sound. Ask a generator for a Worksheet_Change handler and it will readily hand back one that writes to the sheet it's watching. Unless you tell it about Application.EnableEvents in so many words, it won't add the guard. Why would it? The code it writes runs, and on the surface it runs correctly, right up until it either loops until Excel's stack gives out or is "saved" by an unrelated crash. There's a second trap. Hand a generator a loop over messy data and it will often reach for On Error Resume Next to keep going past bad rows. That does keep the macro running. The price is that it reuses the last good value, without a word, in place of the row that failed.

AI-Augment this skill

{
  "skill": "Branching, looping, Dictionary lookups, UDFs, worksheet/workbook events with a recursion guard, and structured error handling with a narrow, closed On Error Resume Next.",
  "use": "A generator is a fast drafter of a Select Case ladder, a Dictionary-based dedupe, or a UDF skeleton with typed arguments — the mechanical grammar this module teaches. It is not a safe drafter of an event handler or an error-swallowing loop unless the prompt states the guard rules explicitly, because both failure modes still produce code that runs and returns control to the user, just with the wrong answer or the wrong sheet.",
  "tools": ["Chat assistants (Claude, ChatGPT) for drafting Select Case ladders, UDF skeletons and Dictionary-based dedupe routines", "The VBE's Immediate window and F8 step-through for watching a generated handler's EnableEvents state and Err object by hand", "A short Python re-entrancy simulation (below) for demonstrating what an unguarded handler does before ever pasting it into a workbook"],
  "prompt": "Write a Worksheet_Change handler for column A of sheet <NAME> that <TASK, e.g. forces upper case>. Requirements, all mandatory: guard with If Intersect(Target, Me.Range(\"A:A\")) Is Nothing Then Exit Sub; wrap every self-write in Application.EnableEvents = False / = True; re-enable events inside an error handler so a mid-procedure crash cannot leave events off for the rest of the session. Separately, for any loop that converts text to a number: do not wrap the whole loop in On Error Resume Next. Either validate with IsNumeric first, or scope Resume Next to one line and check Err.Number immediately after, then On Error GoTo 0.",
  "verify": "For any generated event handler: confirm the EnableEvents guard exists AND that it is re-enabled on every exit path, including the error path — an On Error GoTo that skips the re-enable line leaves the sheet permanently unresponsive to changes for the rest of the session, a bug that shows up minutes later and far from its cause. For any generated error-handling loop: find every On Error Resume Next and confirm each is closed by On Error GoTo 0 within a line or two, with a check in between — a blanket Resume Next left open for a whole procedure is, as the module states plainly, the single most dangerous line a generator will hand you.",
  "diy": "The mastery check is unaided: you must write the EnableEvents recursion guard, the Intersect scope check, and the structured On Error GoTo pattern from memory, and explain in one sentence why a bare On Error Resume Next is never acceptable for a whole procedure. Nothing generated is admissible in that room."
}

Modern Data Analysis

By hand first. Common mistake #7 above names the event-recursion trap and its cure, and P10 has you hand-write the guarded Worksheet_Change. That's the skill. You learn to spot, before you run anything, which handler writes to the range it's watching, and which loop's On Error Resume Next can leave a stale value behind. A generator won't flag either for you unless you already know to look.

Today's workflow. Here are two failure modes, demonstrated in code instead of asserted, because a claim you call "demonstrable" ought to be shown. First, simulate an unguarded Worksheet_Change in Python to see the recursion it produces, without needing Excel open. Second, simulate the stale-value bug that a blanket On Error Resume Next produces when CDbl fails partway down a column. You'll see what it does to a total, with no warning at all.

Tools & sources (IN + US). Microsoft Learn's "Out of stack space (Error 28)" reference page documents what an unguarded, infinite VBA recursion does at runtime. It doesn't stop gracefully. For the kind of register you've built here (InvoiceNo, StoreCode, InclAmount), the sourcing rule from VBA I still applies. An Indian analyst's invoice register is sourced from the company's own billing system export or GSTN e-invoice data, and it isn't typed into a macro. A US analyst's equivalent comes from the AR subledger or a QuickBooks/NetSuite export. The macro validates and totals. It doesn't invent line items.

# Recomputed in-session — R3. Two demonstrations: a blanket On Error Resume Next
# silently reusing a stale value, and an unguarded event handler's unbounded recursion.
import sys

# --- 1. Stale-value bug: CDbl under a blanket On Error Resume Next ---
amounts = [1200, 980, 1435, 1120, 1675, 890, "N/A", 1540, 2100, 990]   # row 7 is bad

def cdbl(x):
    if isinstance(x, str):
        raise ValueError("type mismatch")   # VBA equivalent: runtime error 13
    return float(x)

total_buggy, value = 0.0, 0.0
for a in amounts:
    try:
        value = cdbl(a)               # value only changes on a SUCCESSFUL conversion
    except ValueError:
        pass                          # On Error Resume Next: swallow it, value stays stale
    total_buggy += value              # row 7 silently re-adds row 6's value
total_correct = sum(cdbl(a) for a in amounts if not isinstance(a, str))   # bad row skipped
print(f"buggy total = {total_buggy}, correct total = {total_correct}, "
      f"overstatement = {total_buggy - total_correct}")
# buggy total = 12820.0, correct total = 11930.0, overstatement = 890.0

# --- 2. Unguarded Worksheet_Change: self-write re-fires the handler ---
# A Python-side simulation of the failure MODE, not a VBA runtime: VBA's own ceiling
# is available stack memory (not a fixed call count) and it fails with runtime error
# 28, "Out of stack space" (learn.microsoft.com/.../out-of-stack-space-error-28).
sys.setrecursionlimit(2000)
calls = {"n": 0}
def worksheet_change_unguarded(cell_value):
    calls["n"] += 1
    return worksheet_change_unguarded(cell_value + 1)   # the self-write re-fires the event
try:
    worksheet_change_unguarded(0)
except RecursionError:
    print(f"unguarded handler recursed {calls['n']} times before this simulation's own "
          f"ceiling stopped it")

calls_guarded = {"n": 0}
def worksheet_change_guarded(cell_value, enable_events=True):
    if not enable_events:
        return cell_value
    calls_guarded["n"] += 1
    return cell_value + 1               # EnableEvents=False around the write suppresses re-entry
print(f"guarded handler: fired {calls_guarded['n']} time(s)",
      worksheet_change_guarded(0))

Verify. Follow the stale-value trace. Row 7 fails conversion, value keeps row 6's 890, and 890 gets added a second time. So the running total after row 7 (8,190) is row 6's running total (7,300) plus the stale 890, which the true skip-forward figure would have left out. The buggy total is ₹12,820 and the correct total is ₹11,930. That's an overstatement of ₹890, the value of the one duplicated row, with nothing scattered anywhere else. Now the recursion demo. The unguarded handler calls itself until it reaches this session's raised ceiling (2,000), and Python raises RecursionError. A guarded handler fires just once. VBA has no configurable ceiling like sys.setrecursionlimit. It recurses until the process runs out of actual stack memory. Microsoft Learn documents that as its own numbered runtime error for that reason, instead of as a catchable condition you can plan around. So the guard's job is to prevent the re-entry. You can't plan on surviving it.

Quantitative lens

The stale-value bug is a full-row duplication, not a rounding error. ₹890 of the buggy ₹12,820 total, 6.9% of the reported figure, is a second copy of row 6, contributed by a single bad cell three rows later. A blanket On Error Resume Next doesn't make the macro "mostly right with some noise". It makes one specific row wrong by a specific, traceable amount. That's worse than a visible crash, because nothing in the output signals that anything happened.

Recursion has no plateau. The only bound on the unguarded handler's call count is this simulation's own artificial ceiling, set to 2,000 above. Raise the ceiling and the count rises with it. There's no natural stopping point, because each call's write is the very event that triggers the next call. The real VBA failure has the same shape. Microsoft Learn's error-28 page describes an unrecoverable stack exhaustion, which is a different animal from a bounded loop. So the fix is a guard that stops the second call from ever starting. A cap on how many calls you'll tolerate won't fix it.

Limits of the demo. The recursion-count number above, whatever this session's sys.setrecursionlimit value produces, is an artifact of Python's own interpreter. It isn't a VBA measurement. No VBA runtime was executed to produce it, and nothing here claims VBA would fail at any particular call count. What transfers is the shape of the failure: self-triggering, unbounded, catastrophic instead of gracefully degrading. That shape is a property of the logic, a handler that writes to what it watches, and the language running it doesn't change it. The stale-value figures (₹12,820 / ₹11,930 / ₹890) are exact for the ten-row example above. They don't generalise to a different data set or a different position of the bad row, and a bad row earlier in the list corrupts more subsequent rows, not fewer.

Where this goes next: galaxy cross-links

• G2 · CS1.04 Algorithmic Thinking, Recursion & Big-O, the mechanics of the recursion this addendum demonstrates: what a call stack is, why it is finite, and how to reason about a recursive process's depth before running it.
• X1 · AI0.06 Failure Modes, Verification & the Primary-Source Guardrail, a macro that runs and returns a plausible, silently wrong total is one specific case of the general guardrail the AI branch builds.
• G2 · CS1.02 Python II: Data Structures, Functions & Idiomatic OOP, the Scripting.Dictionary you've just learned is VBA's version of a hash map. The general data structure, its complexity guarantees, and its idioms are taught there.

Teach it back & journal

Feynman prompt. Explain what a UDF is to a colleague who knows worksheet formulas but has never opened the VBA editor. Use only the analogy of teaching Excel a new word. Excel ships knowing SUM and XLOOKUP. A UDF is a word you define (GSTNet) that then works in any cell just like a built-in, because you wrote down, once, what it means. Then explain the one boundary that surprises everyone. Why is this new "word" only allowed to answer the cell that asked (return a value) and forbidden from reaching out to change other cells? And why is that restriction a feature, not a limitation? Only in your last two sentences may you use the words Function, Sub, and side-effect. Attach each one to the moment in the story it describes.

Journal prompt. Find one repetitive Excel task you do by hand (a GST split, a margin calc, a monthly clean-up, a "flag the big ones" scan) and decide which construct it wants. Is it a value you compute over and over (a UDF), an action you repeat (a Sub with a loop), or a reaction to an edit (an event)? Write down the task, the construct, and the three or four lines of pseudo-logic it needs. Then write the one sentence you want to remember for good about why On Error Resume Next left switched on is the most dangerous line you can write. Add why every loop you build from now on will have its bounds derived from the data, never guessed.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.