Excel VBA Case Studies & Practical Application 5

Question 1

A VBA macro that generates invoices must fill a Word template with data from Excel. Which approach enables this cross-application automation?

Accepted Answer

Use Word Automation via CreateObject('Word.Application') to open the template and use Find/Replace or bookmarks to substitute data fields

Answer

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?

Accepted Answer

Use the VBScript RegExp object (CreateObject('VBScript.RegExp')) with a regex pattern to test each cell value

Answer

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?

Accepted Answer

Properties are created with Property Get/Let procedures and methods are standard Sub or Function procedures inside the class module

Answer

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?

Accepted 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

Answer

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?

Accepted Answer

Use Open filename For Append As #1 to open in append mode and write entries with Print #1

Answer

Opening a file For Append positions the write pointer at the end, so new entries are added without overwriting existing content.

Excel VBA Practice Test

Excel VBA Practice Test

Excel VBA Case Studies & Practical Application 5