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.