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.

sbmontecarlosimulation

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  20-Nov-2022 PB V0.1
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:E").ClearContents
Range("D1:E1").FormulaArray = Array("3 showed up after this many throws", _
    "How often")
For i = 1 To UBound(lResult)
    Cells(i + 1, 4) = i
    Cells(i + 1, 5) = lResult(i)
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

Please read my Disclaimer.

sbmonthnumber.xlsm [30 KB Excel Excel file, open and use at your own risk]