Excel VBA Case Studies & Practical Application 3

Question 1

A VBA macro that processes a large dataset crashes with 'Out of Memory'. Which refactoring strategy is most likely to resolve the issue?

Accepted Answer

Break the dataset into smaller chunks, process each chunk, and release object references with Set obj = Nothing

Answer

Processing data in chunks and explicitly releasing object references with Set obj = Nothing frees memory incrementally and prevents exhaustion.

Question 2

You need to build a VBA solution that reads configuration settings (database connections, thresholds) that change periodically without modifying the VBA code. What is the best design pattern?

Accepted Answer

Store settings in a dedicated 'Config' worksheet or a separate INI/XML file and read them at runtime

Answer

Externalizing configuration to a worksheet or file decouples settings from code, allowing updates without opening the VBA editor.

Question 3

A macro populates a pivot table's source data daily. After refreshing the data, the pivot table does not reflect new rows. What must the VBA code do?

Accepted Answer

Update the pivot table's SourceData range to cover the new rows, then call PivotTable.RefreshTable

Answer

The pivot table's source data range must be explicitly expanded to include new rows before calling RefreshTable, otherwise new data is ignored.

Question 4

A workbook macro needs to run only when a specific named range value changes. Which event-driven approach should be used?

Accepted Answer

Worksheet_Change event that checks if the Target intersects the named range

Answer

The Worksheet_Change event fires on cell edits; using Intersect(Target, Range('NamedRange')) checks whether the changed cell is the one of interest.

Question 5

A manager asks you to add a progress indicator to a long-running VBA macro. Which technique provides visual feedback without requiring a separate UserForm?

Accepted Answer

Update the Excel status bar with Application.StatusBar = 'Processing row ' & i

Answer

Application.StatusBar updates the status bar text at the bottom of the Excel window, providing lightweight progress feedback during a running macro.

Excel VBA Practice Test

Excel VBA Practice Test

Excel VBA Case Studies & Practical Application 3