Lesson 7.5 – Automating Tasks with Macros & VBA

Lesson Overview

Macros and VBA (Visual Basic for Applications) in Excel allow users to automate repetitive tasks, streamline workflows, and enhance productivity. This lesson teaches how to record macros, write simple VBA code, and implement automation in Excel workbooks.

1. Introduction to Macros & VBA

• Macro: A recorded sequence of actions that can be replayed

• VBA: Programming language used to create more advanced automation

• Benefits:

  • Save time on repetitive tasks

  • Reduce manual errors

  • Enable complex calculations and automation

2. Recording a Macro

2.1 Steps to Record a Macro

1. Go to View → Macros → Record Macro

2. Provide a Macro name, shortcut key, and store location (This Workbook)

3. Perform the actions you want to automate

4. Click Stop Recording

2.2 Example

• Format a table: bold headers, autofit columns, and apply borders

• Replay macro anytime to apply same formatting to new tables

3. Running a Macro

• Go to View → Macros → View Macros → Select macro → Run

• Or use the assigned shortcut key

• Example: Apply pre-defined formatting across multiple sheets

4. Writing Simple VBA Code

4.1 Accessing VBA Editor

1. Press Alt + F11 to open VBA Editor

2. Insert a new module: Insert → Module

3. Write VBA code inside the module

4.2 Example Code

• Highlights negative values in selected cells

5. Assigning Macros to Buttons

• Go to Developer → Insert → Button

• Assign macro to button

• Click button on sheet to run macro easily

5.1 Example

• Button to clean imported data: remove blanks, trim spaces, and format columns

6. Best Practices

1. Use descriptive macro names

2. Comment code for clarity (' This line formats headers)

3. Backup workbook before running new macros

4. Test macros on sample data before applying to important files

5. Avoid using hard-coded values; use variables and ranges for flexibility

7. Real-Life Examples

• Finance: Automate monthly report formatting

• HR: Highlight late attendance or missing data automatically

• Sales: Generate monthly dashboards from raw sales data

• Operations: Consolidate multiple sheets into a single summary sheet

8. Practice Questions

1. Record a macro that formats a table with bold headers and borders.

2. Write a VBA code to highlight negative numbers in a selected range.

3. Assign a macro to a button for quick execution.

4. Explain the difference between a recorded macro and VBA code.

5. Discuss best practices for using macros in a shared workbook.