Excel VBA Risk Assessment & Management 2 — Questions and Answers
Question 1: In Excel VBA risk modeling, which method best prevents a Monte Carlo simulation from producing identical results on every run?
- Call Randomize before Rnd() (Correct answer)
- Use Now() as the seed
- Set VBA.Rnd = 0 at start
- Use Application.Volatile
Correct answer: Call Randomize before Rnd()
Calling Randomize (optionally with Timer) seeds the random number generator so each run produces a unique sequence.
Question 2: A VBA risk dashboard must highlight cells where a calculated probability exceeds a threshold stored in a named range called 'RiskLimit'. Which approach is most maintainable?
- Hard-code the threshold in the conditional formatting formula
- Reference the named range directly in the macro comparison (Correct answer)
- Copy the named range value into a module-level constant
- Store the threshold in a hidden worksheet cell
Correct answer: Reference the named range directly in the macro comparison
Referencing the named range directly keeps the threshold in one managed location that business users can update without touching VBA code.
Question 3: Which VBA statement correctly catches a division-by-zero error that may occur when calculating a risk ratio?
- On Error GoTo ErrHandler (Correct answer)
- Try Catch Finally
- If Err.Number <> 0 Then
- Resume Next Err.Clear
Correct answer: On Error GoTo ErrHandler
On Error GoTo ErrHandler redirects execution to a labeled error handler where division-by-zero (Err.Number 11) can be addressed.
Question 4: When building a risk scoring matrix in VBA, a nested Select Case structure is preferred over nested If/ElseIf because it:
- Executes faster at runtime
- Improves readability for multiple discrete score bands (Correct answer)
- Supports string comparisons only
- Automatically sorts the cases numerically
Correct answer: Improves readability for multiple discrete score bands
Select Case clearly maps discrete score ranges to outcomes, making the risk banding logic easier to read and audit than deeply nested If/ElseIf.
Question 5: A risk analyst wants to log every VBA-calculated risk score to a new row in a 'RiskLog' sheet automatically. Which technique avoids overwriting existing entries?
- Write to cell A1 each time
- Use Sheets("RiskLog").Cells(Rows.Count,1).End(xlUp).Offset(1,0) (Correct answer)
- Clear the sheet before each write
- Use Range("A1").Insert Shift:=xlDown
Correct answer: Use Sheets("RiskLog").Cells(Rows.Count,1).End(xlUp).Offset(1,0)
Finding the last used row and offsetting by one appends entries without disturbing existing log data.
Question 6: In a VBA-based risk tool, ScreenUpdating is set to False before a long calculation loop. What is the primary risk of forgetting to reset it to True?
- The workbook becomes read-only
- The Excel UI freezes and appears unresponsive to the user after the macro ends (Correct answer)
- Formulas stop recalculating automatically
- Event handlers are permanently disabled
Correct answer: The Excel UI freezes and appears unresponsive to the user after the macro ends
Leaving ScreenUpdating as False after the macro finishes makes Excel appear frozen because screen redraws are suppressed.
Question 7: A risk register macro uses a Public variable to track cumulative exposure across multiple subroutines. What is the key risk of this design?
- Public variables cannot store numeric values
- Any module can accidentally modify the variable, introducing hard-to-trace bugs (Correct answer)
- Public variables are cleared after each Sub call
- Excel limits Public variables to 10 per project
Correct answer: Any module can accidentally modify the variable, introducing hard-to-trace bugs
Public module-level variables are writable by any code in the project, making unintended side effects difficult to diagnose.
Excel VBA Risk Assessment & Management 2
In Excel VBA risk modeling, which method best prevents a Monte Carlo simulation from producing identical results on every run?