DAY 26: Excel - Macros & Automation Basics

Exercise 1: Record Your First Macro - Automated Data Formatting

Goal: Learn to record macros that automate repetitive formatting tasks.

Step-by-Step Instructions:

1. Enable Developer tab (if not visible): - File → Options → Customize Ribbon - Check "Developer" on the right → OK
2. Prepare sample data:

   Create unformatted table:
   A1: Name, B1: Sales, C1: Region, D1: Date
   A2-D10: Sample data (numbers without formatting, plain text)

3. Start recording macro: - Developer tab → Record Macro - Name: FormatSalesReport (no spaces!) - Shortcut key: Ctrl+Shift+F (optional) - Store in: This Workbook - Description: "Formats sales data with headers, borders, and number formatting" - Click OK
4. Perform formatting actions (macro is recording!): - Select header row (A1:D1) - Format: Bold, background color (blue), white text - Select data range (A2:D10) - Apply borders: All Borders - Format column B as Currency: Ctrl+Shift+$ - Format column D as Date: Ctrl+Shift+# - Auto-fit all columns: Select all → Home → Format → AutoFit Column Width
5. Stop recording: - Developer tab → Stop Recording (or click Stop icon in status bar)
6. Test your macro: - Create new unformatted data on Sheet2 - Press Ctrl+Shift+F (your shortcut) - Or: Developer → Macros → FormatSalesReport → Run - Watch Excel automatically apply all formatting!

💡 Tips:

• Macro names can't have spaces: Use camelCase or underscores
• Recording is literal: If you click cell B5, macro will always click B5 (not relative)
• Use shortcuts while recording: They're faster and more reliable
• Stop and restart: If you make a mistake, stop recording and start over
• Macro security: Enable macros when opening workbooks (Developer → Macro Security → Enable)

✅ Expected Result: One-click formatting that transforms raw data into professional tables instantly

🔄 Connection to Previous Learning: Automates manual formatting techniques from Day 1

Exercise 2: Create Buttons to Run Macros

Goal: Add user-friendly buttons that execute macros with a single click.

Step-by-Step Instructions:

1. Using the FormatSalesReport macro from Exercise 1
2. Insert a button (Form Control): - Developer tab → Insert → Button (Form Control) - first icon - Draw the button on your worksheet (click and drag) - "Assign Macro" dialog appears automatically
3. Assign your macro: - Select "FormatSalesReport" from the list - Click OK
4. Edit button text: - Right-click button → Edit Text - Change to: "Format Report" - Click outside button to finish
5. Format the button: - Right-click → Format Control - Font tab: Change to Arial 12pt Bold - Colors and Lines: Background color (matching your theme) - Size: Make it appropriately sized (not too small)
6. Create more macro buttons: - Create a "Clear Data" macro: - Record macro: Select data range → Press Delete - Create a "Refresh Report" button - Arrange buttons in a control panel area
7. Alternative: ActiveX Command Button (more advanced): - Developer → Insert → Command Button (ActiveX Control) - Double-click button to open VBA editor - Add code between `Private Sub` and `End Sub`:

   Private Sub CommandButton1_Click()
       Call FormatSalesReport
       MsgBox "Report formatted successfully!", vbInformation
   End Sub

💡 Tips:

• Form Controls vs ActiveX: Form Controls are simpler, ActiveX allows more customization
• Design mode: Turn on Design Mode (Developer tab) to move/edit ActiveX buttons
• Group buttons logically: Format buttons, Data buttons, Report buttons
• Add icons: Insert shapes/icons near buttons for visual appeal
• Button best practices: Clear labels, consistent sizing, logical placement

✅ Expected Result: User-friendly interface where anyone can run complex macros by clicking buttons

🔄 Connection to Previous Learning: Creates user interfaces similar to Excel forms (Day 23)

Exercise 3: View and Edit Recorded Macro Code

Goal: Learn to open the VBA editor, understand recorded code, and make simple modifications.

Step-by-Step Instructions:

