Simple VBA procedures in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this VBA procedure?

Consider this VBA code in Excel:

Sub ShowMessage()
  MsgBox "Hello, Excel!"
End Sub

What happens when you run ShowMessage?

Excel

Sub ShowMessage()
  MsgBox "Hello, Excel!"
End Sub

A Excel crashes with an error

B The text "Hello, Excel!" is written into the active cell

C Nothing happens because MsgBox is not a valid VBA command

D A message box appears showing the text "Hello, Excel!"

💡 Hint

MsgBox shows a popup message box with the text you give it.

The MsgBox function in VBA displays a popup message box with the text inside the quotes. So running ShowMessage shows a box with "Hello, Excel!".

❓ Function Choice

intermediate

Which VBA procedure clears the contents of cell A1?

You want a VBA procedure that clears the content of cell A1 on the active worksheet. Which code does this correctly?

A

Sub ClearA1()
  Range("A1").ClearContents
End Sub

B

Sub ClearA1()
  Range("A1").Clear
End Sub

C

Sub ClearA1()
  Range("A1").Value = ""
End Sub

D

Sub ClearA1()
  Cells(1, 1).Delete
End Sub

💡 Hint

ClearContents removes the content but keeps formatting.

Option A uses ClearContents which removes the cell's content but keeps formatting intact. Option A deletes the cell which shifts cells and is not recommended. Option A sets the value to empty string which looks empty but is not the same as clearing. Option A clears content and formatting, which is more than asked.

🎯 Scenario

advanced

You want to create a VBA procedure that adds 10 to the value in cell B2. Which code works correctly?

You have a number in cell B2. You want a VBA procedure that adds 10 to that number and puts the result back in B2. Which code does this correctly?

A

Sub AddTen()
  Range("B2") = Range("B2") + 10
End Sub

B

Sub AddTen()
  Range("B2").Value = Range("B2").Value + 10
End Sub

C

Sub AddTen()
  Cells(2, 2).Value = Cells(2, 2).Value + 10
End Sub

D

Sub AddTen()
  Range("B2").Text = Range("B2").Text + 10
End Sub

💡 Hint

Use the Value property to read and write cell values.

Option B correctly reads the value from B2, adds 10, and writes it back. Option B lacks explicit .Value (uses default property) and is less preferred. Option B also works correctly but is not the best answer because it uses Cells notation (valid but less clear). Option B uses .Text which is read-only and cannot be assigned to.

📊 Formula Result

advanced

What error does this VBA procedure cause?

Look at this VBA code:

Sub TestError()
  Dim x As Integer
  x = "Hello"
End Sub

What happens when you run TestError?

Excel

Sub TestError()
  Dim x As Integer
  x = "Hello"
End Sub

A Syntax error when compiling

B No error, x becomes 0

C Type Mismatch error at runtime

D Variable x stores the string "Hello"

💡 Hint

You cannot assign text to an Integer variable.

Variable x is declared as Integer but assigned a string "Hello". This causes a Type Mismatch error at runtime because the types do not match.

❓ data_analysis

expert

How many cells will be colored after running this VBA procedure?

Consider this VBA code:

Sub ColorCells()
  Dim i As Integer
  For i = 1 To 5
    If Cells(i, 1).Value > 10 Then
      Cells(i, 1).Interior.Color = vbYellow
    End If
  Next i
End Sub

Assuming cells A1 to A5 contain the values: 5, 12, 8, 15, 20 respectively, how many cells will be colored yellow after running ColorCells?

Excel

Sub ColorCells()
  Dim i As Integer
  For i = 1 To 5
    If Cells(i, 1).Value > 10 Then
      Cells(i, 1).Interior.Color = vbYellow
    End If
  Next i
End Sub

A 3

B 2

C 5

D 0

💡 Hint

Count how many values in A1:A5 are greater than 10.

Values greater than 10 are in cells A2 (12), A4 (15), and A5 (20). So 3 cells get colored yellow.

Practice

1. What does a simple VBA procedure in Excel always start with?

easy

A. Sub

B. Function

C. Begin

D. Start

Solution

1. Step 1: Understand VBA procedure structure

   A simple VBA procedure always begins with the keyword Sub to define a subroutine.

2. Step 2: Confirm the correct starting keyword

   The procedure does not start with Function, Begin, or Start, only Sub.

3. Final Answer:

   Sub -> Option A

