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.



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
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
        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("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

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
End Sub

Please read my Disclaimer.

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