Excel VBA Case Studies & Practical Application 3 — Questions and Answers
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?
- Increase Excel's undo history stack size
- Break the dataset into smaller chunks, process each chunk, and release object references with Set obj = Nothing (Correct answer)
- Add more error handlers with On Error Resume Next
- Replace all integer variables with Long data type
Correct 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.
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?
- Hardcode all settings as Public constants in the module
- Store settings in a dedicated 'Config' worksheet or a separate INI/XML file and read them at runtime (Correct answer)
- Use Application.InputBox each time the macro runs
- Embed settings in the macro's comment block for the user to edit
Correct 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.
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?
- Call ActiveSheet.Calculate after inserting data
- Update the pivot table's SourceData range to cover the new rows, then call PivotTable.RefreshTable (Correct answer)
- Delete and recreate the pivot table each day
- Use Application.Volatile to force recalculation
Correct 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.
Question 4: A workbook macro needs to run only when a specific named range value changes. Which event-driven approach should be used?
- Workbook_Open event with a loop that polls the cell value
- Worksheet_Change event that checks if the Target intersects the named range (Correct answer)
- Application.OnTime called every second
- Workbook_BeforeSave event
Correct 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.
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?
- Flash the screen by toggling ScreenUpdating on and off
- Update the Excel status bar with Application.StatusBar = 'Processing row ' & i (Correct answer)
- Use MsgBox inside the loop to show each step
- Print progress to the Immediate Window with Debug.Print
Correct 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.
Question 6: 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?
- Set the element's .value property to the option text
- Find the SELECT element, iterate its .options collection, and set .selectedIndex when .text matches the target (Correct answer)
- Use SendKeys to type the option text into the dropdown
- Set the element's .innerText property directly
Correct 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.
Question 7: 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?
- Use AllowFiltering:=True on each sheet
- In the macro, call Sheet.Unprotect with the password before edits and Sheet.Protect with the password after edits (Correct answer)
- Store the password in a public variable and check it at runtime
- Use WorksheetFunction.Protect to bypass the restriction
Correct 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.
Excel VBA Case Studies & Practical Application 3
A VBA macro that processes a large dataset crashes with 'Out of Memory'.
Which refactoring strategy is most likely to resolve the issue?