Running macros in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Running a Macro from a Button

You want to run a macro named FormatReport by clicking a button on your Excel sheet. Which step correctly assigns the macro to the button?

A Insert a shape, then type '=FormatReport()' in the formula bar.

B Double-click the button to open the macro editor and type 'FormatReport'.

C Click the button and press Ctrl+R to run the macro automatically.

D Right-click the button, choose 'Assign Macro', then select 'FormatReport' and click OK.

💡 Hint

Think about how Excel links buttons to macros without typing formulas.

To run a macro from a button, you right-click the button, choose 'Assign Macro', then pick the macro name. Other options either don't link macros or use wrong methods.

📊 Formula Result

intermediate

Macro to Clear Cells

You run this macro to clear cells A1 to A3. What will be the value in cell A2 after running?

Excel

Sub ClearCells()
  Range("A1:A3").ClearContents
End Sub

A "" (empty cell)

B 0

C Error #REF!

D The original value remains unchanged

💡 Hint

ClearContents removes the content but not the cell itself.

The ClearContents method empties the cells' contents, so A2 becomes empty (blank).

❓ Function Choice

advanced

Choosing the Right VBA Method to Run a Macro

You want to run a macro named UpdateSummary from another macro. Which VBA statement correctly runs it?

A Call UpdateSummary

B ExecuteMacro UpdateSummary

C RunMacro("UpdateSummary")

D StartMacro("UpdateSummary")

💡 Hint

Think about the VBA keyword used to call another macro or procedure.

The correct way to run another macro in VBA is to use Call MacroName or simply MacroName. The other options are not valid VBA statements.

❓ data_analysis

advanced

Macro Execution Effect on Workbook Data

A macro runs and deletes all rows where column B is empty. After running, how many rows remain if the original data had 10 rows and 3 had empty cells in column B?

Excel

Sub DeleteEmptyRows()
  Dim i As Integer
  For i = 10 To 1 Step -1
    If Cells(i, 2).Value = "" Then Rows(i).Delete
  Next i
End Sub

A 0 rows remain

B 7 rows remain

C 10 rows remain

D 3 rows remain

💡 Hint

Rows with empty column B cells are deleted one by one from bottom to top.

The macro deletes rows where column B is empty. Since 3 rows have empty column B, those 3 are deleted, leaving 7 rows.

🧠 Conceptual

expert

Understanding Macro Security Settings

Which macro security setting in Excel allows macros to run only if they are digitally signed by a trusted publisher?

A Enable all macros (not recommended)

B Disable all macros with notification

C Disable all macros except digitally signed macros

D Disable all macros without notification

💡 Hint

Think about the setting that trusts only signed macros.

The setting 'Disable all macros except digitally signed macros' allows only macros signed by trusted publishers to run, blocking unsigned macros.

Practice

1. What is the easiest way to run a macro in Excel without writing any code?

easy

A. Press Alt + F8 and select the macro to run

B. Open the Visual Basic Editor and type the macro name

C. Right-click a cell and choose 'Run Macro'

D. Use the formula bar to enter the macro name

Solution

1. Step 1: Understand how to run macros easily

   Excel provides a shortcut Alt + F8 to open the macro list quickly.

2. Step 2: Use Alt + F8 to select and run

   After pressing Alt + F8, you can pick the macro and click Run to execute it.

3. Final Answer:

   Press Alt + F8 and select the macro to run -> Option A

4. Quick Check:

   Alt + F8 runs macros easily [OK]

Hint: Use Alt + F8 to open and run macros fast [OK]

Common Mistakes:

• Trying to run macros from the formula bar
• Thinking right-click menu runs macros
• Confusing Visual Basic Editor with macro running

2. Which of the following is the correct way to assign a macro to a button in Excel?

easy

A. Drag the macro from Visual Basic Editor onto the button

B. Double-click the button and type the macro name

C. Insert a button from Developer tab, then right-click and choose 'Assign Macro'

D. Use the formula =RunMacro() on the button

Solution

1. Step 1: Insert a button from Developer tab

   Excel lets you add buttons via Developer > Insert > Form Controls.

2. Step 2: Assign macro by right-clicking the button

   Right-click the button and select 'Assign Macro' to link your macro.

3. Final Answer:

   Insert a button from Developer tab, then right-click and choose 'Assign Macro' -> Option C

4. Quick Check:

   Assign macro via button right-click [OK]

Hint: Right-click button to assign macro quickly [OK]

Common Mistakes:

• Trying to drag macros onto buttons
• Using formulas to run macros
• Double-clicking button does not assign macro

3. Given this VBA macro code:

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

What happens when you run this macro?

medium

A. A message box appears showing 'Hello, World!'

B. The text 'Hello, World!' is entered in the active cell

C. Nothing happens because MsgBox is incorrect

D. Excel crashes due to syntax error

Solution

1. Step 1: Understand the MsgBox function

   MsgBox displays a popup message box with the given text.

2. Step 2: Running ShowMessage macro shows the message

   When run, the macro triggers MsgBox to show 'Hello, World!'.

3. Final Answer:

   A message box appears showing 'Hello, World!' -> Option A

4. Quick Check:

   MsgBox shows popup message [OK]

Hint: MsgBox shows popup messages in macros [OK]

Common Mistakes:

• Thinking MsgBox writes to cells
• Assuming MsgBox is a syntax error
• Expecting no output from macro

4. You wrote this macro:

Sub AddNumbers()
  Range("A1") = Range("A1") + Range("B1")
End Sub

But running it gives an error. What is the likely problem?

medium

A. You must use Cells() instead of Range() to add numbers

B. Range references need to be declared as variables first

C. The macro syntax is invalid because of missing parentheses

D. The macro tries to add text values causing a type mismatch error

Solution

1. Step 1: Check the data types in cells A1 and B1

   If either cell contains text, adding causes a type mismatch error.

2. Step 2: Understand VBA does not auto-convert text to numbers

   VBA needs numeric values to add; text causes error.

3. Final Answer:

   The macro tries to add text values causing a type mismatch error -> Option D

4. Quick Check:

   Adding text causes type mismatch error [OK]

Hint: Ensure cells contain numbers before adding in macros [OK]

Common Mistakes:

• Assuming Range() needs variable declaration
• Thinking syntax error is due to parentheses
• Believing Cells() must replace Range() always

5. You want to create a macro that clears the contents of cells A1 to A5 and then enters the text 'Done' in cell A6. Which VBA code correctly does this?

hard

A. Sub ClearAndDone()
Clear Range("A1:A5")
Range("A6") = "Done"
End Sub

B. Sub ClearAndDone()
Range("A1:A5").ClearContents
Range("A6") = "Done"
End Sub

C. Sub ClearAndDone()
Range("A1:A5").Clear
Range("A6").Value = "Done"
End Sub

D. Sub ClearAndDone()
Range("A1:A5").ClearContents()
Range("A6").Text = "Done"
End Sub

Solution

1. Step 1: Use ClearContents to clear cells A1 to A5

   ClearContents removes only the data, keeping formatting intact.

2. Step 2: Assign text 'Done' to cell A6 correctly

   Using Range("A6") = "Done" sets the cell value properly.

3. Final Answer:

   Sub ClearAndDone() Range("A1:A5").ClearContents Range("A6") = "Done" End Sub -> Option B

4. Quick Check:

   ClearContents + assign text = correct macro [OK]

Hint: Use ClearContents to clear cells, assign text directly [OK]

Common Mistakes:

• Using Clear instead of ClearContents (clears formatting too)
• Wrong syntax like Clear Range(...)
• Using .Text property to assign cell value