VBA III: UserForms, Files & Speed

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

In VBA I you stopped recording and started writing; in VBA II you gave that writing a mind, branches, loops, a Dictionary, your own functions, events, and error handling. Everything so far, though, has lived inside one workbook, driven by code you started by hand and fed by data already sitting on a sheet. This module breaks all three of those walls at once. It gives your macros a proper front door (a UserForm, so a colleague can drive your tool without ever seeing the code). It gives them a reach beyond the file, since the FileSystemObject and the Dir loop let one macro open, read, and consolidate a whole folder of workbooks. They get a hand into other applications too, so a routine can automate Word and Outlook to build a report and mail it. And they get speed, the discipline that makes all of that survivable on real data. That means turning off screen redraws and automatic recalculation, and above all working through in-memory arrays instead of touching cells one at a time.

The through-line is a single promotion: from a macro that you run on this workbook to a tool that other people run on many workbooks. A tool needs an interface a non-programmer can use without breaking it, that is the UserForm, and most of the work is not drawing it but validating what the user types before it ever reaches your data. A tool that consolidates a team's files needs to find and open those files reliably, skip the ones that are already open or the wrong shape, and never leave a half-opened workbook behind if something fails, that is file I/O plus the error-handling discipline from EX6.02, now doing real work. And a tool that runs over thousands of rows must not crawl. The difference between a macro that finishes in eight seconds and the same logic that finishes in eight minutes is almost always the same two habits. Stop redrawing the screen on every write, and read the range into an array, compute in memory, and write it back once.

A word on why this matters for an analyst specifically. The single most valuable macro most analysts ever write is a folder consolidator: point it at a directory of branch files, or broker statements, or monthly extracts, and get one clean master table back. You will build exactly that here. The second most valuable is a one-click report: refresh, format, save as PDF, attach to an email, done. You will meet the pieces of that here and assemble the whole in VBA IV. Neither is exotic; both are just the constructs of VBA II pointed at the file system and made fast. By the end of this module the phrase "can you just combine all of these?" (the request that used to cost you an afternoon of copy-paste) becomes a button.

One version note, stated once. Everything here assumes Microsoft 365 desktop on Windows. UserForms, the FileSystemObject (the Scripting library), Dir, and the Application speed properties are all long-stable, the code runs identically on Excel 2016 through 365. Menu paths are for the Windows ribbon and the Windows Visual Basic Editor (Alt+F11, or Developer ▸ Visual Basic). Two Windows-specific dependencies are called out where they occur: the Scripting.FileSystemObject and early-bound automation of Word/Outlook are Windows components, on Mac VBA the FileSystemObject is unavailable (fall back to Dir/Open statements) and cross-app automation differs. And as always for anything with code: save the tool as .xlsm (macro-enabled) or a plain .xlsx will silently discard every line of it on close.

Learning objectives

By the end you can:

1. Design a UserForm with the common controls (TextBox, ComboBox, ListBox, CheckBox, OptionButton, CommandButton, Label) set their key properties, and show it modally with .Show.
2. Wire a form's logic through its event handlers (UserForm_Initialize, a button's _Click, _Change), read control values in code, and unload cleanly with Unload Me / Me.Hide.
3. Validate every input before you accept it (numeric, in-range, non-empty, from an allowed list, a real date) and keep focus on the offending control with .SetFocus rather than writing bad data.
4. Use the FileSystemObject (FileExists, FolderExists, GetFolder, .Files, CreateTextFile, OpenTextFile) and the classic Dir pattern to enumerate and test files and folders.
5. Read and write text/CSV files line by line, and open, process, and safely close other workbooks with Workbooks.Open / .Close SaveChanges:=False.
6. Loop a folder of workbooks, pull a range from each, and consolidate the lot into one master sheet, skipping files that are the wrong shape and never leaving an opened book behind.
7. Automate Word and Outlook with late binding (CreateObject) to generate a document or compose an email with an attachment, and know why late binding is the safe default for portable code.
8. Make macros fast: toggle Application.ScreenUpdating, Application.Calculation, and Application.EnableEvents around bulk work (restoring them in an error handler), and (the big one) read a range into a Variant array, compute in memory, and write it back in a single assignment.
9. Structure a growing project: split code across purposeful standard modules, name procedures and modules meaningfully, scope with Public/Private, and factor shared logic into small reusable Subs and Functions.

