Excel VBA Risk Assessment & Management 4 — Questions and Answers
Question 1: In VBA-driven risk reporting, which event is most appropriate for automatically refreshing a risk summary table whenever the source data worksheet is activated?
- Workbook_Open
- Worksheet_Activate (Correct answer)
- Worksheet_Change
- Application_SheetCalculate
Correct answer: Worksheet_Activate
Worksheet_Activate fires each time the user switches to that sheet, ensuring the risk summary reflects the latest data on every view.
Question 2: A risk analyst needs to run 10,000 simulation iterations in VBA without freezing Excel. Which technique best maintains UI responsiveness during the loop?
- Call DoEvents periodically inside the loop (Correct answer)
- Use Application.Wait between iterations
- Disable all event handlers for the loop duration
- Run the loop in a separate workbook
Correct answer: Call DoEvents periodically inside the loop
Calling DoEvents at intervals yields control to Windows temporarily, allowing Excel to process pending messages and appear responsive.
Question 3: Which VBA collection type is best suited for storing a dynamic list of unique risk identifiers where fast lookup by key is required?
- Array
- Collection
- Scripting.Dictionary (Correct answer)
- ArrayList
Correct answer: Scripting.Dictionary
Scripting.Dictionary provides O(1) key-based lookup and enforces unique keys, making it ideal for mapping risk IDs to their records.
Question 4: A VBA procedure writes risk assessment results to a closed workbook using early binding to Excel.Application. What must be true for this to work reliably?
- The target workbook must be password-protected
- The Excel version on both machines must match exactly
- A reference to the Microsoft Excel Object Library must be set in the VBA project (Correct answer)
- The workbook must be stored on a network drive
Correct answer: A reference to the Microsoft Excel Object Library must be set in the VBA project
Early binding requires a reference to the target type library so VBA can resolve object types at compile time, not just runtime.
Question 5: When a risk model macro needs to read configuration from a JSON file, which approach works in standard Excel VBA without third-party libraries?
- Use VBA's built-in JSON.Parse method
- Read the file as text and parse it manually with string functions (Correct answer)
- Call JavaScript's JSON.parse via Application.Run
- Import the json module with a VBA Import statement
Correct answer: Read the file as text and parse it manually with string functions
VBA has no native JSON parser, so reading the file as a text string and extracting values with InStr, Mid, or Split is the standard no-dependency approach.
Question 6: A risk assessment workbook is shared across a team using OneDrive co-authoring. A VBA macro that modifies sheet protection may fail because:
- OneDrive disables all VBA macros
- Co-authoring mode restricts certain structural operations like protecting sheets (Correct answer)
- VBA cannot run on files stored in the cloud
- Protect() requires administrator privileges in co-authoring mode
Correct answer: Co-authoring mode restricts certain structural operations like protecting sheets
Co-authoring imposes restrictions on operations such as sheet protection that could conflict with simultaneous edits by other users.
Question 7: To ensure a risk scoring formula recalculates only when relevant inputs change—not on every worksheet recalculation—a VBA UDF should avoid:
- Using WorksheetFunction calls inside the UDF
- Declaring the function as Public
- Calling Application.Volatile without necessity (Correct answer)
- Returning a Double data type
Correct answer: Calling Application.Volatile without necessity
Application.Volatile marks a UDF to recalculate on every worksheet change, adding overhead; omitting it allows Excel to recalculate only when the function's direct inputs change.
Excel VBA Risk Assessment & Management 4
In VBA-driven risk reporting, which event is most appropriate for automatically refreshing a risk summary table whenever the source data worksheet is activated?