1. Open the VBA Editor: - Press Alt+F11 (or Developer → Visual Basic)
2. Find your macro: - In Project Explorer (left pane): VBAProject → Modules → Module1 - Double-click Module1 to see your code
3. Understand the generated code:

   Sub FormatSalesReport()
   '
   ' FormatSalesReport Macro
   ' Formats sales data with headers, borders, and number formatting
   '
       Range("A1:D1").Select
       With Selection.Interior
           .Color = 15773696
           .Pattern = xlSolid
       End With
       Selection.Font.Bold = True
       ' ... more code ...
   End Sub

4. Common code elements: - `Sub ... End Sub`: Defines macro start and end - `'` = Comments (ignored by Excel) - `Range("A1")` = Refers to cell A1 - `.Select` = Selects range - `With ... End With` = Apply multiple properties to same object
5. Edit the macro to make it more flexible: - Change from Select-then-format to direct formatting:

   Sub FormatSalesReport()
       ' Format header row
       With Range("A1:D1")
           .Font.Bold = True
           .Interior.Color = RGB(68, 114, 196) 'Blue
           .Font.Color = RGB(255, 255, 255) 'White
       End With
   
       ' Format sales column as currency
       Range("B2:B10").NumberFormat = "$#,##0.00"
   
       ' Add borders
       Range("A1:D10").Borders.LineStyle = xlContinuous
   
       ' Auto-fit columns
       Columns("A:D").AutoFit
   
       MsgBox "Report formatted!", vbInformation
   End Sub

6. Test your edited macro: Close VBA editor (Alt+Q), run macro again

💡 Tips:

• Recording creates verbose code: Manual coding is often more efficient
• Remove .Select statements: Direct references are faster and cleaner
• RGB for colors: RGB(255,0,0) = Red; easier than Excel's color numbers
• Use variables for ranges: Makes code more flexible and readable
• Comment your code: Add ' comments to explain what sections do

✅ Expected Result: Ability to read, understand, and modify VBA code for better macro performance

🔄 Connection to Previous Learning: Programming concepts apply logic to Excel automation

Exercise 4: Create Dynamic Macros with Variables

Goal: Write macros that adapt to different data sizes using variables and loops.

Step-by-Step Instructions:

1. Open VBA Editor (Alt+F11)
2. Create a new macro that finds the last row dynamically: - Insert → Module (to create new module) - Write this code:

   Sub FormatDynamicReport()
       Dim lastRow As Long
       Dim ws As Worksheet
   
       ' Set worksheet reference
       Set ws = ActiveSheet
   
       ' Find last row with data in column A
       lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
       ' Format header row
       With ws.Range("A1:D1")
           .Font.Bold = True
           .Interior.Color = RGB(68, 114, 196)
           .Font.Color = RGB(255, 255, 255)
       End With
   
       ' Format data range (dynamic!)
       ws.Range("A2:D" & lastRow).Borders.LineStyle = xlContinuous
       ws.Range("B2:B" & lastRow).NumberFormat = "$#,##0.00"
   
       ' Auto-fit all columns
       ws.Columns("A:D").AutoFit
   
       MsgBox "Formatted " & lastRow - 1 & " rows of data!", vbInformation
   End Sub

3. Understanding the key concepts: - `Dim lastRow As Long`: Declares a variable to store row number - `Set ws = ActiveSheet`: Reference to current worksheet - `Cells(Rows.Count, "A").End(xlUp).Row`: Finds last used row (Ctrl+Up from bottom) - `Range("B2:B" & lastRow)`: Concatenates string to create dynamic range
4. Test with different data sizes: - Try with 5 rows of data → Run macro - Add more rows (50 rows) → Run macro again - Macro adapts automatically!
5. Add a loop to process each row:

   Sub ProcessEachRow()
       Dim lastRow As Long, i As Long
       Dim ws As Worksheet
       Set ws = ActiveSheet
   
       lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
       ' Loop through each row
       For i = 2 To lastRow
           ' Highlight high sales (>5000) in green
           If ws.Cells(i, 2).Value > 5000 Then
               ws.Cells(i, 2).Interior.Color = RGB(146, 208, 80) 'Green
           End If
       Next i
   
       MsgBox "Processed " & lastRow - 1 & " rows!", vbInformation
   End Sub

💡 Tips:

• Always use variables for dynamic ranges: Never hardcode row numbers in production code
• Dim declares variables: Specify type (Long, String, Double, Boolean)
• For...Next loops: Execute code repeatedly for each row/column
• If...Then...End If: Conditional logic in VBA
• Use Cells(row, column): More flexible than Range for loops