Prerequisites & connections

Builds on. EX6.02 (VBA II. Logic, Functions & Events) is the hard prerequisite, and you'll use it on almost every line here. For Each walks a folder's files. If/Select Case validates inputs and branches on file type. The Scripting.Dictionary accumulates per-key totals while you consolidate, and string handling (InStr, Split, Left/Mid) parses file names and paths. Most of all you need the On Error GoTo / Err pattern. File and cross-application work fails in ways in-sheet code never does: a file is locked, a path is wrong, Outlook isn't installed. A tool that opens files must guarantee it closes them and restores the speed settings even when it crashes. From EX6.01 you keep Option Explicit, fully-qualified objects, With blocks, and the no-.Select reflex. From the wider Excel track you bring the analyst's habit, drilled since EX0.02, of never trusting an input you haven't checked. Form validation is that habit, written as code.

Feeds forward. EX6.04 (VBA IV. Automation Capstones) assembles everything here into four finished tools. Your folder consolidator from the first worked example below becomes Capstone 2 essentially unchanged. The report generator uses the same Word/Outlook automation plus the speed trio to produce a formatted PDF pack. A third tool, the model-checker add-in, scans a workbook using the file and range techniques you'll drill here. Every capstone assumes you work through arrays and switch ScreenUpdating off. Those habits are the difference between a demo and a tool people will actually run. On the analyst track, the same machinery drives the portfolio tracker of EX7.02, which turns many broker files into one dashboard. It also powers any "refresh and print the pack" routine, and the reusable UDF/macro library that a professional modeller (EX7.01) carries from job to job. The first-udf badge came from EX6.02. Now you can build the tools that make VBA worth carrying.

Core teaching content

Controlling other apps: Word & Outlook

VBA can drive any application that exposes an automation model, and for an analyst the two most useful are Word and Outlook. You can bind to them in two ways:

• Early binding means setting a reference (Tools ▸ References ▸ Microsoft Outlook xx.x Object Library), then Dim olApp As Outlook.Application. You get IntelliSense and named constants. But the code breaks if the target machine has a different Office version or the library is missing.
• Late binding is Dim olApp As Object: Set olApp = CreateObject("Outlook.Application"). There's no reference and no IntelliSense, but the code is portable, because it binds at run time to whatever version is installed. For code you share, late binding is the safe default. Look up the numeric value of any named constant you need and hardcode it with a comment, as in Word's wdFormatPDF = 17.

Here's a minimal "compose an email with an attachment". It doesn't send until the user clicks, which is the courteous default:

Dim olApp As Object, mail As Object
Set olApp = CreateObject("Outlook.Application")
Set mail = olApp.CreateItem(0)               ' 0 = olMailItem
With mail
    .To = "team@example.com"
    .Subject = "Consolidated report " & Format(Date, "yyyy-mm-dd")
    .Body = "Attached: this week's consolidation."
    .Attachments.Add ThisWorkbook.Path & "\Master.pdf"
    .Display                                 ' .Send would fire it immediately
End With

Excel's own ExportAsFixedFormat Type:=xlTypePDF, Filename:=... turns a sheet or range into that PDF first, so a simple pack needs no Word at all. Reach for Word automation when you need real document layout (headings, a narrative, a table of contents). Reach for Outlook when the deliverable is an email. The capstones that come next chain these into the one-click report generator.

Module structure and organisation

As a project grows past one macro, where code lives starts to matter. It has three homes. Standard modules (Module1…) hold your general Subs and Functions, and UDFs must live there. Sheet modules hold that sheet's events, such as Worksheet_Change. ThisWorkbook holds workbook events like Workbook_Open. None of this is glamorous, but it matters:

