Macro

A Macro is an automation tool that records or contains a sequence of commands to perform repetitive tasks automatically. In Excel, macros use VBA (Visual Basic for Applications). In Google Sheets, they use Apps Script (JavaScript).

What Macros Can Do

• Automate repetitive formatting
• Process and transform data
• Generate reports
• Interact with other applications
• Create custom functions
• Build user interfaces

Recording vs Writing Macros

Recording (Easier)

1. Turn on macro recorder
2. Perform your actions
3. Stop recording
4. Play back anytime

Pros: No coding required Cons: Limited flexibility, can be inefficient

Writing (More Powerful)

Write code directly in VBA or Apps Script

Pros: Complete control, efficient, conditional logic Cons: Requires programming knowledge

Recording a Macro in Excel

1. View → Macros → Record Macro
2. Name your macro
3. Choose shortcut key (optional)
4. Perform your actions
5. View → Macros → Stop Recording

Recording a Macro in Google Sheets

1. Extensions → Macros → Record macro
2. Choose relative or absolute references
3. Perform your actions
4. Click Save
5. Name your macro

Example: VBA Macro

Sub FormatReport()
    ' Format headers
    Range("A1:E1").Font.Bold = True
    Range("A1:E1").Interior.Color = RGB(0, 112, 192)
    Range("A1:E1").Font.Color = RGB(255, 255, 255)

    ' Auto-fit columns
    Columns("A:E").AutoFit

    ' Add borders
    Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
End Sub

Example: Apps Script

function formatReport() {
  const sheet = SpreadsheetApp.getActiveSheet();

  // Format headers
  const headers = sheet.getRange("A1:E1");
  headers.setFontWeight("bold");
  headers.setBackground("#0070C0");
  headers.setFontColor("#FFFFFF");

  // Auto-resize columns
  sheet.autoResizeColumns(1, 5);
}

Running Macros

Excel

• View → Macros → View Macros → Run
• Keyboard shortcut (if assigned)
• Button (from Developer tab)
• Automatically via events

Google Sheets

• Extensions → Macros → [Your macro name]
• Keyboard shortcut
• Custom menu (via script)
• Triggers (time-based or event-based)

Security Considerations

Excel

• Macros can contain malicious code
• Files with macros use .xlsm extension
• Enable only from trusted sources
• Use macro security settings

Google Sheets

• Scripts require authorization
• Review permissions carefully
• Scripts run in Google's cloud

Best Practices

1. Comment your code: Explain what each section does
2. Use descriptive names: ProcessMonthlySales not Macro1
3. Handle errors: Add error handling for robustness
4. Test thoroughly: Check edge cases and invalid inputs
5. Back up: Save before running new macros
6. Modularize: Break complex macros into smaller procedures

When to Use Macros

Good candidates for automation:

• Repeated more than 3 times
• Error-prone manual processes
• Time-consuming formatting
• Regular report generation

Not ideal for:

• One-time tasks
• Simple operations faster done manually
• Tasks requiring human judgment