Explanation
VBA can combine multiple actions into a single automated workflow.
Example Workflow
Raw Data
↓
Clean Data
↓
Format Report
↓
Calculate Totals
↓
Create Summary
↓
Save Report
Example Macro
Sub CreateSalesReport()
Dim ws As Worksheet
Set ws = Worksheets(“Sales”)
ws.Range(“A1:H1”).Font.Bold = True
ws.Columns(“A:H”).AutoFit
MsgBox “Sales Report Updated Successfully!”
End Sub
Practice
Create a macro that:
- Formats the report.
- Autofits columns.
- Calculates totals.
- Creates a summary.
- Displays a completion message.
PROJECT 1 — AUTOMATED INVOICE
Create:
Automated_Invoice.xlsm
Features
- Customer Name
- Invoice Number
- Date
- Product
- Quantity
- Price
- Amount
- GST
- Grand Total
Automation
Create a Generate Invoice button that:
- Reads entered data.
- Calculates amounts.
- Calculates GST.
- Formats the invoice.
- Generates the final invoice.
PROJECT 2 — ATTENDANCE SYSTEM
Create:
Attendance_System.xlsm
Features
- Employee/Student Name
- Date
- Present/Absent
- Attendance %
- Monthly Summary
VBA Automation
Create a macro that:
- Records attendance
- Calculates attendance percentage
- Generates a summary
- Highlights low attendance
PROJECT 3 — AUTOMATED REPORT
Create:
Automated_Report.xlsm
Automation Workflow
Raw Data → Format → Calculate → Summary → Report
Macro should:
- Format headings
- Autofit columns
- Apply borders
- Calculate totals
- Create a summary
- Display completion message
PROJECT 4 — DATA ENTRY FORM
Create:
Data_Entry_Form.xlsm
UserForm Fields
- Name
- City
- Department
- Phone
- Salary
Buttons
Save | Clear | Close
Save Button
The entered information should automatically be added to the next empty row of the database.
IMPORTANT VBA FILE FORMAT
When a workbook contains macros, save it using:
Excel Macro-Enabled Workbook (.xlsm)
Do not save a macro-based project as a normal .xlsx file if you need to retain the VBA project.