Variables and loops in VBA in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an Excel analyst working in the finance department.

📋 Request: Your manager wants you to automate the calculation of total sales for each product category using VBA. They want a macro that loops through sales data and sums sales by category.

📊 Data: You have a table with columns: Product Category, Product Name, and Sales Amount. The data has multiple rows for different products and categories.

🎯 Deliverable: Create a VBA macro that uses variables and loops to calculate total sales per product category and outputs the results in a new summary table.

Sample Data

Product Category	Product Name	Sales Amount
Electronics	Smartphone	500
Electronics	Laptop	800
Furniture	Chair	150
Furniture	Table	300
Clothing	T-shirt	50
Clothing	Jeans	100
Electronics	Headphones	200
Furniture	Sofa	700

1

Step 1: Open the VBA editor by pressing Alt + F11 in Excel.

Expected Result

VBA editor window opens.

2

Step 2: Insert a new module: In the VBA editor, click Insert > Module.

Expected Result

A new blank module appears to write code.

3

Step 3: Declare variables to store the last row, loop counter, category name, sales total, and a dictionary to hold totals.

Dim lastRow As Long Dim i As Long Dim category As String Dim salesTotal As Double Dim salesDict As Object Set salesDict = CreateObject("Scripting.Dictionary")

Expected Result

Variables declared and dictionary object created.

4

Step 4: Find the last row of data in column A (Product Category) to know how many rows to loop through.

lastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(xlUp).Row

Expected Result

lastRow is set to 9 (the last data row).

5

Step 5: Create a loop from row 2 to lastRow to read each product category and sales amount.

For i = 2 To lastRow category = Sheets("Sheet1").Cells(i, 1).Value salesTotal = Sheets("Sheet1").Cells(i, 3).Value

Expected Result

Loop starts reading each row's category and sales.

6

Step 6: Inside the loop, check if the category exists in the dictionary. If yes, add sales amount; if no, add new key with sales amount.

If salesDict.Exists(category) Then salesDict(category) = salesDict(category) + salesTotal Else salesDict.Add category, salesTotal End If Next i

Expected Result

Dictionary accumulates total sales per category.

7

Step 7: Output the results starting at cell E1 with headers 'Category' and 'Total Sales'.

Sheets("Sheet1").Cells(1, 5).Value = "Category" Sheets("Sheet1").Cells(1, 6).Value = "Total Sales"

Expected Result

Headers appear in cells E1 and F1.

8

Step 8: Use a loop to write each category and its total sales from the dictionary to the sheet starting at row 2.

Dim rowOut As Long rowOut = 2 For Each category In salesDict.Keys Sheets("Sheet1").Cells(rowOut, 5).Value = category Sheets("Sheet1").Cells(rowOut, 6).Value = salesDict(category) rowOut = rowOut + 1 Next category

Expected Result

Summary table with categories and total sales appears in columns E and F.

9

Step 9: Run the macro to see the total sales per product category.

Expected Result

Summary table shows Electronics: 1500, Furniture: 1150, Clothing: 150.

Final Result

Category     | Total Sales
--------------------------
Electronics  | 1500
Furniture    | 1150
Clothing    | 150

✓Electronics category has the highest total sales of 1500.

✓Furniture category follows with total sales of 1150.

✓Clothing category has the lowest total sales of 150.

Bonus Challenge

Modify the macro to also calculate and display the average sales per product category.

Show Hint

Use another dictionary to count the number of products per category, then divide total sales by count.

Practice

1. What is the main purpose of a variable in VBA?

easy

A. To format cells automatically

B. To repeat actions multiple times

C. To create a new worksheet

D. To store data that can be used later

Solution

1. Step 1: Understand what a variable does

   A variable holds information like numbers or text so you can use it later in your code.

2. Step 2: Compare with other options

   Repeating actions is done by loops, not variables. Creating worksheets or formatting cells are different tasks.

3. Final Answer:

   To store data that can be used later -> Option D

4. Quick Check:

   Variable = store data [OK]

Hint: Variables hold data; loops repeat actions [OK]

Common Mistakes:

• Confusing variables with loops
• Thinking variables create sheets
• Mixing variables with formatting tasks

2. Which of the following is the correct way to declare an integer variable named 'count' in VBA?

easy

A. Var count = Integer

B. Declare count Integer

