Excel VBA Regulatory Frameworks & Compliance 2 — Questions and Answers
Question 1: Under SOX Section 302, what VBA practice best supports the CEO/CFO certification requirement for financial controls?
- Storing all macros in personal.xlsb for easy access
- Maintaining a VBA audit log that records every financial cell modification with timestamp and user (Correct answer)
- Using AutoOpen macros to auto-populate financial summaries
- Disabling worksheet protection to allow faster data entry
Correct answer: Maintaining a VBA audit log that records every financial cell modification with timestamp and user
SOX Section 302 requires executives to certify internal controls, so VBA audit logs documenting who changed what financial data and when directly support that certification.
Question 2: A VBA macro processes HIPAA-covered patient data. Which approach complies with the Minimum Necessary Standard?
- Load the entire patient database into an array for faster processing
- Request only the specific fields needed for the current operation (Correct answer)
- Cache all patient records in a hidden worksheet for reuse
- Copy the full dataset to a local drive before processing
Correct answer: Request only the specific fields needed for the current operation
HIPAA's Minimum Necessary Standard requires accessing only the PHI fields actually needed for the specific task, not entire records.
Question 3: Which VBA technique helps enforce PCI DSS Requirement 3.4 regarding rendering Primary Account Numbers (PANs) unreadable?
- Displaying PANs in a light gray font color
- Using VBA to mask PANs by showing only the last four digits in the cell (Correct answer)
- Storing PANs in a password-protected named range
- Converting PANs to text format before saving
Correct answer: Using VBA to mask PANs by showing only the last four digits in the cell
PCI DSS Requirement 3.4 mandates rendering PANs unreadable wherever stored; VBA masking to show only the last four digits implements this standard.
Question 4: A GDPR compliance officer asks you to implement a 'right to erasure' workflow in Excel VBA. What should the macro do?
- Clear only the Name column for the requested subject
- Locate all rows referencing the data subject's ID and permanently delete their personal data across all worksheets (Correct answer)
- Hide rows containing the data subject's information
- Replace the subject's name with 'DELETED' as a placeholder
Correct answer: Locate all rows referencing the data subject's ID and permanently delete their personal data across all worksheets
GDPR's right to erasure (Article 17) requires complete removal of a data subject's personal data from all locations, not partial deletion or obfuscation.
Question 5: When building a VBA solution for a FINRA-regulated broker-dealer, which record retention practice is most compliant?
- Deleting workbook versions older than 30 days to save disk space
- Using VBA to enforce read-only status on finalized reports and log any export or print action (Correct answer)
- Allowing traders to overwrite historical trade records directly in Excel
- Storing trade confirmations only in the workbook's Comment fields
Correct answer: Using VBA to enforce read-only status on finalized reports and log any export or print action
FINRA Rule 4511 requires broker-dealers to preserve records in a non-alterable format; enforcing read-only status and logging access supports this requirement.
Question 6: A VBA macro must generate reports for FDA 21 CFR Part 11 compliance. Which feature is mandatory?
- Using Excel's built-in spell checker before saving
- Capturing an electronic signature with signer identity and timestamp before finalizing the report (Correct answer)
- Saving the file in CSV format for simplicity
- Enabling AutoSave on OneDrive
Correct answer: Capturing an electronic signature with signer identity and timestamp before finalizing the report
FDA 21 CFR Part 11 requires electronic records to include electronic signatures with the signer's identity and the date/time of signing.
Question 7: Which VBA error-handling pattern is most appropriate in a compliance-critical payroll macro to ensure no silent data corruption occurs?
- On Error Resume Next throughout the entire macro
- On Error GoTo ErrorHandler with logging of the error description, line number, and rollback of any partial changes (Correct answer)
- On Error GoTo 0 without any handler
- Wrapping all code in a single Try block that shows a generic MsgBox
Correct answer: On Error GoTo ErrorHandler with logging of the error description, line number, and rollback of any partial changes
Compliance-critical macros must log errors with full detail and roll back partial changes to prevent undetected data corruption in regulated records.
Excel VBA Regulatory Frameworks & Compliance 2
Under SOX Section 302, what VBA practice best supports the CEO/CFO certification requirement for financial controls?