Variables and loops in VBA in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a variable in VBA?

A variable is a named storage that holds data which can change while the program runs. Think of it like a labeled box where you keep information to use later.

beginner

How do you declare a variable in VBA?

Use the Dim statement followed by the variable name and type. For example: Dim count As Integer creates a variable named count that stores whole numbers.

beginner

What is a For loop in VBA used for?

A For loop repeats a block of code a set number of times. It’s like telling Excel to do something over and over, counting from a start number to an end number.

intermediate

Explain the difference between For and Do While loops in VBA.

For loops run a fixed number of times decided before starting. Do While loops keep running as long as a condition is true, which might be unknown before the loop starts.

intermediate

What happens if you forget to increment the loop counter inside a Do While loop?

The loop may run forever because the condition never becomes false. This is called an infinite loop and can freeze your program.

Which statement correctly declares a variable named 'score' as a number in VBA?

A Dim score As Integer

B Var score = 0

C Declare score Number

D Set score = Integer

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

What does this VBA code do?
For i = 1 To 5
MsgBox i
Next i

A Shows one message box with number 5

B Creates 5 message boxes at once

C Shows message boxes with numbers 1 to 5 one by one

D Does nothing

The 'For' loop runs from 1 to 5, showing a message box with the current number each time.

Which loop type runs while a condition is true?

A For loop

B Do While loop

C Select Case

D If statement

'Do While' loops continue running as long as the condition remains true.

What keyword is used to end a 'For' loop in VBA?

A End For

B Loop

C Exit

D Next

The 'Next' keyword tells VBA to go to the next loop iteration or end the loop if done.

What is the risk of not changing the loop variable inside a 'Do While' loop?

A The loop may run forever (infinite loop)

B The loop will never run

C The loop will run only once

D The loop will skip iterations

If the condition never becomes false, the loop keeps running endlessly, causing an infinite loop.

Describe how to declare and use a variable in VBA with an example.

Think about how you tell Excel to remember a number or text.

Explain the difference between a 'For' loop and a 'Do While' loop in VBA and when you might use each.

One counts a set number of times, the other depends on a condition.

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