VBA Programming Exercises
Learn to automate repetitive Excel tasks using VBA macros, variables, conditions, loops, worksheet objects, and error handling. Practise creating reusable macros for data entry, report formatting, calculations, and business report automation.
1. Introduction to VBA Programming
VBA stands for Visual Basic for Applications. It is the programming language built into Microsoft Office applications, including Excel. VBA allows users to automate repetitive operations and create procedures that perform tasks automatically.
For example, a sales reporting task may require formatting headings, calculating totals, highlighting important values, and preparing reports. Instead of performing each operation manually, a VBA macro can execute these steps in sequence.
VBA is particularly useful when a task follows a predictable process and needs to be repeated regularly. However, macros should be tested carefully because they can change workbook data.
VBA Automation Workflow
A reliable macro follows a defined process, validates its results, and produces the required output.
2. Set Up the VBA Environment
Before writing VBA code, enable the Developer tab in Excel and open the Visual Basic Editor.
Step 1: Enable the Developer Tab
- Open Microsoft Excel.
- Select File → Options.
- Choose Customize Ribbon.
- Enable the Developer checkbox.
- Click OK.
Step 2: Open the VBA Editor
Press Alt + F11 to open the Visual Basic Editor. This is where VBA modules and procedures are created.
Step 3: Insert a Module
- Open the Project Explorer.
- Select your workbook project.
- Choose Insert → Module.
- Write or paste the VBA code into the module window.
Step 4: Save the Workbook
Save a workbook containing VBA code as an Excel Macro-Enabled Workbook (.xlsm). The standard .xlsx format does not preserve VBA macros.
3. Understand VBA Program Structure
A VBA procedure is a named block of instructions. A Sub procedure can perform an operation such as displaying a message, calculating a value, or formatting a worksheet.
Option Explicit
Sub FirstMacro()
MsgBox "Welcome to VBA Programming!"
End Sub
Explanation of the Code
| Statement | Purpose |
|---|---|
Option Explicit |
Requires variables to be declared before use. |
Sub FirstMacro() |
Begins a procedure named FirstMacro. |
MsgBox |
Displays a message box to the user. |
End Sub |
Marks the end of the procedure. |
How to Execute the Macro
- Open the VBA Editor.
- Insert a module and paste the code.
- Place the cursor inside the procedure.
- Press F5 to run the macro.
- Check the message displayed by Excel.
4. Variables and Data Types
Variables store values temporarily while a procedure is running. Declare variables using the Dim statement and select an appropriate data type for each value.
Option Explicit
Sub CalculateTotal()
Dim quantity As Long
Dim price As Double
Dim total As Double
quantity = 5
price = 250
total = quantity * price
MsgBox "Total Amount: " & total
End Sub
Common VBA Data Types
| Data Type | Purpose | Example |
|---|---|---|
| Integer | Stores smaller whole numbers. | Dim count As Integer |
| Long | Stores whole numbers with a larger range. | Dim rowNumber As Long |
| Double | Stores floating-point numeric values. | Dim price As Double |
| String | Stores text. | Dim employeeName As String |
| Boolean | Stores True or False. | Dim isValid As Boolean |
| Date | Stores dates and times. | Dim orderDate As Date |
| Worksheet | References an Excel worksheet object. | Dim ws As Worksheet |
5. Conditional Statements in VBA
Conditional statements allow a macro to make decisions based on values. The If...Then...Else structure is commonly used to check sales targets, marks, attendance, and other business conditions.
Sub CheckSalesTarget()
Dim sales As Double
Dim target As Double
sales = 75000
target = 60000
If sales >= target Then
MsgBox "Sales target achieved."
Else
MsgBox "Sales target not achieved."
End If
End Sub
Explanation
- Declare variables for actual sales and the target.
- Assign values to both variables.
- Compare sales with the target using the greater-than-or-equal operator.
- Display the appropriate message for the condition.
Multiple Conditions
Sub CheckPerformance()
Dim marks As Double
marks = 82
If marks >= 75 Then
MsgBox "Distinction"
ElseIf marks >= 40 Then
MsgBox "Pass"
Else
MsgBox "Fail"
End If
End Sub
The conditions are evaluated from top to bottom. The first condition that evaluates to True determines which block runs.
6. Loops in VBA
Loops repeat a block of instructions. They are useful when processing multiple rows, filling serial numbers, formatting ranges, or checking a large number of records.
6.1 For Loop
Sub FillSerialNumbers()
Dim i As Long
For i = 1 To 10
Cells(i, 1).Value = i
Next i
End Sub
This macro writes numbers from 1 to 10 into cells A1 to A10 of the active worksheet.
6.2 For Each Loop
Sub FormatAllWorksheetHeaders()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1:C1").Font.Bold = True
ws.Columns("A:C").AutoFit
Next ws
End Sub
The For Each loop visits every worksheet in the workbook containing the code and formats the specified columns.
How a VBA Loop Works
A loop checks its condition, executes the instructions, advances the counter, and stops when the condition is false.
7. Work with Excel Worksheets and Cells
VBA provides objects such as Workbook, Worksheet, Range, and Cells to interact with Excel. These objects allow macros to read data, write values, apply formatting, and perform calculations.
7.1 Write Data into Cells
Sub WriteEmployeeDetails()
Range("A1").Value = "Employee Name"
Range("B1").Value = "Department"
Range("A2").Value = "Amit"
Range("B2").Value = "Sales"
End Sub
7.2 Read a Cell Value
Sub ReadEmployeeName()
Dim employeeName As String
employeeName = CStr(Range("A2").Value)
MsgBox "Employee Name: " & employeeName
End Sub
7.3 Use Worksheet Variables
Sub WriteUsingWorksheetObject()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A1").Value = "Sales Report"
ws.Range("A2").Value = 50000
End Sub
Replace Sheet1 with the actual worksheet name in
your workbook. Using a worksheet variable helps make the target
of the operation explicit.
8. Automate Excel Report Formatting
Formatting is a common repetitive task in Excel. VBA can apply consistent heading styles, font colours, background colours, alignment, and column widths.
Sub FormatSalesReport()
Dim ws As Worksheet
Set ws = ActiveSheet
With ws.Range("A1:C1")
.Font.Bold = True
.Font.Color = RGB(255, 255, 255)
.Interior.Color = RGB(31, 78, 121)
.HorizontalAlignment = xlCenter
End With
ws.Columns("A:C").AutoFit
MsgBox "Sales report formatting completed."
End Sub
Explanation of Formatting Properties
| Property | Purpose |
|---|---|
Font.Bold |
Makes the text bold. |
Font.Color |
Changes the font colour. |
Interior.Color |
Changes the cell background colour. |
HorizontalAlignment |
Controls horizontal alignment. |
Columns("A:C").AutoFit |
Adjusts column widths to fit their contents. |
9. Process Data Using VBA
A macro can read rows, validate values, apply business rules, and write the results into a worksheet. The following exercise uses a sales dataset to calculate the status of each record.
Sample Dataset
| Employee | Sales (₹) | Sales Status |
|---|---|---|
| Amit | 75000 | Target Achieved |
| Neha | 42000 | Below Target |
| Priya | 68000 | Target Achieved |
| Omkar | 55000 | Target Achieved |
Assume the Employee heading is in A1 and Sales is in B1. The macro will write Sales Status into column C.
Option Explicit
Sub CheckEmployeeSales()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim salesValue As Double
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
ws.Range("C1").Value = "Sales Status"
For i = 2 To lastRow
If Len(Trim(CStr(ws.Cells(i, "B").Value))) = 0 Then
ws.Cells(i, "C").Value = "Missing Sales"
ElseIf IsNumeric(ws.Cells(i, "B").Value) Then
salesValue = CDbl(ws.Cells(i, "B").Value)
If salesValue >= 50000 Then
ws.Cells(i, "C").Value = "Target Achieved"
Else
ws.Cells(i, "C").Value = "Below Target"
End If
Else
ws.Cells(i, "C").Value = "Check Sales Data"
End If
Next i
MsgBox "Sales status processing completed."
End Sub
Step-by-Step Explanation
- Assign the active worksheet to the ws variable.
- Find the last used row in column B.
- Set the heading for the output column.
- Loop through the data rows beginning at row 2.
- Check whether the sales cell is blank.
- Verify that the sales value is numeric.
- Compare valid sales with the target of ₹50,000.
- Write the result into column C.
10. Error Handling in VBA
Error handling helps a procedure respond to unexpected runtime errors. It is useful when a macro works with worksheets, external files, calculations, or data that may not be in the expected format.
Sub SafeDivision()
On Error GoTo ErrorHandler
Dim numerator As Double
Dim denominator As Double
Dim result As Double
numerator = 100
denominator = 0
If denominator = 0 Then
MsgBox "Denominator cannot be zero."
Exit Sub
End If
result = numerator / denominator
MsgBox "Result: " & result
Exit Sub
ErrorHandler:
MsgBox "Unexpected error: " & Err.Description
End Sub
Important Error Handling Statements
-
On Error GoTo ErrorHandlerdirects a runtime error to the named error-handling section. -
Err.Descriptionprovides a description of the current runtime error. -
Exit Subends the procedure before the error handler section is reached during normal execution.
The denominator check prevents division by zero in this example. The error handler provides additional protection against other runtime errors; it does not replace validation of input values.
11. VBA Automation Workflow for Reports
A report automation macro can combine several operations into one reusable procedure. For example, it can prepare headings, calculate totals, format the report, and display a completion message.
Automated Reporting Process
Report Automation Checklist
- Confirm that the source worksheet exists.
- Check that the expected columns contain valid data.
- Calculate totals and summary values.
- Apply consistent headings and number formats.
- Check the final report against the source data.
- Save the workbook after reviewing the output.
12. Practical VBA Programming Exercises
Complete the following exercises in Excel. Write each macro in a separate procedure, run it with sample data, and verify the output before moving to the next exercise.
| No. | Exercise | Concept | Expected Result |
|---|---|---|---|
| 1 | Display your name and today's date. | MsgBox and Date | Display both values in a message box. |
| 2 | Calculate the total price using quantity and price. | Variables | Display the calculated amount. |
| 3 | Check whether marks meet the passing threshold. | If...Then...Else | Display Pass or Fail. |
| 4 | Write serial numbers from 1 to 50 in column A. | For loop | Populate cells A1:A50. |
| 5 | Format the heading row of a worksheet. | Range and formatting | Bold headings with adjusted columns. |
| 6 | Identify sales values above ₹50,000. | Conditions and cells | Display the status for each record. |
| 7 | Apply formatting to every worksheet. | For Each loop | Format the selected headings in each sheet. |
| 8 | Count non-empty employee names in column A. | Worksheet functions | Display the count of non-empty names. |
| 9 | Display a warning if a required cell is blank. | Validation | Show a warning for missing data. |
| 10 | Create a reusable sales report formatting macro. | Automation | Produce a consistently formatted report. |
13. Mini Project — Sales Report Automation
Build a VBA macro that checks sales records, calculates each employee's sales status, formats the report headings, and displays a completion message.
Sample Data
| Employee | Department | Sales (₹) | Status |
|---|---|---|---|
| Amit | Sales | 75000 | Achieved |
| Neha | Sales | 42000 | Below Target |
| Priya | Sales | 68000 | Achieved |
| Omkar | Sales | 55000 | Achieved |
Project Requirements
- Read the employee and sales details from the worksheet.
- Check whether sales values are missing or invalid.
- Use ₹50,000 as the sales target.
- Write the status into the output column.
- Format the heading row and adjust column widths.
- Display a completion message after processing.
- Test the macro with both valid and invalid data.
Evaluation Checklist
- ☐ The macro executes without an unhandled error.
- ☐ Sales statuses are calculated correctly.
- ☐ Missing or invalid sales values are handled.
- ☐ The report headings are formatted consistently.
- ☐ The source data is preserved where required.
- ☐ The workbook is saved in .xlsm format.
14. Common VBA Programming Mistakes
| Mistake | Why It Is a Problem | Recommended Improvement |
|---|---|---|
| Using undeclared variables | Typing errors can create unexpected variables. | Use Option Explicit and declare variables. |
| Using the wrong worksheet | Data may be written to an unintended sheet. | Reference the required worksheet explicitly. |
| Using an incorrect loop range | Rows may be skipped or unintended cells changed. | Check loop boundaries and the last used row. |
| Ignoring invalid data | Unexpected values may cause incorrect results. | Validate input before performing calculations. |
| Not testing the macro | Errors may go unnoticed until the report is used. | Test different input values and edge cases. |
| Saving as .xlsx | VBA code is not preserved in the standard format. | Use the .xlsm format for macro-enabled workbooks. |
15. Important Points
- VBA automates repetitive Excel operations using macros.
- Use Sub and End Sub to define procedures.
- Declare variables with Dim and choose suitable data types.
- Use If...Then...Else for decision-making.
- Use For and For Each loops to process repeated tasks.
- Use Range and Cells to read and write worksheet data.
- Specify the worksheet when the target sheet matters.
- Validate values and handle runtime errors appropriately.
- Save workbooks containing VBA code in .xlsm format.
- Test macros on sample data and maintain backups before making important workbook changes.
16. Summary
In this module, you learned how to set up the VBA environment, write procedures, declare variables, use conditional statements and loops, interact with worksheet cells, format reports, process records, and handle runtime errors.
You also practised building a sales report automation workflow and explored common programming mistakes. These skills provide a foundation for automating repetitive reporting tasks in Excel.
Complete the practical exercises and Sales Report Automation mini project before moving to the Final Assignments module.