Case Studies & Practical Application Flashcards

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

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

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

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?

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

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

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?

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

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

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

Answer: Worksheet_Change event that checks if the Target intersects the named range

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

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?

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

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

You are automating data entry into a web form using VBA and Internet Explorer Automation. The form has a dropdown menu. How do you select a specific option by its visible text?

Answer: Find the SELECT element, iterate its .options collection, and set .selectedIndex when .text matches the target

You must iterate the options collection of the SELECT element and set selectedIndex to the matching option's index to programmatically choose a value.

A client wants a VBA macro that protects all sheets in a workbook with one password but still allows the macro itself to make edits programmatically. What is the correct approach?

Answer: In the macro, call Sheet.Unprotect with the password before edits and Sheet.Protect with the password after edits

VBA can call Unprotect and Protect with the password string to temporarily lift and restore sheet protection around programmatic edits.