• Split by purpose, not by size. A modConsolidate for the folder loop, a modReport for the PDF/email chain, a modUtils for the shared helpers (a LastRow(ws) function, a SheetExists(name) boolean). One 3,000-line module is a code smell.
• Public vs Private. Mark a Sub Private if only its own module calls it, and Public (the default) if it's an entry point or a shared helper. Fewer public names means fewer accidental collisions and a cleaner Alt+F8 macro list, since only Public Subs with no arguments appear there.
• Factor out the repeated line. The instant you write ws.Cells(ws.Rows.Count, "A").End(xlUp).Row for the third time, make it Function LastRow(ws As Worksheet) As Long. Small, named, reused helpers let the folder consolidator and the report generator share the same "find the data" logic without copy-paste.
• Name for the reader. Call it ConsolidateBranches, not Macro3. Use txtAmount, not TextBox1, and modReport, not Module2. You pay the cost once, and the benefit comes back every time anyone opens the project.

Is good structure gold-plating? It can look like it. But it's what makes the four capstones that follow buildable, because each of them reuses these modules instead of starting from nothing.

Common mistakes & how experts think differently

A TextBox value is a String, not a number. The most common form bug is total = Me.txtAmount.Value + 100 producing string concatenation or a type error, because .Value is text. Experts test with IsNumeric and convert with CDbl before arithmetic, and they do it every time.

Validating after you have written, or not at all. The novice form writes the row, then maybe checks it. The expert's form writes nothing until every gate passes, returns focus to the bad control, and treats "the user typed garbage" as the normal case. A form that can write bad data is just a faster way to corrupt a sheet.

Leaving files open after a crash. Picture a consolidation loop that opens a workbook and then hits an error on a bad sheet. Without a guard, that workbook stays open and invisible in memory, and it often stays locked for everyone else too. Experts wrap the open in error handling, and the handler's first job is If Not wb Is Nothing Then wb.Close SaveChanges:=False.

Forgetting to restore the speed settings on error. ScreenUpdating = False with no matching restore in the error path leaves the user with a frozen screen and no idea why. An expert uses Resume CleanUp to make the same cleanup code run on success and on failure, so the trio always goes back once and only once.

Cell-by-cell when an array would do. The novice loops For i = 2 To 10001: ws.Cells(i,1) = ws.Cells(i,1) * 1.05: Next and wonders why it takes a minute. The expert reads the range into a Variant array, computes in memory, and writes it back once. That's two boundary crossings instead of twenty thousand. It's the biggest performance lever in all of VBA.

Early binding in code you share. Dim ol As Outlook.Application compiles on your machine and fails to compile on a colleague's with a different Office version or a missing reference. Experts use late binding (CreateObject, As Object) for anything that leaves their own PC, and hardcode the numeric constants with a comment.

Every one of those habits comes from a single mental shift, from macro to tool, from "code I run" to "code other people run on data I have not seen." A tool assumes the input is wrong until validated. It assumes a file in the folder is malformed until it opens cleanly. It assumes it may crash, and guarantees it leaves the workbook in a sane state anyway. And it assumes it'll meet more rows than you tested with, so it works through arrays instead of cells. Look at every "extra" line: the validation ladder, the If Not wb Is Nothing, the Resume CleanUp, the array read. None of it is defensiveness for its own sake. It's the price of a macro that survives contact with a colleague and a real folder.

Worked examples

Worked example 1: India: consolidate a folder of branch workbooks (India, ₹)

Scenario. Four regional branches each send a workbook: North.xlsx, South.xlsx, East.xlsx, West.xlsx, sitting in a branches subfolder next to your tool. Each has one sheet with a header row and then Product / Units / Revenue rows. You must consolidate all of them into a single Master sheet, tagging each row with its branch, and report the row count and grand total.

The data (₹):

Step 1, expected results, computed by hand first (so the macro has something to be checked against). Same rule as always: know the answer before you trust the code. Before you run anything, do the sums.

• Consolidated rows = 3 + 3 + 2 + 3 = 11 rows.
• Grand total = 31,15,000 + 24,50,000 + 17,25,000 + 28,25,000 = ₹1,01,15,000 (₹10,115,000).

Step 2, the macro is ConsolidateBranches, unchanged from the core section above. It confirms the folder, clears Master, sets the speed trio, and Dir-loops the *.xlsx files. It opens each one read-only, walks its used rows appending Branch/Product/Units/Revenue to Master, then closes it SaveChanges:=False. Finally it restores the settings in CleanUp (and in CleanFail). The branch tag comes from the file name: Left(fname, InStrRev(fname, ".") - 1) turns North.xlsx into North.

