Excel VBA Risk Assessment & Management 5

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?

Accepted Answer

Workbook_BeforeSave

Answer

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:

Accepted Answer

Pre-filter the array to remove error values before calling Percentile

Answer

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:

Accepted Answer

Call DoEvents every 50 iterations using Mod

Answer

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?

Accepted Answer

Create an Outlook.Application object via late binding and send via its MailItem

Answer

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:

Accepted Answer

Application.OnUndo

Answer

Application.OnUndo registers a custom procedure with the Undo stack so the user can reverse the macro's destructive action.

Excel VBA Practice Test

Excel VBA Practice Test

Excel VBA Risk Assessment & Management 5