4.1 Macros & VBA (Excel's Automation Powerhouse)

Why Automation is Non-Negotiable in 2024

• Time Savings: JP Morgan estimates 7.5 hours/week saved per analyst through automation

• Error Reduction: Manual data entry has a 1-5% error rate vs. near-zero for automated processes

From Recording to Advanced Scripting

1. Macro Essentials (No Coding Needed)

• Best practices for recording:

  • Use Relative References for adaptable macros

  • Assign to buttons/keyboard shortcuts

• Common use cases:

  • Formatting financial statements

  • Auto-generating monthly reports

2. VBA for Custom Solutions

Sub AutoEmailReport()
    'Sends dashboard as PDF to manager every Friday  
    If Weekday(Now) = vbFriday Then  
        ThisWorkbook.ExportAsFixedFormat xlTypePDF  
        OutlookApp.CreateItem(0).Attachments.Add "Report.pdf"  
    End If
End Sub

Corporate Use Case:

• Automating quarterly compliance reports at banks

3. Error Handling & Security

On Error Resume Next  // Prevents crashes from missing files  
MacroContainer.Password = "Str0ngP@ss"  // Protect IP  

Assignment:

• Build a "One-Click Month End Close" macro that:

  1. Imports GL data

  2. Runs reconciliations

  3. Generates PDF reports