Step 3, the check row on Master. After the loop, put the totals where a human, and the lab, can read them: =COUNTA(B2:B100000) for the row count and =SUM(D2:D100000) for the grand total.

• =COUNTA(B2:B100000) returns 11.
• =SUM(D2:D100000) returns 10,115,000, which is ₹1,01,15,000.

Step 4: why the numbers are trustworthy. The by-hand total from Step 1 and the sheet total from Step 3 agree at ₹1,01,15,000, and the row count agrees at 11. Any disagreement would have flagged a bug: a skipped file, a double-counted header row, an off-by-one in the used-range detection. Here's the classic failure this catches. Suppose the used-range end-row calculation grabbed the header. Each file would contribute one phantom row, and the count would read 15 instead of 11. Against the hand figure you'd see that instantly. So always compute the expected total independently before you believe a consolidation macro.

Worked example 2: US: a UserForm that validates an expense, then writes a formatted row (US, $)

Scenario. An expense log on sheet Log has headers Vendor / Category / Amount / Date in A1:D1. You want a form so anyone can add an entry safely: the amount must be a positive number, the date must be real, the category must come from a fixed list, and the vendor must be non-empty. Bad input must never reach the sheet.

Step 1, the form. frmExpense holds three TextBoxes, txtVendor, txtAmount and txtDate, and the date box defaults to today. cboCategory is a ComboBox filled in Initialize with Travel/Meals/Software/Office. btnOK is the Default button and btnCancel the Cancel one. UserForm_Initialize fills the combo and stamps Format(Date,"yyyy-mm-dd") into txtDate.

Step 2, the validation ladder is btnOK_Click as written in the core section. It checks for a vendor first, then IsNumeric(txtAmount), then CDbl(txtAmount) > 0, then cboCategory.ListIndex <> -1, then IsDate(txtDate). Each failure gets MsgBox + SetFocus + Exit Sub. Only when all five pass does it call WriteExpenseRow.

Step 3, the writer appends one formatted row beneath the log's last used row:

Sub WriteExpenseRow(vendor As String, cat As String, amt As Double, dt As Date)
    Dim ws As Worksheet, r As Long
    Set ws = ThisWorkbook.Worksheets("Log")
    r = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    Application.EnableEvents = False                 ' don't trip a Change handler
    With ws
        .Cells(r, 1).Value = vendor
        .Cells(r, 2).Value = cat
        .Cells(r, 3).Value = amt
        .Cells(r, 3).NumberFormat = "$#,##0.00"
        .Cells(r, 4).Value = dt
        .Cells(r, 4).NumberFormat = "yyyy-mm-dd"
    End With
    Application.EnableEvents = True
End Sub

Step 4, trace a good and a bad entry.

• Good: vendor "CloudCorp", category Software, amount "2306.40", date "2026-07-18". IsNumeric("2306.40") = True, CDbl = 2306.40 > 0, a category is selected, and IsDate = True. So the row is written, with Amount shown as $2,306.40 and Date as 2026-07-18.
• Bad: amount "12O0", with a letter O where the zero should be. It's an easy typo to make, and a hard one to spot by eye. IsNumeric("12O0") = False, so the message "Amount must be a number" appears, focus returns to txtAmount, and nothing is written. That's the corruption the form exists to prevent. A stray letter would otherwise land as text in a numeric column and break every SUM beneath it, with no error to warn you.

Step 5, the running total, as a check. Say the log starts with $1,840.00, $512.75, $2,306.40, $89.99, $1,275.00. Then =SUM(C2:C6) = $6,024.14. You can compute that figure independently (1840 + 512.75 + 2306.40 + 89.99 + 1275 = 6024.14) and use it to confirm the form wrote the amounts as numbers. Text entries would be skipped by SUM, and the total would read low.

Practice set

Work these with Excel and the VBE open. Numeric answers are exact unless a tolerance is stated. Full solutions follow each.

Practice 1 (guided), the validation ladder. A UserForm has txtQty (a TextBox). Write the btnOK_Click gate. It should reject a blank, a non-numeric, or a non-positive quantity, keep focus on txtQty each time, and proceed only if the quantity is a positive whole number.

Solution. 0 The whole-number test compares the value to its Int. Order matters here. Test blank and numeric before CDbl, or CDbl("") throws.

