Variables and loops in VBA in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to declare a variable named count as Integer.

Excel

Dim count As [1]

Drag options to blanks, or click blank then click option'

AString

BBoolean

CInteger

DDouble

Attempts:

3 left

💡 Hint

Common Mistakes

Using String instead of Integer for numbers.

Forgetting to declare the variable type.

In VBA, Integer is used to declare whole number variables like count.

2fill in blank

medium

Complete the code to start a For loop that counts from 1 to 5 using variable i.

Excel

For i = 1 To [1]
    ' Your code here
Next i

Drag options to blanks, or click blank then click option'

A5

B10

C0

Di

Attempts:

3 left

💡 Hint

Common Mistakes

Using 10 instead of 5 for the loop limit.

Using 0 which would not run the loop as expected.

The loop runs from 1 to 5, so the upper limit is 5.

3fill in blank

hard

Fix the error in the loop that sums numbers from 1 to 3 into variable total.

Excel

Dim total As Integer
total = 0
For j = 1 To 3
    total = total [1] j
Next j

Drag options to blanks, or click blank then click option'

A+

B-

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using multiplication or division instead of addition.

Using subtraction which would decrease the total.

To sum numbers, you add each number to total using the + operator.

4fill in blank

hard

Fill both blanks to create a loop that prints numbers from 1 to 4 using variable k.

Excel

For [1] = 1 To [2]
    Debug.Print k
Next k

Drag options to blanks, or click blank then click option'

Ak

Bi

C4

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different variable name than k.

Setting the upper limit to 5 instead of 4.

The loop variable is k and it counts from 1 to 4.

5fill in blank

hard

Fill all three blanks to create a loop that stores squares of numbers 1 to 3 in an array arr.

Excel

Dim arr(1 To 3) As Integer
Dim n As Integer
For [1] = 1 To [2]
    arr([3]) = n * n
Next n

Drag options to blanks, or click blank then click option'

An

B3

Di

Attempts:

3 left

💡 Hint

Common Mistakes

Using different variables for loop and array index.

Setting the loop limit incorrectly.

The loop variable is n, it runs from 1 to 3, and the array index uses n to store squares.

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