Excel VBA Regulatory Frameworks & Compliance 3 — Questions and Answers
Question 1: Under GLBA (Gramm-Leach-Bliley Act), a VBA workbook stores customer financial information. Which control is most appropriate?
- Sharing the workbook on a public SharePoint site for easy access
- Encrypting the workbook with a strong password and restricting access via VBA-enforced user authentication (Correct answer)
- Using cell formatting to hide sensitive columns by default
- Saving the workbook on the user's local desktop
Correct answer: Encrypting the workbook with a strong password and restricting access via VBA-enforced user authentication
GLBA requires financial institutions to protect customer financial information through administrative, technical, and physical safeguards, including encryption and access controls.
Question 2: A VBA macro processes export-controlled technical data under ITAR. What access control should be implemented?
- Allow any authenticated network user to open the workbook
- Check the current Windows username at workbook open and terminate if the user is not on an approved US-person list (Correct answer)
- Use a simple password that all employees share
- Restrict access by file extension only
Correct answer: Check the current Windows username at workbook open and terminate if the user is not on an approved US-person list
ITAR restricts controlled technical data to US persons only; VBA can enforce this by checking user identity against an approved list at runtime.
Question 3: Which VBA approach supports ISO 27001 Annex A control A.12.4.1 (Event Logging) for a spreadsheet used in an ISMS?
- Printing a paper log of all changes at end of day
- Writing a timestamped entry to a secure log worksheet for every Workbook_Open, Save, and Close event (Correct answer)
- Using Excel's Track Changes feature only when auditors are present
- Logging events only when errors occur
Correct answer: Writing a timestamped entry to a secure log worksheet for every Workbook_Open, Save, and Close event
ISO 27001 A.12.4.1 requires event logging of user activities and security events; VBA event handlers that log every open, save, and close action fulfill this control.
Question 4: A VBA solution handles data subject to CCPA. A California resident submits an opt-out request. What must the macro do?
- Flag the record with a note for review next quarter
- Immediately cease selling or sharing the resident's personal information and update their record with an opt-out timestamp (Correct answer)
- Remove the resident's name but retain all other data fields
- Send an automated email acknowledging the request without making data changes
Correct answer: Immediately cease selling or sharing the resident's personal information and update their record with an opt-out timestamp
CCPA gives California residents the right to opt out of the sale of their personal information, requiring immediate action to stop data sharing and record the opt-out.
Question 5: When a VBA macro must comply with NIST SP 800-53 control AU-9 (Protection of Audit Information), which technique is appropriate?
- Storing audit logs in a regular, editable worksheet tab
- Writing audit records to a hidden worksheet protected with an admin-only password and verifying log integrity with a hash (Correct answer)
- Using Excel comments to record audit events
- Keeping audit data only in memory during the session
Correct answer: Writing audit records to a hidden worksheet protected with an admin-only password and verifying log integrity with a hash
NIST AU-9 requires protecting audit information from unauthorized access, modification, and deletion; password-protected logs with integrity checks fulfill this control.
Question 6: A healthcare VBA application must comply with the HITECH Act's breach notification rule. Which capability should be built in?
- A macro that emails all staff when any file is saved
- Detection logic that identifies when PHI has been accessed without authorization and automatically notifies the Privacy Officer (Correct answer)
- Disabling all sharing features to prevent any data movement
- Requiring a daily password reset for all workbook users
Correct answer: Detection logic that identifies when PHI has been accessed without authorization and automatically notifies the Privacy Officer
HITECH strengthened HIPAA breach notification requirements, so VBA applications handling PHI should detect unauthorized access and trigger timely notification workflows.
Question 7: An SOX-compliant VBA macro must prevent back-dating of journal entries. Which implementation achieves this?
- Allowing users to type any date in the date column
- Comparing the user-entered date to Now() and rejecting entries dated more than the allowable posting period in the past (Correct answer)
- Using date validation that only accepts future dates
- Formatting the date column as text to prevent date manipulation
Correct answer: Comparing the user-entered date to Now() and rejecting entries dated more than the allowable posting period in the past
SOX controls require preventing fraudulent back-dating of financial entries; validating that posted dates fall within the allowable period enforces this control.
Excel VBA Regulatory Frameworks & Compliance 3
Under GLBA (Gramm-Leach-Bliley Act), a VBA workbook stores customer financial information.
Which control is most appropriate?