## Abstract

Creating a simple Monte Carlo simulation with Excel/VBA is fairly easy, but keep in mind some potential obstacles:

- Use the class SystemState to speed up the program by turning off ScreenUpdating and setting Calculation to xlCalculationManual.
- Inform the user about the simulation progress.
- Deal efficiently with unknown dimension increases during program execution.
- Keep in mind that in general Excel is not the best (not the fastest) simulation tool.

## Literature

From Excel 2010 onwards it seems to be ok to use Excel for Monte Carlo simulations unless it’s too slow:

(External link!) Alexei Botchkarev, Assessing Excel VBA Suitability for Monte Carlo Simulation

## Appendix – sbMonteCarloSimulation Code

Please read my Disclaimer.

```
Option Explicit
Sub Simulate()
'Creates a simple Monte Carlo simulation by counting how long
'it takes to throw a 3 with a die with 10 surfaces (likelihood
'for each to show is 1/10).
'Source (EN): http://www.sulprobil.com/sbmontecarlosimulation_en/
'Source (DE): http://www.bplumhoff.de/sbmontecarlosimulation_de/
'(C) (P) by Bernd Plumhoff 23-Nov-2022 PB V0.2
Dim i As Long
Dim lSimulations As Long
Dim lTries As Long
Dim state As SystemState
With Application.WorksheetFunction
Set state = New SystemState
Randomize
lSimulations = Range("Simulations")
ReDim lResult(1 To 1) As Long 'Error Handler will increase as needed
On Error GoTo ErrHdl
For i = 1 To lSimulations
If i Mod 10000 = 1 Then Application.StatusBar = "Simulation " & _
Format(i, "#,##0") 'Inform the user that program is still alive
lTries = 0
Do
lTries = lTries + 1
Loop Until .RandBetween(1, 10) = 3 'This is the simulation
lResult(lTries) = lResult(lTries) + 1
Next i
On Error GoTo 0
Range("D:F").ClearContents
Range("D1:F1").FormulaArray = Array("3 showed up after this many throws", _
"How often", "Theoretical Value (rounded)")
For i = 1 To UBound(lResult)
Cells(i + 1, 4) = i
Cells(i + 1, 5) = lResult(i)
lTries = .Round(lSimulations * 0.1, 0)
Cells(i + 1, 6) = lTries
lSimulations = lSimulations - lTries
Next i
End With
Exit Sub
ErrHdl:
If Err.Number = 9 Then
'Here we normally get if we breach Ubound(lResult)
If lTries > UBound(lResult) Then
'So we need to increase dimension
ReDim Preserve lResult(1 To lTries)
Resume 'Back to statement which caused error
End If
End If
'Other error - terminate
On Error GoTo 0
Resume
End Sub
```

## Download

Please read my Disclaimer.

sbmontecarlosimulation.xlsm [31 KB Excel Excel file, open and use at your own risk]