Case Studies & Practical Application Flashcards
7 cards from real Excel VBA practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 7 Case Studies & Practical Application flashcards as text
A VBA macro that generates invoices must fill a Word template with data from Excel. Which approach enables this cross-application automation?
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.
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?
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.
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?
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.
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?
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.
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?
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.
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?
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.
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?
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.