Excel VBA Case Studies & Practical Application 2 — Questions and Answers
Question 1: 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?
- Use a For Each loop over the range directly
- Read the entire range into a Variant array, process in memory, then write results back (Correct answer)
- Use Selection.Find in a loop
- Call a worksheet function with VLOOKUP inside the loop
Correct 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.
Question 2: You are building a report that emails a different Excel worksheet as a PDF to each regional manager. Which VBA approach best automates this?
- Manually export PDFs and attach them to Outlook drafts
- Use Sheet.ExportAsFixedFormat with xlTypePDF and Outlook Automation to create and send emails in a loop (Correct answer)
- Copy each sheet to a new workbook and save as CSV
- Use the SendKeys method to trigger File > Save As
Correct 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.
Question 3: A macro needs to import data from a closed workbook without opening it in the Excel UI. What is the most reliable VBA technique?
- Use a formula like ='C:\path\[file.xlsx]Sheet1'!A1 inserted by VBA
- Open the workbook with Workbooks.Open, read the data, then close it with SaveChanges:=False
- Use ADO (ActiveX Data Objects) to query the file as a database
- Both B and C are reliable approaches (Correct answer)
Correct 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.
Question 4: 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?
- The macro uses late binding for all COM objects
- The code references a sheet by index (e.g., Sheets(3)) but the sheet order differs on other machines (Correct answer)
- The code uses Application.ScreenUpdating = False
- Option Explicit is missing from the module
Correct 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.
Question 5: 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?
- Copy the entire used range from each file and Paste Special > Values
- 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 (Correct answer)
- Use a VLOOKUP formula to pull data from all files
- Record a macro of a manual consolidation and run it 12 times
Correct 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.
Question 6: 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?
- Read ListBox.Value, which returns a comma-separated string of selections
- Loop through ListBox.ListCount and check ListBox.Selected(i) for each index (Correct answer)
- Use ListBox.MultiSelect property to get the selections directly
- Call ListBox.GetSelected() method
Correct 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.
Question 7: 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?
- Hardcode Monday and Sunday dates in the code
- Use Date and Weekday() to calculate the start (last Monday) and end (last Sunday) dates at runtime (Correct answer)
- Prompt the user with InputBox to enter the date range each week
- Use Now() only and ignore the specific day of the week
Correct 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.
Excel VBA Case Studies & Practical Application 2
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?