Simple VBA procedures in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a VBA procedure named MyMacro.

Excel

Sub [1]()
    ' Your code here
End Sub

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

AMacro1

BStartMacro

CRunMacro

DMyMacro

Attempts:

3 left

💡 Hint

Common Mistakes

Using spaces in the procedure name

Starting the procedure without Sub

Using reserved words as names

The Sub keyword starts a VBA procedure, and the name follows it. Here, MyMacro is the procedure name.

2fill in blank

medium

Complete the code to display a message box with the text "Hello World".

Excel

Sub ShowMessage()
    [1] "Hello World"
End Sub

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

AShowMsg

BMessageBox

CMsgBox

DDisplay

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect command names like MessageBox

Forgetting to put the message text in quotes

Missing parentheses (optional in VBA but recommended)

MsgBox is the VBA command to show a message box with a message.

3fill in blank

hard

Fix the error in the code to correctly assign the value 10 to cell A1 in the active worksheet.

Excel

Sub SetValue()
    ActiveSheet.Range("A1").[1] = 10
End Sub

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

AText

BValue

CContent

DSet

Attempts:

3 left

💡 Hint

Common Mistakes

Using Set which is a VBA keyword but not a property

Using Content which is not a valid property

Trying to assign value to Text property

The Value property sets or returns the value of a cell. This is the correct way to assign a value.

4fill in blank

hard

Fill both blanks to create a procedure that loops from 1 to 5 and shows each number in a message box.

Excel

Sub ShowNumbers()
    Dim i As Integer
    For [1] = 1 To 5
        MsgBox [2]
    Next i
End Sub

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

Ai

Bj

Attempts:

3 left

💡 Hint

Common Mistakes

Using different variable names in the loop and message box

Using undeclared variables

Forgetting to declare the variable

The loop variable i is declared and used in the For loop and inside the MsgBox to show the current number.

5fill in blank

hard

Fill all three blanks to create a procedure that sums numbers from 1 to 10 and shows the result.

Excel

Sub SumNumbers()
    Dim total As Integer
    total = 0
    Dim [1] As Integer
    For [2] = 1 To 10
        total = total + [3]
    Next [2]
    MsgBox total
End Sub

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

Ai

Dj

Attempts:

3 left

💡 Hint

Common Mistakes

Using different variable names in declaration and loop

Forgetting to declare the loop variable

Using undeclared variables in the sum

The variable i is declared and used as the loop counter and added to total each time.

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