Practice 2 (guided): array uplift. Column A2:A10001 holds 10,000 base prices. Write the fastest VBA that raises every price by 5% and reports how many worksheet touches it makes. Base value $2,200 becomes what, and how many touches versus a cell-by-cell loop?

Solution. Read to a Variant, loop in memory, write back once: 0 $2,200 × 1.05 = $2,310.00. Worksheet touches: 2 (one bulk read, one bulk write) versus 20,000 for a cell-by-cell read-and-write loop. The array version is dramatically faster because it crosses the boundary between VBA and the grid twice, where the loop crosses it twenty thousand times.

Practice 3 (independent): folder count. A branches folder holds North.xlsx, South.xlsx, East.xlsx, West.xlsx, and a stray notes.txt. Write the Dir loop that counts only the .xlsx files. How many?

Solution. 0 The *.xlsx pattern excludes notes.txt, so n = 4. A *.* pattern would wrongly count 5, then choke when Workbooks.Open met the text file. The pattern itself is the filter.

Practice 4 (independent): consolidation total. Using the Worked Example 1 branch data (North ₹31,15,000; South ₹24,50,000; East ₹17,25,000; West ₹28,25,000), what grand total should your =SUM check show, and how many consolidated data rows?

Solution. Grand total = 31,15,000 + 24,50,000 + 17,25,000 + 28,25,000 = ₹1,01,15,000 (10,115,000). Rows = 3 + 3 + 2 + 3 = 11. Computing this by hand first is the check that tells you whether the macro double-counted a header or skipped a file.

Practice 5 (independent): regional Dictionary consolidation (US, $). Four US regions report totals: East $12,300, Central $9,950, Mountain $8,500, Pacific $15,000. A For Each loop accumulates each region's amount into a Scripting.Dictionary keyed by region. What is the combined total, and how many keys does the Dictionary end with?

Solution. Combined = 12,300 + 9,950 + 8,500 + 15,000 = $45,750. Distinct keys = 4, one per region. Now suppose the source data had a "east" and an "East", and you forgot dict.CompareMode = vbTextCompare. You'd get 5 keys and a split total. It's the case-sensitivity gotcha from VBA II, biting a consolidation this time.

Practice 6 (stretch, timed 15 min), the safe folder loop. Write a complete Sub that consolidates ThisWorkbook.Path & "\branches" *.xlsx files into a Master sheet. It must (a) set and restore the speed trio, (b) guarantee no source workbook is left open if one file errors, and (c) apply an 8% uplift to a base of ₹95,000 as a sanity cell. What does the uplift cell read?

Solution. The structure is ConsolidateBranches from the core section. The speed trio is set before the loop and restored in a shared CleanUp, which both the happy path and Resume CleanUp from CleanFail reach. The handler holds If Not wbSrc Is Nothing Then wbSrc.Close SaveChanges:=False, so a mid-loop error never orphans a file. The uplift sanity cell reads ₹95,000 × 1.08 = ₹1,02,600 (102,600). Three things are graded here, and none of them is optional: settings restored on both paths, no orphaned workbook, and the uplift computed correctly. Get all three and you have a folder loop you can trust on real data.

P7 (independent). The shortfall mailer (India, ₹). After the Worked Example 1 consolidation runs, a second Sub drafts one Outlook message per branch that came in under a monthly target of ₹25,00,000, quoting that branch's shortfall. Using the same four branch revenues (North ₹31,15,000; South ₹24,50,000; East ₹17,25,000; West ₹28,25,000): how many drafts does the loop create, what shortfall does each carry, and why should the Outlook.Application reference be late-bound?

Solution. Two branches miss the target, so the loop creates 2 drafts. South is short 25,00,000 − 24,50,000 = ₹50,000. East is short 25,00,000 − 17,25,000 = ₹7,75,000. Together that's ₹8,25,000. North and West clear the target and are skipped. 0 Late binding with As Object plus CreateObject lets the file open on a machine with a different Outlook version, or none at all. An early-bound As Outlook.MailItem throws the compile-time "Can't find project or library" error the moment the reference breaks. The cost is that you lose IntelliSense and must write the enumerated constants as their numbers, so olMailItem becomes 0. Until a human has read the draft once, use .Display and leave .Send alone.

