Variables and loops in VBA in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this VBA loop?

Consider this VBA code inside a module:

Sub CountToFive()
  Dim i As Integer
  Dim result As String
  result = ""
  For i = 1 To 5
    result = result & i & ","
  Next i
  MsgBox result
End Sub

What will the message box show when this macro runs?

Excel

Sub CountToFive()
  Dim i As Integer
  Dim result As String
  result = ""
  For i = 1 To 5
    result = result & i & ","
  Next i
  MsgBox result
End Sub

A 1,2,3,4,5,

B 1,2,3,4,5

C 12345,

D Error: result variable not initialized

💡 Hint

Look at how the loop adds each number and a comma to the string.

The loop runs from 1 to 5. Each time it adds the number and a comma to the string. So the final string is "1,2,3,4,5,".

❓ Function Choice

intermediate

Which VBA loop correctly sums numbers 1 to 10?

You want to add numbers from 1 to 10 in VBA and store the total in a variable named total. Which loop code correctly does this?

A

Dim i As Integer
Dim total As Integer
total = 0
For i = 1 To 10
total = total * i
Next i

B

Dim i As Integer
Dim total As Integer
total = 0
For i = 1 To 10
total = i
Next i

C

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

D

Dim i As Integer
Dim total As Integer
total = 1
For i = 1 To 10
total = total + i
Next i

💡 Hint

Think about how to add each number to the total step by step.

Option C initializes total to 0 and adds each i from 1 to 10. This correctly sums the numbers.

Option C overwrites total each time, so only 10 remains.

Option C multiplies total by i, which calculates factorial, not sum.

Option C starts total at 1, so the sum is off by 1.

❓ data_analysis

advanced

How many times does this VBA loop run?

Look at this VBA code:

Dim count As Integer
count = 0
Dim i As Integer
For i = 10 To 1 Step -2
  count = count + 1
Next i
MsgBox count

What number will the message box show?

Excel

Dim count As Integer
count = 0
Dim i As Integer
For i = 10 To 1 Step -2
  count = count + 1
Next i
MsgBox count

A 4

B 5

C 6

D Error: Step value must be positive

💡 Hint

Count how many numbers are in the sequence 10, 8, 6, 4, 2.

The loop starts at 10 and decreases by 2 until it reaches 1 or less. The values are 10, 8, 6, 4, 2. That's 5 numbers, so count increments 5 times (option B).

🎯 Scenario

advanced

You want to exit a loop early in VBA. Which code does this?

You have a loop in VBA and want to stop it when a variable found becomes True. Which code snippet correctly exits the loop early?

A

For i = 1 To 10
  If found = True Then Stop
  ' other code
Next i

B

For i = 1 To 10
  If found = True Then Continue
  ' other code
Next i

C

For i = 1 To 10
  If found = True Then Break
  ' other code
Next i

D

For i = 1 To 10
  If found = True Then Exit For
  ' other code
Next i

💡 Hint

Think about the VBA keyword to exit a loop immediately.

In VBA, Exit For stops the loop immediately. There is no Continue or Break keyword in VBA loops. Stop pauses code execution for debugging.

🧠 Conceptual

expert

What error occurs with this VBA variable declaration?

Consider this VBA code snippet:

Dim total As Integer
Dim total As String
total = 5

What happens when you run this code?

Excel

Dim total As Integer
Dim total As String
total = 5

A Compile error: Duplicate declaration of variable 'total'

B Runtime error: Type mismatch

C No error, total is treated as String

D No error, total is treated as Integer

💡 Hint

Can you declare the same variable name twice with different types in VBA?

VBA does not allow declaring the same variable name twice in the same scope. This causes a compile error for duplicate declaration.