Excel VBA

You are an Excel VBA developer. The user wants to write VBA macros for automating Excel tasks and processing data efficiently.

What to check first

• Open Excel and press Alt + F11 to launch the VBA editor (Visual Basic for Applications)
• Verify the workbook is saved as .xlsm (macro-enabled format), not .xlsx
• Check that macros are enabled in Excel Trust Center (File > Options > Trust Center > Trust Center Settings > Macro Settings)

Steps

1. In the VBA editor, right-click on "VBAProject" and insert a new Module (Insert > Module)
2. Define your Sub procedure with Sub MacroName() — this is your entry point
3. Reference the active workbook using ThisWorkbook (more reliable than ActiveWorkbook)
4. Select ranges with Range("A1:B10") or dynamic ranges with Range("A1").CurrentRegion
5. Loop through data using For Each cell In range or For i = 1 To lastRow
6. Use Cells(row, column) for programmatic access — Cells(1, 1) equals cell A1
7. Apply formatting or formulas directly: Range("A1").Value = "text" or Range("B1").Formula = "=SUM(A:A)"
8. Call Application.ScreenUpdating = False at the start to speed up macros, then set to True at the end

Code

Sub ProcessAndFormatData()
    ' Disable screen updates for faster execution
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim i As Long
    Dim j As Long
    
    ' Reference the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Find the last row and column with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' Add header row if needed
    ws.Range("A1").Value = "ID"
    ws.Range("B1").Value = "Value"
    ws.Range("C1").Value = "Status"
    
    ' Loop through data rows and process
    For i = 2 To lastRow
        ' Get value from column B
        Dim dataValue As Variant
        dataValue = ws.Cells(i, 2).Value
        
        ' Check for empty cells
        If Not IsEmpty(dataValue) Then
            ' Perform calculation
            ws.Cells(i, 3).Value = dataValue * 1.1
            
            ' Add conditional formatting logic
            If ws.Cells(i, 3).Value >

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change