CLASSWORK: Advanced Spreadsheet Tools – Working with Macros
Learning Objectives
By the end of this class, students should be able to:
Record and edit macros
Use relative & absolute references in macros
Write simple VBA macro code
Automate data cleaning
Create automated reports using macros
Assign macros to buttons
PART 1: DATASET
Copy the following dataset into Excel starting at cell A1
Sales Dataset
Payment
OrderID Date Salesperson Region Product Category Units UnitPrice
Mode
1001 05-01-2025 John North Laptop Electronics 3 800 Card
1002 07-01-2025 Mary South Printer Electronics 5 150 Cash
1003 08-01-2025 David East Desk Furniture 2 300 Card
1004 08-01-2025 John North Chair Furniture 6 120 UPI
1005 09-01-2025 Sarah West Phone Electronics 4 600 Card
1006 10-01-2025 Mary South Table Furniture 1 450 Cash
1007 11-01-2025 David East Laptop Electronics 2 800 UPI
1008 12-01-2025 Sarah West Printer Electronics 3 150 Card
1009 13-01-2025 John North Desk Furniture 1 300 Cash
1010 14-01-2025 Mary South Phone Electronics 5 600 Card
PART 2: TASKS
Task 1: Record a Macro (Basic Automation)
Objective:
Create a macro that:
1. Inserts a new column named TotalAmount
2. Calculates Units × UnitPrice
3. Applies:
Currency format
AutoFit columns
Bold header row
Table formatting
Steps:
Go to Developer → Record Macro
Name: FormatSalesData
Perform formatting steps
Stop recording
Run the macro
Task 2: Macro to Clean Data
Write a VBA Macro to:
Remove duplicate records
Convert all Salesperson names to Proper case
Highlight Electronics category rows in light blue
VBA Code (Students Type This)
Sub CleanSalesData()
Dim lastRow As Long
lastRow = Cells(RowsCount, 1)End(xlUp)Row
'Remove Duplicates
Range("A1:I" & lastRow)RemoveDuplicates Columns:=1, Header:=xlYes
'Proper case for Salesperson
Dim i As Long
For i = 2 To lastRow
Cells(i, 3)Value = ApplicationWorksheetFunctionProper(Cells(i, 3)Value)
Next i
'Highlight Electronics
For i = 2 To lastRow
If Cells(i, 6)Value = "Electronics" Then
Rows(i)InteriorColor = RGB(173, 216, 230)
End If
Next i
End Sub
Task 3: Create Automated Summary Report
Create a macro that:
1. Inserts a new sheet named Summary
2. Displays:
Total Sales
Sales by Region
Sales by Category
Macro Code
Sub CreateSummary()
Dim ws As Worksheet
Set ws = SheetsAdd
wsName = "Summary"
wsRange("A1")Value = "Total Sales"
wsRange("B1")Formula = "=SUM(Sheet1!J:J)"
End Sub
(Students extend this using Pivot Tables recorded via macro)
Task 4: Create Button to Run Macro
1. Insert → Shapes → Button
2. Right-click → Assign Macro
3. Link to:
CleanSalesData
CreateSummary
PART 3: Advanced Challenge (Higher-Level Students)
Challenge 1:
Modify the macro to:
Automatically add current date/time when report is generated
wsRange("A3")Value = "Report Generated On:"
wsRange("B3")Value = Now
Challenge 2:
Create a macro that:
Filters data for a selected region
Exports it to a new Excel file
Saves file as: RegionName_Reportxlsx
Expected Learning Outcomes
Students will be able to:
Record macros
Understand VBA structure (Sub…End Sub)
Use loops (For Next)
Use conditional statements (If)
Automate repetitive tasks
Generate automated reports
Assessment Questions
A. What is the difference between relative and absolute macro recording?
B. What does xlUp mean in VBA?
C. Why is ApplicationWorksheetFunctionProper used?
D. How can you assign a macro to a button?
E. What are the advantages of macros in large datasets?