## 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:

## Appendix – sbMonteCarloSimulation Code

``````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
``````