P8 (independent). The append-mode audit log (US, $). Every run of the consolidator appends one line per branch to audit.csv through FileSystemObject, and writes a header line only when it creates the file. You run the macro three times against four branch files. How many lines does audit.csv hold afterwards, and what single argument decides whether that number is right?

Solution. One header plus 3 runs × 4 branches = 13 lines. The deciding argument is the iomode passed to OpenTextFile: 0 Mode 8 appends. Mode 2 (ForWriting) truncates the file to nothing on open, so the same three runs would leave 5 lines, meaning the header plus the last run only. The two earlier runs would be gone, and nothing would raise an error anywhere. Test FileExists before the open. Opening in append mode with create:=True makes the file exist, and then the header would never be written.

P9 (stretch, timed 20 min). Cost out the speed trio (US, $). You time three versions of the same 20,000-row uplift on one machine. The plain cell-by-cell loop with every default left on takes 4.80 seconds. The same loop with ScreenUpdating off takes 1.60 seconds, and the read-loop-write array version takes 0.12 seconds. Give the speed-up at each step and end to end, give the worksheet-touch counts for the first and last versions, and say which of the three numbers you would quote in a code review.

Solution. Screen updating alone: 4.80 / 1.60 = 3.00×. The array rewrite on top of that: 1.60 / 0.12 = 13.33×. End to end: 4.80 / 0.12 = 40.0×. As for worksheet touches, the cell-by-cell version reads and writes each of 20,000 cells, for 40,000 crossings of the VBA-to-grid boundary. The array version reads once and writes once, for 2, a ratio of 20,000 : 1. Divide the naive time by its touch count and you get about 120 microseconds per crossing. The whole section is about that one constant. Quote the 40×, and quote it as a ratio. Seconds don't travel. Absolute times move with the machine, the Excel build, the calculation mode and what else is running, so 4.80 seconds won't reproduce on a reviewer's laptop. Touch counts are structural and reproduce anywhere. The trap here is reading 13.33× as "the array trick is four times more valuable than the speed trio". They aren't substitutes. The trio is three lines and works on code you aren't allowed to restructure, while the array rewrite is a redesign. Do the trio first, measure again, then decide whether the redesign is worth it.

Applied mini-project

Now build the branch consolidator, end to end. Create a macro-enabled workbook called Consolidator.xlsm, with a Master sheet whose headers are Branch / Product / Units / Revenue in A1:D1. Add a branches subfolder holding the four files from Worked Example 1. Write ConsolidateBranches in a modConsolidate standard module. Add a CommandButton (Developer ▸ Insert ▸ Button, or a shape assigned the macro) on Master labelled "Consolidate". Then:

1. Press the button; confirm the message reports 4 files, 11 rows.
2. On Master, add =COUNTA(B2:B100000) and =SUM(D2:D100000); confirm 11 and ₹1,01,15,000.
3. Break one branch file by renaming its sheet or deleting column C, and run again. Confirm the handler reports which file failed, closes it, and leaves the screen and calculation restored, with nothing frozen.
4. Add a second button that opens a frmExpense UserForm writing to a Log sheet, as in Worked Example 2. Enter one good and one bad amount, and confirm the bad one is rejected with focus returned and nothing written.

Ship it with the speed trio on and the error handler proven. Then keep the file. What you've built is Capstone 2 of VBA IV in embryo.

Rubric. Score each row yourself. A row that's missing or wrong gets 0, a partial one gets 1, and a correct, defended one gets 2. You pass at 8 of 12 with no zero on any row.

A row scores zero when its own test fails, whatever the code looks like. Run the failure test in row 3 hardest. Break a branch file deliberately, run the macro, and check that Excel comes back with the screen drawing and calculation on. If the consolidator leaves the application frozen after an error, it has failed, even if all eleven rows landed.

Lab: open the VBA III, files & speed workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

