The ExcelMaster.ai Blog

Application.WorksheetFunction lets VBA borrow Excel's 450-plus built-in functions instead of rewriting SUM, VLOOKUP or COUNTIF as a loop. The catch is that there are two ways to call them and they fail differently. WorksheetFunction.X raises a run-time error when there is no match, while Application.X returns an error value you test with IsError. Learn which call style to use, why the result must be a Variant, which functions you should not call this way, and when to pass a whole range instead of looping cell by cell.

Range.RemoveDuplicates is Excel's Remove Duplicates button in a single line of code, but it is destructive in ways a loop is not. It deletes rows in place, keeps the first occurrence, and cannot be undone after a macro runs. The argument that trips everyone is Columns, whose numbers are offsets inside the range, not sheet column letters. Learn the range-offset trap, why Header xlYes matters, how to keep the last row instead of the first, and when to reach for Advanced Filter or a Dictionary instead.

Range.AdvancedFilter is the one filter that outputs data instead of a view. It can pull a unique list or a criteria-matched set of rows to another location in a single call, without a loop and without deleting anything. The part that feels alien is that its WHERE clause lives in cells, a criteria range whose header must match the data exactly. Learn xlFilterInPlace versus xlFilterCopy, how the criteria range works, how Unique True dedupes non-destructively, the header-match trap that returns empty output, and how it differs from AutoFilter and Remove Duplicates.

The VBA Range.Find method is Excel's Ctrl+F for code, and it trips people up because it returns a Range object (or Nothing when there is no match) instead of a number. Learn the one check that stops the error-91 crash, the sticky-arguments trap that makes Find behave differently every run, how LookAt xlWhole versus xlPart decides whole-cell versus contains, and how to loop FindNext to get every match without an infinite loop.

The VBA AutoFilter method filters a table in code, but the rows it hides are still there — they still count in SUM, still copy, and still sit in your range. Learn the mental model that prevents the classic bugs, why you must go through SpecialCells xlCellTypeVisible to touch only visible rows, how criteria and operators work, the toggle trap that turns your filter off on the second run, and the fast filter-then-delete pattern for bulk row removal.

Sorting in VBA is a permanent reorder with no Ctrl+Z, so the first rule is to protect the original order before you touch it. Learn the mental model that separates a view from a mutation, why Header xlYes matters or your titles end up in the data, the difference between the quick Range.Sort and the unlimited Sort object, the SortFields.Clear trap that inherits old sort keys, and why numbers stored as text sort in the wrong order.

Deleting a row in VBA is a structural edit, not a clear — every row below slides up to fill the gap, which is why a forward loop skips rows. Learn the one rule that fixes it (loop bottom to top, or delete in a single Union), how EntireRow.Delete differs from clearing a cell, the fast way to strip blank rows, and how to delete rows by condition without corrupting your data or reference formulas.

Inserting is deleting's mirror — it pushes existing rows down (or columns right) to make room, so the same shifting that breaks a delete loop breaks an insert loop too. Learn EntireRow.Insert versus a partial range with the Shift argument, why CopyOrigin decides which neighbour's formatting the new row inherits, the safe direction to insert inside a loop, and the one-call way to add many rows at once.

Hiding a column in VBA is not deleting it and not filtering it — the data is still there, still in every SUM, still copied by a range copy, just given zero display width. Learn why .Hidden lives on EntireColumn and EntireRow, why hidden cells still count in formulas, the unhide-everything line that rescues a stuck sheet, and how manually hidden rows differ from AutoFilter-hidden rows when you loop.

Worksheet_BeforeDoubleClick is the event Excel fires the instant you double-click a cell, before it enters edit mode, and it hands you the Target cell plus a Cancel flag. Set Cancel to True to suppress edit mode and run your own action instead — toggle a checkmark, mark a row done, or drill down to detail. Learn how to scope it to one column with Intersect, why forgetting Cancel drops the cell into edit mode, and where the code must live.

Worksheet_BeforeRightClick is the event Excel fires the instant you right-click a cell, before the context menu appears, and it hands you the Target cell plus a Cancel flag. Set Cancel to True to suppress the built-in menu and run your own action or show a custom menu instead. Learn how to scope it with Intersect so you don't cripple Copy and Paste, why disabling right-click is UX rather than protection, and where the code must live.

Workbook_BeforePrint is the event Excel fires before anything in the workbook is printed, and it hands you a Cancel flag. Set Cancel to True and the print is blocked — validate before printing, or stop a draft going out. Learn why the event fires for Print Preview too (so heavy work makes preview crawl), why it runs once for the whole workbook rather than per sheet, why a silent Cancel is a bug, and where the code must live.

Workbook_BeforeClose is the event Excel fires the instant someone tries to close the file — before anything is torn down — and it hands you a Cancel flag. Set Cancel to True and the close is called off. Learn how to run cleanup on close, how to stop Excel asking to save twice with the Saved property, why it never fires on a crash, and where the code must live.

Workbook_BeforeSave is the event Excel fires the moment a save is requested — before anything is written to disk — and it hands you two flags, SaveAsUI and Cancel. Learn how to block a save when a required cell is empty, how to auto-stamp who saved and when, why calling Save inside the handler loops forever, and how SaveAsUI lets you enforce a filename.

Worksheet_Activate fires when a sheet becomes the active one and Worksheet_Deactivate fires just as you leave it — the arrival and departure events for a sheet. The catch that defines them — unlike BeforeClose and BeforeSave, neither gives you a Cancel, so you can watch a sheet switch but you can't block it. Learn the refresh-on-view pattern, the bounce-back workaround, and sheet-level versus workbook-level handlers.

