The ExcelMaster.ai Blog

VBA has two ways to join text — & and + — and only one is safe. Learn why & is the only string glue you should trust, the Null trap behind +, and when to switch to Join.

UCase and LCase aren't for storing data — they're for comparing and displaying it. Learn why VBA's = is case-sensitive, how to compare without changing your data, and why StrConv ruins names like McDonald.

VBA's Str function hides two traps — a leading space in front of positive numbers and a locale-blind decimal point. Learn when Str helps, why CStr is the safer default, and how to convert numbers to text the right way.

ThisWorkbook is the file your code lives in; ActiveWorkbook is whatever is in front right now — and confusing them is why VBA macros read, write, and save the wrong workbook.

A worksheet has three handles — tab name, CodeName, and index — and two of them break when users rename or reorder sheets. Here's which reference to use and why Sheets isn't Worksheets.

ActiveSheet, ActiveCell and Selection are global mutable state that changes under your macro. Here's why an unqualified Range silently writes to the wrong sheet — and the one habit that fixes it.

VBA has two ways to call VLOOKUP, and one crashes on a missing value while the other returns a testable error. The mental model, the loop that turns VLOOKUP into an O(n²) trap, and the Dictionary fix. Tested on Excel 365, 2021, 2019.

The macro recorder teaches Select, Copy, Paste — three steps through the clipboard that are slow, fragile, and easily interrupted. The mental model, why .Value = .Value beats it, and when PasteSpecial is still right. Tested on Excel 365, 2021, 2019.

Sending Outlook email from Excel VBA is late binding plus a security wall — and a bridge Microsoft is dismantling. The mental model, CreateObject vs a reference, .Display vs .Send, and what New Outlook kills. Tested on Excel 365, 2021, 2019.

VBA Trim only strips the ends, not the spaces inside — and it ignores the Chr(160) non-breaking space that web and PDF paste leaves behind. The mental model, the one normalize function to use, and when each tool wins. Tested on Excel 365, 2021, 2019.

VBA Format turns a value into a display string — it never changes what the value IS. The mental model, the format codes that matter, the m-means-month trap, and why writing Format() into a cell quietly breaks SUM and sort. Tested on Excel 365, 2021, 2019.

CStr, CDate and Val change what a value IS, not just how it looks. The mental model, the locale trap that makes CDate read 01/02 as Jan 2 on one PC and Feb 1 on another, why Val quietly truncates European decimals, and the DateSerial fix. Tested on Excel 365, 2021, 2019.

VBA Split explained as the un-concatenate: one delimited string becomes a zero-based array. The off-by-one that drops your first field, why it is not a CSV parser, and when to reach for it. Tested on Excel 365, 2021, 2019.

VBA Mid, Left and Right explained as cutting a string by coordinates. The 1-indexed off-by-one, why Mid is also a statement, and when position-based extraction is the wrong tool. Tested on Excel 365, 2021, 2019.

VBA Replace explained as code-level Find & Replace: every match, in one pass. Why it's case-sensitive by default, the Start argument that silently truncates your string, and when Range.Replace beats it. Tested on Excel 365, 2021, 2019.

VBA has no try/catch — robust error handling is one structure: arm a handler, run, route every exit through a single cleanup point, then Resume. The Resume rule, re-raising, and the cleanup point that saves your file. Tested on Excel 365, 2021, 2019.

VBA On Error explained as a switch that decides where a macro goes after it hits a runtime error. Resume Next vs GoTo a handler vs GoTo 0, the one rule that stops bugs hiding. Tested on Excel 365, 2021, 2019.

The VBA Err object is the black box of a runtime error: Err.Number, Err.Description and Err.Source tell you what broke, and Err.Raise lets you throw your own. When it auto-clears and why it matters. Tested on Excel 365, 2021, 2019.

VBA Sub explained the way it works: a Sub is a named action, your macro IS a Sub, and the parentheses rule for calling one is behind half the compile errors. Copy-paste examples, tested on Excel 365, 2021, 2019.

VBA Function explained: return a value by assigning to the function's own name (not Return), turn it into a custom worksheet function you can type in a cell, and know when to use a Function vs a Sub. Tested on Excel 365, 2021, 2019.

VBA ByRef vs ByVal explained: ByRef passes the original variable (changes stick), ByVal passes a copy. Why VBA defaults to ByRef and silently mutates your variable — and the one habit that prevents it. Tested on Excel 365, 2021, 2019.

VBA MsgBox explained the way it actually works: when to use brackets, how to read a Yes/No/Cancel answer, and why MsgBox is a decision gate — not a debugger. Copy-paste examples, tested on Excel 365, 2021, 2019.

Excel has two VBA InputBoxes, not one. Learn when the plain InputBox is enough, when Application.InputBox with a Type wins, and the Cancel trap that silently breaks both. Copy-paste examples, tested on Excel 365, 2021, 2019.

A VBA UserForm done right: the event-driven mindset, why your code 'disappears' after .Show, and the Unload-vs-Hide rule that decides whether you can read what the user typed. Copy-paste event code, tested on Excel 365, 2021, 2019.

Master the VBA Range object in Excel: Range vs Cells, .Value vs .Value2, dynamic ranges with End and CurrentRegion, and why you should never use .Select. 8 copy-paste examples. Tested on Excel 365, 2021, 2019.

Master VBA arrays in Excel: declare static and dynamic arrays, ReDim Preserve, UBound/LBound, and the one trick that makes macros 100× faster — reading a range into an array. 7 copy-paste examples. Tested on Excel 365, 2021, 2019.

Master the VBA Dictionary in Excel: early vs late binding, Add and Exists, plus the two patterns analysts really use it for — building unique lists and counting/grouping. 6 copy-paste examples. Tested on Excel 365, 2021, 2019.

VBA If Then Else is a checklist of independent questions. Learn the one trap that breaks it (VBA never short-circuits), the single-line vs block rule, and when to switch to Select Case — with copy-paste examples. Tested on Excel 365, 2021, 2019.

A VBA While loop is a promise that it will end — and you own that promise. Learn the three parts every While loop needs, why Do While beats While...Wend, and how to never hang Excel again. With copy-paste examples. Tested on Excel 365, 2021, 2019.

VBA Select Case is a switchboard, not a checklist. Understand the one rule that explains every Select Case bug, when to use it instead of ElseIf, and the Select Case True escape hatch — with copy-paste examples. Tested on Excel 365, 2021, 2019.