• Microsoft Learn, "Get started with VBA" and the Excel VBA object model reference (free). The authoritative reference for Workbooks.Open, Application.ScreenUpdating, and the Range.Value array behaviour. Search the method name, and the pages give you runnable snippets.
• Microsoft Learn, Scripting Runtime (FileSystemObject) reference (free). Every FSO member (FileExists, GetFolder, OpenTextFile, mode constants), with examples.
• Chip Pearson's site, "Working With UserForms" and "Automating Word/Outlook from Excel" (free). The classic, still-accurate deep dives on form events and late-bound cross-app automation.
• Excel Macro Mastery (Paul Kelly). "The Complete Guide to Excel UserForms" and "VBA Performance" (free articles, paid course). Practitioner-grade patterns for validation ladders and the array-over-cells idiom, with timing benchmarks.
• "Professional Excel Development" (Bovey et al.) (paid). It goes deeper into module structure, add-in packaging, and the engineering practice that EX6.04's capstones assume.

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)

Ask a generator in plain language for "a macro that raises every price in a column by 5%," and its first draft is very often the naive translation: For i = 2 To lastRow: ws.Cells(i, 1) = ws.Cells(i, 1) * 1.05: Next. It produces the right numbers, so it isn't wrong. It's also the very pattern a whole section above taught you to replace. A generator doesn't know your row count is 10,000 unless you tell it, and it has no reason to reach for the array idiom unless the prompt asks for it by name.

AI-Augment this skill

{
  "skill": "UserForm input validation, FileSystemObject/Dir-based file and folder handling, safe cross-application automation with late binding, and the speed trio plus the array-over-cells idiom.",
  "use": "A generator drafts a UserForm's control layout and its _Click handlers quickly, and is a fast first-drafter of a Dir-loop folder consolidator with the open/process/close-safely shape this module teaches. Its default habit on any 'loop through the data and change it' request is the cell-by-cell pattern, because that is the literal translation of the English sentence — the array rewrite is a deliberate professional habit, not the obvious one, so it has to be requested explicitly, every time.",
  "tools": ["Chat assistants (Claude, ChatGPT) for drafting UserForm event handlers and Dir-loop folder consolidators to a stated shape", "Rubberduck (rubberduck-vba on GitHub) — a free, open-source COM add-in for the VBA IDE with static-analysis inspections and unit testing, useful for reviewing generated code before it runs", "Microsoft Learn's Range.Value reference for confirming the exact shape (1-based, 2-D) a bulk array read/write returns"],
  "prompt": "Write a VBA Sub that <TASK over a range of up to N rows>. Do not touch cells one at a time. Read the full range into a single Variant array with one .Value read, perform every computation on the array in memory, and write the result back with exactly one .Value assignment. State the exact number of worksheet read/write operations your code performs, as a number, before I run it.",
  "verify": "Count the boundary crossings yourself rather than trusting the generator's stated count: search the generated code for `.Cells(` or `.Range(` inside a `For`/`For Each` loop body — any hit there is a per-iteration touch, not a one-time bulk operation, and the count claim is wrong if you find one. Separately, for any UserForm handler: confirm every TextBox is validated (numeric, in range, non-empty) before its value reaches a cell, and that a rejected entry keeps focus on the offending control rather than silently accepting bad data.",
  "diy": "The mastery check is unaided: you must write the array-read, in-memory-compute, array-write pattern from memory, state its exact touch count against a cell-by-cell alternative, and hand-validate a UserForm's inputs with a calculator for any numeric checks. Nothing generated is admissible in that room."
}

Modern Data Analysis

By hand first. The worked example already proved the case on 10,000 rows. The array version made 2 worksheet touches against 20,000 for cell-by-cell, and both turned $2,200 into the identical $2,310.00. Knowing "arrays are faster" isn't enough, though. You want to be able to derive the touch count for any row count on paper. Hold that arithmetic in your head and you'll spot the naive loop on sight, whether it's in someone else's code or in a generator's first draft.