A VBA class module lets you define your own object type — a blueprint that bundles data and behaviour — then stamp out independent instances with New. The catch that trips everyone is that objects are reference types, so Set b = a makes both names point at the same instance, and Dim x As New hides a lazy-instantiation trap. Learn how the module name becomes the type name, when a class beats a Type, and how to avoid the classic New pitfalls.

A VBA Type — a user-defined type declared with Type ... End Type — bundles several related fields into one variable, so Name, Age and Salary travel together instead of as three parallel arrays. It is a value type, which means assigning one Type variable to another copies every field, unlike objects, which share. Learn where the declaration must live, why copy-not-share matters, and the exact point where you should reach for a class module instead.

Property Get, Let and Set turn a class field into a gate — a small procedure that runs your code whenever the outside reads or writes it, so you can validate input, compute values on the fly, or make a field read-only. The distinction that trips everyone is Let versus Set — Let assigns a value, Set assigns an object — and using the wrong one is a compile or run-time error. Learn the backing-field pattern, when a plain Public variable is the honest choice, and how to build a read-only property.

ActiveCell is a live pointer to the one cell that has the cursor right now — always exactly one cell, on the active sheet, sitting inside the current Selection. Learn how it differs from Selection, how to read and write it with .Value and .Offset, and the number-one reason it breaks — it follows whatever sheet and cursor the user left behind, so it is the wrong tool for code that is not about where the user is.

Selection is a live pointer to whatever is highlighted right now — usually a range of cells, but it can also be a chart, a shape, or nothing at all. That is why code that assumes Selection is a range crashes the moment a chart is selected. Learn how to loop the selected cells, handle multi-area selections with .Areas, guard with TypeName, and when to skip Selection entirely and name your range.

The macro recorder writes what your mouse does — Select a sheet, Select a cell, act on the Selection — because that is how a person works, not how code should. Learn the real difference between Select (highlight one or many cells) and Activate (set the one active cell), why almost every .Select is a slow, fragile detour you can delete, the rare times you genuinely need to select, and how to refactor recorder code to act on qualified range references directly.

Workbook_Open is the event Excel fires the instant your file finishes opening — code that runs itself, no button needed. But it only fires if it lives in the ThisWorkbook object (not a standard Module) and the user has macros enabled. Learn where the code must go, why it silently never runs, Workbook_Open vs Auto_Open, and how to keep it fast and crash-proof.

Worksheet_Change is the event Excel fires every time a user edits a cell on the sheet, handing you the changed cell as Target. The trap that catches everyone — your handler writes a cell, that write fires the event again, and Excel loops forever. Learn the Application.EnableEvents fix, how to scope it with Intersect, why it ignores formula recalcs, and where the code must live.

Worksheet_SelectionChange is the event Excel fires every time the cursor moves — a click, an arrow key, an Enter. It hands you the new selection as Target, which makes cursor-following tricks like highlighting the active row possible. But it fires constantly, so heavy code makes the whole sheet lag. Learn the highlight-active-row pattern done right, why it can loop, and how to keep it featherlight.

For Each says 'do this to every item' and lets VBA handle the order and the bounds — no counter, no off-by-one. But that convenience hides three hard rules — the loop variable must be an object or a Variant, iterating an array is read-only, and you must never delete from a collection you are walking. Learn when For Each beats For…Next and when it quietly betrays you.

A VBA Collection is an ordered list that grows as you Add to it — no ReDim, no size guessing. But it has four sharp edges beginners hit every time — it is 1-based not 0-based, you cannot overwrite an item (only Add/Remove), duplicate keys throw error 457, and it has no built-in Exists check. Learn when a Collection beats an Array or a Dictionary, and when it quietly costs you.

The With statement names an object once and lets you hit its members with a bare leading dot — less typing, faster code, cleaner blocks. But that leading dot is the whole game — forget it and .Font becomes Font, which silently binds to the active sheet instead of your object. Learn what With actually optimizes, the missing-dot trap, and how With pairs with For Each.

Dim reserves a named box before you use it — but the real reason to declare variables is that VBA silently invents a new empty Variant the moment you misspell a name. Learn why Option Explicit is the single most valuable line in VBA, the Dim a, b As Long trap, and when you need Set instead.

A VBA data type is the shape and size of a variable's box — and choosing it is really choosing what can go wrong. Learn why Integer overflows at 32,767 (use Long), why Variant is a lazy default that hides bugs, why money needs Currency not Double, and how to read Type mismatch and Overflow errors.

A VBA constant is a name locked to a value at compile time — the fix for magic numbers scattered through your code. Learn why Const stops values drifting, why assigning to one is a compile error (that's the feature), why Const must be a compile-time literal, and when to reach for Enum instead.

The most-searched VBA question — how to find the last row of data — has four answers, and each one breaks on a different sheet. Learn why Cells(Rows.Count, 1).End(xlUp).Row is the right default, when to switch to .Find, and why .UsedRange and .End(xlDown) quietly lie.

Range.Offset returns a new range shifted by (rows, columns) — it moves, it doesn't resize, and Offset(1,0) means one row down, not row 1. Learn the mental model, the header-skip idiom Offset(1).Resize(n-1), and how the VBA method differs from the OFFSET worksheet function.

UsedRange and CurrentRegion both grab "the whole block," but they answer different questions. UsedRange is Excel's cached bounding box — it over-reports and won't shrink. CurrentRegion is computed live around a cell and stops at blank rows. Learn which to trust and when.

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.