4. Quick Check:

   Simple VBA procedures start with Sub [OK]

Hint: Remember VBA subs always start with 'Sub' keyword [OK]

Common Mistakes:

• Confusing Sub with Function
• Using Begin or Start instead of Sub
• Forgetting to write Sub at the start

2. Which of the following is the correct way to end a simple VBA procedure?

easy

A. End Function

B. Stop

C. End Sub

D. Exit Sub

Solution

1. Step 1: Identify the correct ending for a Sub procedure

   A simple VBA procedure defined with Sub must end with End Sub.

2. Step 2: Differentiate from other endings

   End Function ends a function, Stop pauses code, and Exit Sub exits early but does not end the procedure.

3. Final Answer:

   End Sub -> Option C

4. Quick Check:

   Simple VBA procedures end with End Sub [OK]

Hint: End VBA subs with 'End Sub' exactly [OK]

Common Mistakes:

• Using End Function for subs
• Confusing Exit Sub with End Sub
• Writing Stop instead of End Sub

3. What will the following VBA code do when run?

Sub ShowMessage()
  MsgBox "Hello, Excel!"
End Sub

medium

A. Save the workbook automatically

B. Clear all cells in the worksheet

C. Change the background color of cells

D. Display a message box saying 'Hello, Excel!'

Solution

1. Step 1: Analyze the MsgBox command

   The code uses MsgBox to show a popup message with the text "Hello, Excel!".

2. Step 2: Confirm no other actions are present

   There are no commands to clear cells, change colors, or save the workbook.

3. Final Answer:

   Display a message box saying 'Hello, Excel!' -> Option D

4. Quick Check:

   MsgBox shows a popup message [OK]

Hint: MsgBox always shows a popup message [OK]

Common Mistakes:

• Thinking MsgBox clears cells
• Assuming MsgBox changes colors
• Believing MsgBox saves files

4. Identify the error in this VBA procedure:

Sub ClearCells()
  Range("A1:A10").ClearContents
End Function

medium

A. Procedure ends with End Function instead of End Sub

B. Missing parentheses in ClearContents

C. Range syntax is incorrect

D. Sub keyword is misspelled

Solution

1. Step 1: Check procedure start and end keywords

   The procedure starts with Sub but incorrectly ends with End Function.

2. Step 2: Confirm correct ending for Sub procedures

   Sub procedures must end with End Sub, not End Function.

3. Final Answer:

   Procedure ends with End Function instead of End Sub -> Option A

4. Quick Check:

   Sub procedures end with End Sub [OK]

Hint: Match Sub with End Sub, not End Function [OK]

Common Mistakes:

• Ending Sub with End Function
• Adding unnecessary parentheses to ClearContents
• Incorrect Range syntax
• Misspelling Sub keyword

5. You want to write a VBA procedure that changes the background color of cells A1 to A5 to yellow. Which code snippet correctly does this?

hard

A. Sub ColorCells()
Cells(1,1).Color = vbYellow
End Sub

B. Sub ColorCells()
Range("A1:A5").Interior.Color = vbYellow
End Sub

C. Sub ColorCells()
Range("A1:A5").Color = Yellow
End Sub

D. Sub ColorCells()
Range("A1:A5").Interior.ColorIndex = "Yellow"
End Sub

Solution

1. Step 1: Understand how to set cell background color in VBA

   Use Range(...).Interior.Color with a color constant like vbYellow.

2. Step 2: Check each option for correct syntax

   Sub ColorCells() Range("A1:A5").Interior.Color = vbYellow End Sub uses correct syntax. Sub ColorCells() Range("A1:A5").Color = Yellow End Sub uses .Color without Interior and 'Yellow' without vb prefix. Sub ColorCells() Cells(1,1).Color = vbYellow End Sub changes only one cell and uses .Color incorrectly. Sub ColorCells() Range("A1:A5").Interior.ColorIndex = "Yellow" End Sub uses ColorIndex with a string, which is invalid.

3. Final Answer:

   Sub ColorCells() Range("A1:A5").Interior.Color = vbYellow End Sub -> Option B

4. Quick Check:

   Use Interior.Color = vbYellow to color cells [OK]

Hint: Use Interior.Color = vbYellow to color cells [OK]

Common Mistakes:

• Using .Color without Interior
• Passing color name as string instead of constant
• Changing only one cell instead of range
• Using ColorIndex with string value