VBA III — UserForms, Files & Speed

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 is used on almost every line here: For Each to walk a folder's files, If/Select Case to validate inputs and branch on file type, the Scripting.Dictionary to accumulate per-key totals while consolidating, string handling (InStr, Split, Left/Mid) to parse file names and paths, and, most of all, the On Error GoTo / Err pattern, because file and cross-application work fails in ways in-sheet code never does (a file is locked, a path is wrong, Outlook is not installed) and 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 have not checked, which is the whole philosophy of form validation.

Feeds forward. EX6.04 (VBA IV. Automation Capstones) assembles everything here into four finished tools: the folder consolidator you build in this module's worked example becomes Capstone 2 essentially unchanged; the report generator uses this module's Word/Outlook automation plus the speed trio to produce a formatted PDF pack; and the model-checker add-in scans a workbook using the file and range techniques drilled here. The speed disciplines, arrays over cells, ScreenUpdating off, are assumed by every capstone and are the difference between a demo and a tool people will actually run. On the analyst track, this module is the machinery behind the portfolio tracker of EX7.02 (many broker files → one dashboard), behind any "refresh and print the pack" routine, and behind the reusable UDF/macro library that a professional modeller (EX7.01) carries from job to job. The first-udf badge came from EX6.02; the tools you can now build are what make VBA worth carrying.

UserForms: input done right

First principles. A UserForm is a custom dialog box (your own window, with your own controls) that a macro can display to collect input or show output. It is what turns a macro you run into a tool anyone can run: instead of asking a colleague to edit cell B2 and press Alt+F8 and pick the right macro name, you hand them a button that opens a clean form with labelled boxes. Insert one with Insert ▸ UserForm in the VBE; the Toolbox floats beside it with the controls; the Properties window (F4) edits the selected control's design-time properties; and double-clicking a control opens its code module, where its events live.