Excel VBA Risk Assessment & Management 5 — Questions and Answers
Question 1: A VBA risk tool must prevent users from saving the workbook unless all mandatory risk fields are populated. Which event should enforce this validation?
- Workbook_BeforeSave (Correct answer)
- Workbook_Open
- Worksheet_Deactivate
- Application.OnTime
Correct answer: Workbook_BeforeSave
Workbook_BeforeSave fires before every save attempt, allowing VBA to validate fields and cancel the save by setting Cancel = True if requirements are not met.
Question 2: In a VBA-based Value-at-Risk (VaR) calculation, the Percentile worksheet function is accessed via WorksheetFunction. If the returns array contains errors, the safest approach is to:
- Wrap the call in On Error Resume Next and check Err.Number afterward
- Use WorksheetFunction.IfError as a wrapper
- Pre-filter the array to remove error values before calling Percentile (Correct answer)
- Set Application.DisplayAlerts = False before the call
Correct answer: Pre-filter the array to remove error values before calling Percentile
Filtering out error values before calling Percentile ensures the function receives clean numeric data, preventing a VBA runtime error that On Error Resume Next would silently swallow.
Question 3: A risk macro loops through 500 portfolio positions and updates a progress bar label on a UserForm. Calling DoEvents every iteration causes noticeable slowdown. The best compromise is to:
- Remove the progress bar entirely
- Call DoEvents every 50 iterations using Mod (Correct answer)
- Use Application.StatusBar for progress instead of a UserForm label
- Switch to a separate thread via CreateObject
Correct answer: Call DoEvents every 50 iterations using Mod
Updating the UI and yielding to Windows every 50 iterations (i Mod 50 = 0) reduces DoEvents overhead while keeping the interface reasonably responsive.
Question 4: Which technique allows a VBA risk model to send an automated email alert when calculated exposure exceeds a defined limit, using only built-in Office components?
- Use the Shell command to launch Outlook with mailto:
- Create an Outlook.Application object via late binding and send via its MailItem (Correct answer)
- Write to a text file and let a scheduled task handle sending
- Use Application.SendMail which sends directly via SMTP
Correct answer: Create an Outlook.Application object via late binding and send via its MailItem
Creating an Outlook.Application COM object and configuring a MailItem gives full control over recipients, subject, and body without external tools.
Question 5: A risk register macro accidentally deletes rows instead of hiding them. To add an undo checkpoint before the destructive operation, a developer should call:
- Application.OnUndo (Correct answer)
- ActiveWorkbook.SaveCopyAs before deleting
- CommandBars.ExecuteMso "Undo"
- Application.EnableEvents = False
Correct answer: Application.OnUndo
Application.OnUndo registers a custom procedure with the Undo stack so the user can reverse the macro's destructive action.
Question 6: When a VBA simulation produces a risk score distribution, which WorksheetFunction call returns the value below which 95% of simulated scores fall?
- WorksheetFunction.Percentile(arr, 0.95) (Correct answer)
- WorksheetFunction.Quartile(arr, 3)
- WorksheetFunction.Confidence(0.05, stdDev, n)
- WorksheetFunction.NormInv(0.95, mean, stdDev)
Correct answer: WorksheetFunction.Percentile(arr, 0.95)
WorksheetFunction.Percentile with k=0.95 returns the 95th percentile, which is the value below which 95% of the simulated outcomes fall.
Question 7: A compliance-sensitive risk workbook must log every macro execution with a timestamp to an audit trail sheet. To ensure the log is written even if the macro errors out, the logging call should be placed in:
- The beginning of the Sub only
- Both the main flow and the error handler's Exit Sub path (Correct answer)
- The Workbook_BeforeClose event exclusively
- A separate workbook that monitors this one
Correct answer: Both the main flow and the error handler's Exit Sub path
Placing the audit log write in both the normal exit path and the error handler guarantees a record is created regardless of whether the macro succeeded or failed.
Excel VBA Risk Assessment & Management 5
A VBA risk tool must prevent users from saving the workbook unless all mandatory risk fields are populated.
Which event should enforce this validation?