Excel VBA Risk Assessment & Management 3

Question 1

Which Excel VBA function returns the standard deviation of a range of simulated risk outcomes stored in an array?

Accepted Answer

WorksheetFunction.StDev(arr)

Answer

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:

Accepted Answer

Use conditional formatting rules instead of VBA color assignments

Answer

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:

Accepted Answer

Protect the sheet and pass the password in VBA using Protect/Unprotect

Answer

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?

Accepted Answer

Double

Answer

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:

Accepted Answer

MsgBox to show results and wait for user confirmation

Answer

MsgBox halts macro execution and presents results, resuming only after the user dismisses it, making it ideal for interactive scenario review.

Excel VBA Practice Test

Excel VBA Practice Test

Excel VBA Risk Assessment & Management 3