Tips, Tricks & Best Practices

Essential Debugging Techniques

Method 1: Step Through Code (F8)

The single most useful debugging technique!

How to use:

1. Open VBA Editor (Alt+F11)
2. Click in your macro
3. Press F8 to run one line at a time
4. Watch what happens after each line
5. Check variable values in the Immediate Window (Ctrl+G)

When to use:

• Macro does something unexpected
• You want to understand how code works
• Testing new code

Debug.Print

Add this to your code to track progress:

Debug.Print "Starting loop, lastRow = " & lastRow
For i = 1 To lastRow
    Debug.Print "Processing row " & i
    ' Your code
Next i
Debug.Print "Loop complete"

View output in Immediate Window (Ctrl+G in VBA Editor)

Method 2: Breakpoints (F9)

Set a breakpoint where you want code to pause:

1. Click in the grey left margin of VBA Editor (or press F9)
2. A red dot appears - that's your breakpoint
3. Run the macro normally
4. Code pauses at the breakpoint
5. Press F8 to step through from there

Perfect for: Checking code behaviour at a specific point without stepping through everything.

Method 3: Message Boxes for Quick Checks

' Check if variable has expected value
MsgBox "lastRow = " & lastRow

' Check if condition is true
If lastRow > 100 Then
    MsgBox "Warning: More than 100 rows!"
End If

Quick and dirty, but effective!

Common Error Messages Decoded

Compile Error: Expected End Sub

Cause: Missing End Sub, End If, End With, or Next i

Fix: Make sure every opening statement has a closing: - Sub → End Sub - If...Then → End If - With → End With - For → Next

Run-time Error 9: Subscript Out of Range

Cause: Trying to access a sheet/range that doesn't exist

Fix: Check sheet names (case-sensitive!), add error handling:

On Error Resume Next
Set ws = Worksheets("SheetName")
If ws Is Nothing Then
    MsgBox "Sheet not found!"
    Exit Sub
End If
On Error GoTo 0

Run-time Error 1004: Application-defined or Object-defined Error

Cause: Trying to do something Excel won't allow

Common causes: - Invalid range (e.g., Range("A1048577") - past last row) - Protected sheet - Chart/object already exists with that name

Fix: Check your range references, use error handling

Type Mismatch Error

Cause: Trying to assign wrong data type

Example:

Dim count As Integer
count = "Hello"  ' Error! Can't put text in integer variable

Fix: Check variable types match the data

Performance Optimization

Problem: Macro Runs Slowly

If your macro takes more than a few seconds, optimize it:

1. Turn Off Screen Updating

Sub FastMacro()
    Application.ScreenUpdating = False  ' Stop screen flicker

    ' Your code here

    Application.ScreenUpdating = True   ' Turn back on
End Sub

Impact: 10-100x faster for complex operations!

2. Turn Off Automatic Calculation

Sub FastMacro()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    ' Your code here

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Use when: Writing lots of formulas

3. Avoid .Select

Slow code:

For i = 1 To 1000
    Range("A" & i).Select
    Selection.Value = i
Next i

Fast code:

For i = 1 To 1000
    Range("A" & i).Value = i
Next i

Speed difference: 5-10x faster!

4. Use Arrays for Large Data

Slow (reads from Excel 1000 times):

For i = 1 To 1000
    x = Range("A" & i).Value
    Range("B" & i).Value = x * 2
Next i

Fast (reads once, writes once):

Dim dataArray() As Variant
dataArray = Range("A1:A1000").Value  ' Read all at once

For i = 1 To 1000
    dataArray(i, 1) = dataArray(i, 1) * 2
Next i

Range("B1:B1000").Value = dataArray  ' Write all at once

Speed difference: 100x+ faster for large datasets!

Safety & Error Handling

The Ctrl+Z Problem

Critical: Macros Clear Undo History

After running a macro, Ctrl+Z often DOES NOT WORK!

VBA macros can clear Excel's undo stack. You cannot reliably undo macro changes.

Safety Protocol:

1. Always save before testing (Ctrl+S)
2. Test on dummy data first, never on your only copy
3. Keep backups of important files
4. If something goes wrong: Close without saving and reopen

Real story: A researcher lost 2 hours of work testing a "Delete Empty Rows" macro on real data without a backup. Don't be that person!

Add Proper Error Handling

Basic pattern:

Sub SafeMacro()
On Error GoTo ErrorHandler

    ' Your code here
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    ' More code...

    Exit Sub  ' Exit before error handler

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical, "Error"
    ' Optional: Log to file, send email alert, etc.
End Sub

Use for: Any macro that processes important data

Validate Inputs