C. Dim count As Integer

D. Set count Integer

Solution

1. Step 1: Recall VBA variable declaration syntax

   In VBA, variables are declared using 'Dim', followed by the name, then 'As' and the type.

2. Step 2: Check each option

   Only 'Dim count As Integer' follows this correct syntax. Others use wrong keywords or structure.

3. Final Answer:

   Dim count As Integer -> Option C

4. Quick Check:

   Use 'Dim name As Type' to declare variables [OK]

Hint: Use 'Dim variableName As Type' to declare variables [OK]

Common Mistakes:

• Using 'Declare' instead of 'Dim'
• Missing 'As' keyword
• Using 'Set' for simple variables

3. What will be the value of variable total after running this VBA code?

Dim total As Integer
total = 0
For i = 1 To 3
  total = total + i
Next i

medium

A. 3

B. 6

C. 0

D. 1

Solution

1. Step 1: Understand the loop range and initial value

   The loop runs from i = 1 to 3, and total starts at 0.

2. Step 2: Calculate total after each iteration

   Iteration 1: total = 0 + 1 = 1
   Iteration 2: total = 1 + 2 = 3
   Iteration 3: total = 3 + 3 = 6

3. Final Answer:

   6 -> Option B

4. Quick Check:

   Sum 1+2+3 = 6 [OK]

Hint: Sum numbers 1 to 3 to find total [OK]

Common Mistakes:

• Adding only last value
• Starting total at wrong number
• Misunderstanding loop range

4. Identify the error in this VBA code snippet:

Dim i As Integer
For i = 1 To 5
  MsgBox i
Next

medium

A. No error, code runs fine

B. Variable 'i' not declared

C. Missing 'Next i' instead of just 'Next'

D. MsgBox cannot be used inside loops

Solution

1. Step 1: Check variable declaration

   Variable 'i' is declared correctly as Integer.

2. Step 2: Check loop syntax

   Using 'Next' alone is valid in VBA; 'Next i' is optional.

3. Final Answer:

   No error, code runs fine -> Option A

4. Quick Check:

   'Next' alone is valid in VBA loops [OK]

Hint: 'Next' alone is valid to close loops [OK]

Common Mistakes:

• Thinking 'Next' must have variable name
• Assuming MsgBox can't be in loops
• Forgetting to declare variables

5. You want to create a VBA loop that adds all even numbers from 2 to 10 into a variable sumEven. Which code correctly does this?

hard

A. Dim sumEven As Integer
sumEven = 0
For i = 2 To 10 Step 2
sumEven = sumEven + i
Next i

B. Dim sumEven As Integer
sumEven = 0
For i = 1 To 10 Step 2
sumEven = sumEven + i
Next i

C. Dim sumEven As Integer
sumEven = 0
For i = 2 To 10
sumEven = sumEven + i
Next i

D. Dim sumEven As Integer
sumEven = 0
For i = 2 To 10 Step 3
sumEven = sumEven + i
Next i

Solution

1. Step 1: Understand the goal

   We want to add even numbers 2,4,6,8,10 to sumEven.

2. Step 2: Check each option's loop and addition

   Dim sumEven As Integer sumEven = 0 For i = 2 To 10 Step 2 sumEven = sumEven + i Next i uses 'Step 2' to jump by 2, adding only even numbers.
   Dim sumEven As Integer sumEven = 0 For i = 1 To 10 Step 2 sumEven = sumEven + i Next i starts from 1 with Step 2, adding odd numbers 1,3,5,7,9.
   Dim sumEven As Integer sumEven = 0 For i = 2 To 10 sumEven = sumEven + i Next i adds all numbers 2 to 10, including odd numbers.
   Dim sumEven As Integer sumEven = 0 For i = 2 To 10 Step 3 sumEven = sumEven + i Next i adds numbers 2,5,8 only (step 3), missing some even numbers.

3. Final Answer:

   Dim sumEven As Integer sumEven = 0 For i = 2 To 10 Step 2 sumEven = sumEven + i Next i -> Option A

4. Quick Check:

   Use 'Step 2' to loop over even numbers [OK]

Hint: Use 'Step 2' to loop through even numbers [OK]

Common Mistakes:

• Adding all numbers, not just even
• Using wrong step size
• Using Step 2 starting from 1, adding odds