Excel VBA Case Studies & Practical Application 5 — Questions and Answers
Question 1: A VBA macro that generates invoices must fill a Word template with data from Excel. Which approach enables this cross-application automation?
- Copy Excel cells and use SendKeys to paste into Word
- Use Word Automation via CreateObject('Word.Application') to open the template and use Find/Replace or bookmarks to substitute data fields (Correct answer)
- Export Excel data to CSV and import it into Word manually
- Use Excel's Mail Merge feature triggered by a VBA macro in Word
Correct answer: Use Word Automation via CreateObject('Word.Application') to open the template and use Find/Replace or bookmarks to substitute data fields
Word Automation via COM lets VBA control a Word instance, open a template, and programmatically replace bookmarks or placeholder text with Excel data.
Question 2: A quality control macro must validate that every cell in a column contains a valid US phone number format before importing. Which VBA tool is best for pattern matching?
- Use InStr to check for dashes manually
- Use the VBScript RegExp object (CreateObject('VBScript.RegExp')) with a regex pattern to test each cell value (Correct answer)
- Use the Len() function to check that each value is exactly 12 characters
- Use a worksheet Data Validation rule applied by VBA
Correct answer: Use the VBScript RegExp object (CreateObject('VBScript.RegExp')) with a regex pattern to test each cell value
VBScript.RegExp provides full regular expression support in VBA, enabling flexible and accurate pattern validation for formats like phone numbers.
Question 3: A developer needs to create a VBA class module that models a 'Product' with properties (Name, Price, Stock) and a method (ApplyDiscount). Which statement is correct about this implementation?
- VBA class modules cannot have methods, only properties
- Properties are created with Property Get/Let procedures and methods are standard Sub or Function procedures inside the class module (Correct answer)
- All properties in a VBA class must be Public variables, not Property procedures
- VBA classes require an Interface keyword to define properties
Correct answer: Properties are created with Property Get/Let procedures and methods are standard Sub or Function procedures inside the class module
VBA class modules use Property Get/Let/Set for encapsulated properties and standard Sub/Function procedures for methods, fully supporting OOP encapsulation.
Question 4: A macro that imports CSV files stops working when a user's CSV has a semicolon delimiter instead of a comma. What is the most robust fix?
- Hardcode comma as the delimiter and ask users to resave their files
- Detect the delimiter by reading the first line of the file and testing which character appears most frequently between known positions, or prompt the user to specify it (Correct answer)
- Always open CSVs with Workbooks.Open and let Excel auto-detect the delimiter
- Use Split(line, ',') and ignore delimiter differences
Correct answer: Detect the delimiter by reading the first line of the file and testing which character appears most frequently between known positions, or prompt the user to specify it
Auto-detecting the delimiter by inspecting the file's first row, or prompting the user, makes the import robust to regional delimiter differences.
Question 5: A VBA solution needs to log errors with timestamps to a text file for troubleshooting. What is the correct approach to append log entries without overwriting previous ones?
- Use Open filename For Output As #1, which appends by default
- Use Open filename For Append As #1 to open in append mode and write entries with Print #1 (Correct answer)
- Use FileCopy to duplicate the log before each write
- Use the FileSystemObject's OpenTextFile with ForWriting mode
Correct answer: Use Open filename For Append As #1 to open in append mode and write entries with Print #1
Opening a file For Append positions the write pointer at the end, so new entries are added without overwriting existing content.
Question 6: A VBA macro creates a new worksheet, populates it, and then tries to delete it if it already exists before recreating it. What must be done before deleting the sheet to prevent an Excel confirmation dialog from interrupting the macro?
- Set Application.ScreenUpdating = False before the delete
- Set Application.DisplayAlerts = False before the delete, then restore it to True afterward (Correct answer)
- Call Sheet.Protect before deleting to suppress dialogs
- Use DoEvents to clear pending dialogs before the delete
Correct answer: Set Application.DisplayAlerts = False before the delete, then restore it to True afterward
Excel shows a confirmation dialog before deleting sheets; setting Application.DisplayAlerts = False suppresses it and allows the delete to proceed silently.
Question 7: A finance team's end-of-month macro takes 45 minutes to run. Profiling shows most time is spent in a nested loop that applies cell-by-cell formatting. What is the single most impactful refactor?
- Replace the loop with a single call to Range.AutoFormat
- Replace cell-by-cell formatting with a single conditional formatting rule applied to the entire range using VBA, or apply formatting to the full range object in one call (Correct answer)
- Split the macro into two smaller macros run sequentially
- Use DoEvents inside the loop to free up the processor
Correct answer: Replace cell-by-cell formatting with a single conditional formatting rule applied to the entire range using VBA, or apply formatting to the full range object in one call
Applying formatting to an entire Range object in one call (e.g., Range.Interior.Color = ...) is orders of magnitude faster than setting properties on individual cells in a loop.
Excel VBA Case Studies & Practical Application 5
A VBA macro that generates invoices must fill a Word template with data from Excel.
Which approach enables this cross-application automation?