Case Studies & Practical Application Flashcards

A financial analyst needs to loop through 10,000 rows and flag rows where the value in column C exceeds a threshold. Which approach avoids the performance hit of reading cells one-by-one?

Answer: Read the entire range into a Variant array, process in memory, then write results back

Loading data into a Variant array with a single range read and writing back once dramatically reduces the number of cell interactions and speeds up processing.

You are building a report that emails a different Excel worksheet as a PDF to each regional manager. Which VBA approach best automates this?

Answer: Use Sheet.ExportAsFixedFormat with xlTypePDF and Outlook Automation to create and send emails in a loop

ExportAsFixedFormat outputs the sheet as PDF and Outlook Automation via CreateObject allows programmatic email creation and sending.

A macro needs to import data from a closed workbook without opening it in the Excel UI. What is the most reliable VBA technique?

Answer: Both B and C are reliable approaches

Workbooks.Open is straightforward and reliable, while ADO can read data without a visible open—both are valid and commonly used.

An HR dashboard macro runs correctly on the developer's machine but throws 'Subscript out of range' on other users' machines. What is the most likely cause?

Answer: The code references a sheet by index (e.g., Sheets(3)) but the sheet order differs on other machines

Referencing sheets by index number is fragile because sheet order can differ between workbooks; use sheet CodeName or name string instead.

You want a VBA procedure to consolidate data from 12 monthly workbooks into a single summary sheet. Which method efficiently appends each file's data without duplicating headers?

Answer: For each file, find the last row of the destination sheet, then copy only data rows (skipping the header) and paste starting at that row

Dynamically finding the last row of the destination and skipping source headers ensures clean appending without header duplication.

A sales team's workbook uses a UserForm with a ListBox to let users select multiple products. How should the VBA code retrieve all selected items from a multi-select ListBox?

Answer: Loop through ListBox.ListCount and check ListBox.Selected(i) for each index

For multi-select ListBoxes, you must iterate through each item index and test the Selected property to determine which items are chosen.

A compliance team runs a macro each Monday to archive last week's data. They need the macro to automatically determine the correct date range without manual input. Which VBA approach is appropriate?

Answer: Use Date and Weekday() to calculate the start (last Monday) and end (last Sunday) dates at runtime

Using Date combined with Weekday() lets VBA dynamically calculate the correct previous week range regardless of when the macro runs.