Stop Writing VBA Macros Blindly — Try This Instead

The first thing most people do when they decide to create scripts in Excel is open the VBA editor (Alt+F11), type Sub MyFirstScript(), and start pasting code from Stack Overflow. That’s like wiring a light switch after watching one YouTube video — it *might* work, but you’ll probably trip the breaker or fry something important.

The Setup

You’re managing quarterly sales data for a small SaaS reseller. Eight reps report deals closed each month, but entries come in messy: inconsistent date formats, duplicate entries flagged as 'REPEAT', missing territories, and revenue entered as text (e.g., "42,500" instead of 42500). You need to clean, standardize, and flag anomalies — not just once, but every time new data drops into Sheet1!A1:E12.

Rep Name	Deal Date	Company	Revenue	Territory
Sarah Chen	03/15/2024	Acme Corp	42,500	West
Javier Mora	2024-03-17	Nexus Labs	$68,900	South
Priya Desai	Mar 12 2024	Veridian Systems	39,200	East
Sarah Chen	03/15/2024	Acme Corp	42,500	REPEAT
Marcus Lee	2024/03/20	Tecton Dynamics	$55,100	North
Aisha Khan	03-18-2024	Lumina Health	28,750	West
Javier Mora	2024-03-17	Nexus Labs	68900	South
Priya Desai	Mar 12 2024	Veridian Systems	$39,200	East
Marcus Lee	2024/03/20	Tecton Dynamics	55100	North
Aisha Khan	03-18-2024	Lumina Health	28,750	West

The Challenge

You need to create scripts in Excel that handle three things reliably: convert mixed date formats into true Excel dates (so you can sort and filter), strip dollar signs and commas from Revenue and convert to numbers, and replace 'REPEAT' in Territory with blank — but only if the full row matches an earlier entry exactly. The tricky part? You can’t assume users will paste cleanly. And if your script runs twice on the same data, it might double-convert numbers or wipe valid territory names. That’s why how to write scripts for Excel isn’t about typing faster — it’s about designing guards. Every script needs validation at the top: Is this range actually selected? Are cells non-empty? Has this already been processed? We’ll bake those in.

Walking Through It

Let’s build a script step-by-step — not all at once, but as discrete, testable actions. Open the VBA editor with Alt+F11. In the Project Explorer, right-click ThisWorkbook → Insert → Module. Paste this first version:

Sub CleanSalesData()
    Dim rng As Range
    Set rng = Range("A1:E12")
    
    ' Step 1: Convert dates
    With rng.Columns(2)
        .NumberFormat = "yyyy-mm-dd"
        .Value = .Value ' forces re-evaluation
    End With
End Sub

Run it (F5). Nothing changes visibly — but now check cell B2. Its underlying value is now a serial number (45370), not text. That’s critical. Excel won’t sort '03/15/2024' and '2024-03-17' correctly unless both are true dates. Here’s the before/after for column B:

Before (B1:B12)	After (B1:B12)
03/15/2024	2024-03-15
2024-03-17	2024-03-17
Mar 12 2024	2024-03-12
03/15/2024	2024-03-15
2024/03/20	2024-03-20

Now add Step 2 — cleaning Revenue (column D):

    ' Step 2: Clean revenue
    With rng.Columns(4)
        .Value = Evaluate("IF(ROW(" & .Address & "),SUBSTITUTE(SUBSTITUTE(" & .Address & ",""$"",""),"",""),"")")
        .Value = .Value ' force numeric conversion
    End With

Yes — we used Evaluate. It’s faster than looping through 12 rows, and safer than Replace() when you don’t know where commas sit. (Trust me, I learned this the hard way trying to clean $1,234,567.00.) Before/after for column D:

Before (D1:D12)	After (D1:D12)
42,500	42500
$68,900	68900
39,200	39200
42,500	42500
$55,100	55100

Finally, Step 3 — deduplicate by row and clear 'REPEAT':

    ' Step 3: Flag and clear repeats
    Dim i As Long
    For i = rng.Rows.Count To 2 Step -1
        If Application.WorksheetFunction.CountIfs( _
            rng.Columns(1), rng.Cells(i, 1).Value, _
            rng.Columns(2), rng.Cells(i, 2).Value, _
            rng.Columns(3), rng.Cells(i, 3).Value, _
            rng.Columns(4), rng.Cells(i, 4).Value) > 1 Then
            If rng.Cells(i, 5).Value = "REPEAT" Then rng.Cells(i, 5).ClearContents
        End If
    Next i

Notice we loop backwards (To 2 Step -1). That avoids skipping rows when deleting — a classic gotcha.

The Result

After running the full script, here’s what lives in A1:E12 — clean, sortable, formula-ready, and safe to pivot or chart:

Rep Name	Deal Date	Company	Revenue	Territory
Sarah Chen	2024-03-15	Acme Corp	42500	West
Javier Mora	2024-03-17	Nexus Labs	68900	South
Priya Desai	2024-03-12	Veridian Systems	39200	East
Sarah Chen	2024-03-15	Acme Corp	42500
Marcus Lee	2024-03-20	Tecton Dynamics	55100	North
Aisha Khan	2024-03-18	Lumina Health	28750	West
Javier Mora	2024-03-17	Nexus Labs	68900
Priya Desai	2024-03-12	Veridian Systems	39200
Marcus Lee	2024-03-20	Tecton Dynamics	55100	North
Aisha Khan	2024-03-18	Lumina Health	28750	West

What Could Go Wrong

Even with careful design, these three mistakes happen constantly — and they’re rarely caught until the report goes out.

Symptom	Cause	Fix
Dates become ###### after running	Column width too narrow *after* formatting change	Add rng.Columns(2).AutoFit after date conversion
Revenue shows #VALUE! in some rows	Cells contain non-numeric text like "N/A" or "Pending"	Wrap Evaluate with IFERROR: Evaluate("IFERROR(...,0)")
Script deletes *all* Territory values, not just REPEAT	Missing quotes around "REPEAT" in the If statement	Use If rng.Cells(i, 5).Value = "REPEAT" Then — not = REPEAT

One more thing: Never save a workbook with scripts as .xlsx. Always use .xlsm. Excel disables macros silently in .xlsx — and there’s no warning until your button stops working. Here’s your quick-reference cheat sheet for how to create scripts in Excel safely:

• Alt+F11 — Open VBA editor
• Ctrl+R — Toggle Project Explorer (find your module fast)
• F5 — Run current macro
• Alt+Q — Close VBA editor and return to Excel
• Always wrap core logic in On Error Resume Next + error logging — even for simple scripts
• Test with MsgBox "Step 1 complete" after each major block
• Name modules meaningfully: modSalesClean, not Module1