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.