Excel VBA Risk Assessment & Management 3 — Questions and Answers
Question 1: Which Excel VBA function returns the standard deviation of a range of simulated risk outcomes stored in an array?
- WorksheetFunction.StDev(arr) (Correct answer)
- VBA.StdDev(arr)
- Application.StDevP(arr)
- Math.StdDev(arr)
Correct answer: WorksheetFunction.StDev(arr)
WorksheetFunction.StDev exposes Excel's STDEV function to VBA, making it straightforward to compute sample standard deviation on an array or range.
Question 2: A macro generates a risk heat map by coloring cells. After running, colleagues report the colors persist even after underlying data changes. The best fix is to:
- Use conditional formatting rules instead of VBA color assignments (Correct answer)
- Delete and recreate the sheet each run
- Add a button to manually clear colors
- Use .Interior.Color = xlNone
Correct answer: Use conditional formatting rules instead of VBA color assignments
Conditional formatting updates automatically when cell values change, whereas VBA-applied colors are static until the macro re-runs.
Question 3: To protect sensitive risk model formulas from accidental editing while still allowing VBA to write results, you should:
- Protect the sheet and pass the password in VBA using Protect/Unprotect (Correct answer)
- Set all cells to Locked = True without sheet protection
- Use Application.WorkbookBeforeSave event to re-lock
- Mark the workbook as Final
Correct answer: Protect the sheet and pass the password in VBA using Protect/Unprotect
Calling ActiveSheet.Unprotect with the password before writing, then re-protecting afterward, lets VBA update output cells while blocking manual edits.
Question 4: Which data type is most appropriate in VBA for storing a probability value such as 0.0235 in a risk calculation?
- Integer
- Double (Correct answer)
- Boolean
- Long
Correct answer: Double
Double provides 64-bit floating-point precision, necessary for accurate probability arithmetic where rounding errors could compound across many calculations.
Question 5: A VBA macro that performs scenario analysis must pause and display interim results to the user before continuing. The best approach is to use:
- MsgBox to show results and wait for user confirmation (Correct answer)
- DoEvents in a tight loop
- Application.Wait 5000
- Debug.Print to the Immediate Window
Correct answer: MsgBox to show results and wait for user confirmation
MsgBox halts macro execution and presents results, resuming only after the user dismisses it, making it ideal for interactive scenario review.
Question 6: When importing external risk data via VBA using ADODB, which step is critical before releasing the connection to prevent memory leaks?
- Set rs = Nothing and conn.Close (Correct answer)
- Call Application.Quit
- Set conn = New ADODB.Connection again
- Use On Error Resume Next to suppress errors
Correct answer: Set rs = Nothing and conn.Close
Closing the recordset and connection, then setting both to Nothing, releases COM object references and prevents memory leaks.
Question 7: A risk tolerance threshold is stored in a VBA Const at the top of a module. A compliance requirement changes the threshold. What is the main disadvantage of using Const for this value?
- Constants cannot store decimal values
- Changing the threshold requires editing and re-deploying VBA code (Correct answer)
- Constants are visible to all users
- Constants slow down arithmetic operations
Correct answer: Changing the threshold requires editing and re-deploying VBA code
A Const is compiled into the code, so any change requires a developer to modify source, whereas a named cell or configuration sheet allows business-user updates without code changes.
Excel VBA Risk Assessment & Management 3
Which Excel VBA function returns the standard deviation of a range of simulated risk outcomes stored in an array?