Sub CalculateStress()
    Dim width As Double, thickness As Double

    width = Range("B4").Value
    thickness = Range("C4").Value

    ' Validate inputs
    If width <= 0 Or thickness <= 0 Then
        MsgBox "Error: Width and thickness must be positive!", vbCritical
        Exit Sub
    End If

    ' Safe to proceed with calculations
    ' ...
End Sub

Code Quality Best Practices

1. Comment Your Code

Good comments explain WHY, not WHAT:

' ❌ Bad comment (obvious from code)
x = width * thickness  ' Multiply width by thickness

' ✅ Good comment (explains purpose)
csa = width * thickness  ' Cross-sectional area in mm²

Comment sections:

Sub ProcessData()
    ' ===============================================
    ' 1. VALIDATE INPUTS
    ' ===============================================
    If Range("B4").Value <= 0 Then Exit Sub

    ' ===============================================
    ' 2. CALCULATE STRESS & STRAIN
    ' ===============================================
    For i = 7 To lastRow
        ' Stress formula: σ = F/A
        Range("E" & i).Formula = "=B" & i & "/" & csa
    Next i

    ' ===============================================
    ' 3. FORMAT RESULTS
    ' ===============================================
    Range("E:F").NumberFormat = "0.0000"
End Sub

2. Use Named Ranges

Instead of:

width = Range("B4").Value  ' What's in B4? Have to look at sheet

Use:

width = Range("SpecimenWidth").Value  ' Self-documenting!

How to create named ranges:

1. Select cell B4
2. Click in the Name Box (left of formula bar)
3. Type "SpecimenWidth"
4. Press Enter

Benefits:

• Code is self-explanatory
• Works if you insert rows (B4 might become B5, but "SpecimenWidth" still works)
• Less error-prone

3. Use Constants for Magic Numbers

Bad:

Range("E" & i).Value = Range("B" & i).Value * 0.78
' What is 0.78??? Conversion factor? Coefficient?

Good:

Const POISSON_RATIO As Double = 0.78
Range("E" & i).Value = Range("B" & i).Value * POISSON_RATIO

4. Break Large Macros into Smaller Functions

Instead of one 200-line macro:

Sub ProcessExperiment()
    ValidateInputs
    CalculateStress
    CreateChart
    FormatWorksheet
    ExportResults
End Sub

Sub ValidateInputs()
    ' Validation code here
End Sub

Sub CalculateStress()
    ' Calculation code here
End Sub
' etc.

Benefits:

• Easier to test individual parts
• Easier to debug
• Can reuse functions
• More readable

When NOT to Use Macros

Use Python Instead For:

Task	Why Python is Better
Complex statistics	pandas, scipy, statsmodels libraries
Machine learning	scikit-learn, TensorFlow
Batch process 100+ files	More robust file handling
Curve fitting	numpy, scipy.optimize
Large datasets (1M+ rows)	Excel slows down, Python doesn't

Use Macros For:

• Excel-specific tasks (formatting, charts, borders)
• Workflows that stay within Excel
• Quick automation (record and done!)
• Sharing with colleagues (everyone has Excel)

Hybrid Approach

Best of both worlds:

1. Use Python for heavy computations/analysis
2. Export results to Excel
3. Use macro for formatting and chart creation

Quick Checklist: Before Deploying Your Macro

Macro Quality Checklist

• Tested on dummy data
• Works with different row counts (dynamic ranges)
• Has error handling (On Error GoTo)
• Validates inputs (checks for zero, negative, missing data)
• Has comments explaining key sections
• No hard-coded values (uses constants or input boxes)
• Gives user feedback (MsgBox on completion)
• No unnecessary .Select statements
• Column references match actual data layout
• Saved as .xlsm (macro-enabled format)

Resources & Further Learning

Built-in Help

• F1 key - Context-sensitive help in VBA Editor
• Object Browser - Press F2 in VBA Editor to explore Excel objects
• Macro Recorder - Best way to learn syntax for new tasks

Cheat Sheet

Download VBA_CheatSheet.md for quick syntax reference!

Online Communities

• Stack Overflow - Search "Excel VBA [your question]"
• r/vba subreddit - Active community for questions
• MrExcel Forum - Excel-specific help

What You've Learned

✓ How to debug with F8, breakpoints, and Debug.Print
✓ How to optimize slow macros (screen updating, arrays)
✓ Why Ctrl+Z doesn't work and how to stay safe
✓ Error handling patterns
✓ Code quality best practices (comments, named ranges, constants)
✓ When to use macros vs Python
✓ Quality checklist before deployment

Next: Q&A & Troubleshooting

Final section! Common questions, troubleshooting guide, and where to go next.

Continue to Q&A →

---

← Back to Exercise 2 | Home