✅ Expected Result: Intelligent macros that handle any data size and make decisions based on values

🔄 Connection to Previous Learning: Similar logic to SQL CASE statements (Day 18) and Excel IF functions (Day 3)

Exercise 5: Add Error Handling and User Input

Goal: Make macros robust with error handling and interactive with user prompts.

Step-by-Step Instructions:

1. Create a macro with error handling:

   Sub SafeFormatReport()
       On Error GoTo ErrorHandler
   
       Dim lastRow As Long
       Dim ws As Worksheet
   
       ' Check if activesheet is a worksheet (not chart)
       If TypeName(ActiveSheet) <> "Worksheet" Then
           MsgBox "Please select a worksheet first!", vbExclamation
           Exit Sub
       End If
   
       Set ws = ActiveSheet
       lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
       ' Validate that there's data
       If lastRow < 2 Then
           MsgBox "No data found to format!", vbExclamation
           Exit Sub
       End If
   
       ' Format report
       With ws.Range("A1:D1")
           .Font.Bold = True
           .Interior.Color = RGB(68, 114, 196)
           .Font.Color = RGB(255, 255, 255)
       End With
   
       ws.Range("A2:D" & lastRow).Borders.LineStyle = xlContinuous
       ws.Columns("A:D").AutoFit
   
       MsgBox "Report formatted successfully!", vbInformation
       Exit Sub
   
   ErrorHandler:
       MsgBox "Error: " & Err.Description, vbCritical
   End Sub

2. Add user input with InputBox:

   Sub ColorByThreshold()
       Dim threshold As Double
       Dim lastRow As Long, i As Long
       Dim ws As Worksheet
   
       ' Ask user for threshold value
       threshold = InputBox("Enter sales threshold:", "Set Threshold", 5000)
   
       ' Validate input
       If threshold = 0 Then Exit Sub ' User clicked Cancel
   
       Set ws = ActiveSheet
       lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
       ' Highlight rows based on user input
       For i = 2 To lastRow
           If ws.Cells(i, 2).Value > threshold Then
               ws.Cells(i, 2).Interior.Color = RGB(146, 208, 80) 'Green
           Else
               ws.Cells(i, 2).Interior.Color = RGB(255, 192, 0) 'Orange
           End If
       Next i
   
       MsgBox "Highlighted " & lastRow - 1 & " rows based on threshold $" & Format(threshold, "#,##0"), vbInformation
   End Sub

3. Create confirmation dialogs:

   Sub ClearDataWithConfirmation()
       Dim response As VbMsgBoxResult
   
       ' Ask user to confirm
       response = MsgBox("Are you sure you want to clear all data?" & vbCrLf & _
                         "This action cannot be undone!", _
                         vbYesNo + vbQuestion, "Confirm Clear")
   
       If response = vbYes Then
           Range("A2:D1000").ClearContents
           MsgBox "Data cleared successfully!", vbInformation
       Else
           MsgBox "Action cancelled.", vbInformation
       End If
   End Sub

4. Add status updates for long processes:

   Sub ProcessLargeData()
       Dim i As Long
       Application.ScreenUpdating = False ' Speed up macro
       Application.StatusBar = "Processing data..."
   
       For i = 2 To 10000
           ' Your processing code here
           If i Mod 100 = 0 Then ' Update every 100 rows
               Application.StatusBar = "Processing row " & i & " of 10000..."
           End If
       Next i
   
       Application.StatusBar = False ' Reset status bar
       Application.ScreenUpdating = True
       MsgBox "Processing complete!", vbInformation
   End Sub

💡 Tips:

• On Error GoTo: Catches errors and prevents crashes
• Always validate user input: Check for empty, zero, or invalid values
• Application.ScreenUpdating = False: Speeds up macros by not updating display
• Exit Sub: Exits macro early (useful after validations)
• vbCrLf: Line break in message boxes
• Test error scenarios: Try running macro with no data, wrong sheet, etc.

✅ Expected Result: Professional, robust macros that handle errors gracefully and interact with users

🔄 Connection to Previous Learning: Similar to SQL error handling and validation (Days 20, 24)