Today's workflow. Ask "how many worksheet touches does this perform?" of every generated loop. It's the same reflex VBA I built for danger patterns and VBA II built for recursion. The check is mechanical. Any .Cells( or .Range( reference inside a loop body is a per-iteration touch, and a correct array version has two in total, whatever the row count.

Tools & sources (IN + US). Microsoft Learn's Excel VBA Range.Value reference documents the array shape a bulk read returns, which is a 1-based, 2-D Variant even for a single column. That detail is what trips up a first attempt at the idiom. The consolidation task you've been building toward, many source files into one master table, is the same job pandas does on the Python side. Think of an Indian analyst consolidating monthly branch MIS extracts, or a US analyst consolidating broker CSV statements. Today they're doing the FileSystemObject/Dir loop taught here, and tomorrow they'll likely do it with pandas.concat over a folder of files. Both are the same idea of reading many and stacking once, built on two different stacks.

# Recomputed in-session — R3. Verify the module's own worked figures, then generalise
# the touch-count relationship across row counts, including Excel's actual row ceiling.
n, base, uplift = 10000, 2200, 0.05
print(f"uplifted value: {base * (1 + uplift):.2f}")            # 2310.00 — matches the module
print(f"cell-by-cell touches: {2 * n}")                          # 20000 — matches the module
print(f"array touches: 2, ratio: {2 * n / 2:.0f}")               # 10000  — matches the module

for rows in (100, 1_000, 10_000, 100_000, 1_048_576):             # 1,048,576 = Excel's row ceiling
    print(f"n={rows:>9,}  cell-by-cell={2*rows:>9,}  array=2  ratio={rows:>9,}")

Verify. The engine's $2,310.00, 20,000-touch, 10,000-ratio outputs match the worked example's own figures, which gives you a clean anchor. Now extend it across row counts. You might expect a fixed "arrays are N times faster" constant. Instead the ratio equals the row count itself, because cell-by-cell touches scale as 2n while the array idiom stays a constant 2 regardless of n. At Excel's actual maximum of 1,048,576 rows, that ratio is 1,048,576, which is over a million.

Quantitative lens

Now spell out the mechanism. A cell-by-cell loop over n rows performs one read and one write per row, for 2n total worksheet touches. The array idiom performs one bulk read and one bulk write regardless of n, for 2 total touches. The ratio of the two, 2n / 2 = n, comes from algebra. No benchmark went into it. It's what the two algorithms' touch counts reduce to, so it holds at every row count in the table above without any approximation. Each individual touch crosses the boundary between VBA and Excel's own object model, the same boundary VBA I walked down as Application ▸ Workbook ▸ Worksheet ▸ Range. A bulk .Value read or write crosses it once, where the loop crosses it n times.

So the lever grows as your data grows. A 100-row report and a 1,048,576-row full-column loop have the same qualitative bug, touching cells one at a time. Yet the second one's ratio is over ten thousand times worse than the first's, purely from the row count. On a 100-row demo sheet the habit looks like a minor style preference. On a sheet sized for real data, it's the difference between a macro that returns control in under a second and one that doesn't.

Mind the limits of that claim. The touch-count ratio is exact arithmetic, and it says nothing about timing. No seconds are asserted for either version, because no VBA runtime was benchmarked to produce these figures. Wall-clock time depends on hardware, on whether calculation and screen updates are also toggled off (the speed trio from earlier), and on what the per-cell computation itself does. What's exact is the count of boundary crossings. The arithmetic above proves that count, a specific, countable property of the two algorithms. Speed is a separate question.

Where this goes next: galaxy cross-links

• G2 · DA1.01 NumPy & Vectorized Computation. It's the array-over-cells idiom generalised. NumPy's whole design replaces a Python-level per-element loop with one bulk operation, for the same boundary-crossing reason.
• G2 · DA1.03 pandas II: Cleaning, Time Series & the Excel↔Python Bridge. The folder consolidator you build here, which turns many workbooks into one master table, is the VBA-side version of pandas.concat over a folder of files, the Python-side tool for the same job.
• G2 · CS2.04 Systems & Data-Intensive Thinking. Boundary crossings as the real cost, batching as the fix, and reasoning about how a cost scales with data size, so you never measure it once and assume it stays fixed.

Teach it back & journal

Explain three things to an imagined colleague, without notes. (1) Why building a UserForm is mostly validation. (2) The five-step safe folder-loop, and the one line that keeps it from orphaning a file. (3) Why reading a range into an array and writing it back once is the biggest speed lever in VBA, with the "2 touches vs 20,000" count. Then journal. Which of the three did you have to look up? That's the one to drill again before the lab. Finally, pick one macro from your own work that the folder consolidator or the validation form would replace. A task you do by hand counts too. Schedule an hour to build it. The reflex only sticks when you ship a tool you actually use.

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.