We help IT Professionals succeed at work.

# Monte Carlo simulation in Excel VBA

on
Medium Priority
3,822 Views
Hello,
I need help creating a Monte Carlo simulation model in Excel. It's probably not as much of the Monte Carlo specific question per se but a question of approach to looking up corresponding values in probability table, suming and storing the results (in a temporary worksheet? or array? not sure what's best and how to achieve it), and moving on to the next simulation. Number of simulations is a user controlled input.  I have created a skeleton of the model with some very high level VBA coding that for now that asks for user input for number of simulations,  starts a blank "test" loop, and shows a progress bar. I need help/direction/guidance on the rest. My VBA knowledge would be best described as advanced beginner.

Thanks,
MonteCarlo.xlsm
Comment
Watch Question

## View Solution Only

Developer & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:

Commented:
>a question of approach to looking up corresponding values in probability table, suming and storing the results

this sounds like a not-so-simple formula

Are you familiar with VLOOKUP?  It takes an input (typically from a cell) and searches a column for a specific value and returns a corresponding value in an adjacent cell.

e.g. =VLOOKUP(A1, B:C, 2)
this says to take what's in cell A1 and search down column B and if found, return what's in the 2nd column of the group called B:C (i.e. column C)
if not found, you have a 4th option to either generate an error or return the closest value, which would assume that the values are sorted.

If you apply that to a collection of cells (e.g. copy from A1 to  A2: A99) then you can get a collection of values that you can then SUM (which are automatically stored in the cells where you have the formulas.

Does this help?

Commented:
rspahitz,
I ended up using sumproduct lookup vs vlookup. The code works well so far, where I am stuck is storing the results of each simulation in an array, since I am not well versed in arrays. I am trying to create a two dimensional array where 1st variable is the number of simulation, and second is the sum of the results from respective simulation:
Sub Simulation()

Dim i As Long 'Number of simulations
Dim ii As Long 'Number of random var in each simulation for Option 1
Dim k As Long
Dim sum1 As Long
Dim MySims As Long 'Actual number of simulation the model will run
Dim PercentDone As Single
Dim SimsEntered As Variant 'User entered number of simulations
Dim array1() As Long ' Array for storing simulation results for Option 1 Whats the best way to declare it ?????

SimsEntered = InputBox("Enter number of Simulations")
If SimsEntered = "" Or SimsEntered <= 0 Or IsNumeric(SimsEntered) = False Then
MsgBox "The number of repetitions must be an integer greater than zero."

Exit Sub
End If

MySims = CInt(SimsEntered)

k = Worksheets("Simulation").Range("C5").Value 'getting number of random variables from user input cell
'need error check if <= 0

For i = 1 To MySims 'Loop for number of simulation

For ii = 1 To k 'Loop for number of random variable to be generated per each simulation

Worksheets("Results").Cells(ii, 1) = "=rand()" 'generate random variables
Worksheets("Results").Cells(ii, 1).Value = Worksheets("Results").Cells(ii, 1).Value 'Leave value only in the cell

'Look up average amount
Worksheets("Results").Cells(ii, 2).FormulaR1C1 = "=sumproduct((rngCPL <= RC[-1]) * (rngCPH >= RC[-1]) * rngAvAmt)" 'same here

Next ii

sum1 = Application.Sum(Range(Cells(1, 2), Cells(k, 2))) 'sum all results in Column 2

MsgBox sum1 'show total for testing purposes

'Here need to store each sum1 in the two dimensional array1( , ) where first value is the number of simulation,
'and second value is the value of sum1
'This array will need to populate a table summarizing the simulations on a different sheet (output sheet)

'Need to clear columns 1, 2 on "Results" sheet before next simulation runs

'Track progress
PercentDone = i / MySims

'Update status bar
Application.StatusBar = "Progress: " & Format(PercentDone, "0%") & " Complete"

Next i

End Sub

Commented:
>
Dim array1() As Long ' Array for storing simulation results for Option 1 Whats the best way to declare it ?????
<

This is fine; this creates a dynamic array that can change dimensions later. I usually "initialize it right away like this:
ReDim array1(1,0)' this creates a 2-dimensional array of array1(0,0) and array1(1,0) since arrays are zero-based.

>
'Here need to store each sum1 in the two dimensional array1( , ) where first value is the number of simulation,
'and second value is the value of sum1
<

Since you'll only have paired values, one of the dimensions will be 2 and the other will grow (e.g. array1(0,0) array1(1,0) array1(0,1) array1(1,1) array1(0,2) array1(1,2) array1(0,3) array1(1,3)...)

' add a current-item counter somewhere; can probably use the "i" variable in this case

array1(0, i) = i'  <= not sure if this is what you mean (*)
array1(1, i) = sum1
ReDim Preserve array1(1, ubound(array1(2))+1)

(*) if this is what you want, you don't need a multidimension array because the index of the array entry is already known.  For example, for the 5th sum1, you already know it's item #5 (index is one less since it's zero-based)

--
>
'This array will need to populate a table summarizing the simulations on a different sheet (output sheet)

'Need to clear columns 1, 2 on "Results" sheet before next simulation runs
<

Not quite sure about what you mean here.  We can address that next if the array parts work as you'd like.

Commented:
My though behind 2 dimensional array was that i wanted to "paste" first dimension which is a simulation counter into the first column of a table with the simulation results, and the second dimension which are the actual results (sum1s) into a second column of the table. The reason behind using the table is that I could easily link other formulas in the spreadsheet via structured reference.

So array1 should be something like array1 ( number of simulations = MySims , number of results (same as number of simulations))
then store 1 through MySims in first dimension and all corresponding generated sum1s in second dimension. After loop is over "paste" array into the existing table.
MonteCarlo.xlsm

Commented:
Maybe an example would help.

So if you have 3 simulations (with results of say 7,2,6,4 in the first simulation), what do you want in the array? (Or give me a better set of numbers that make sense.)

array(0,0)=?
array(0,1)=?
array(0,2)=?
array(1,0)=?
etc.?

Commented:
rspahitz,
So what model does in each simulation is summing (sum1 variable) the looked up \$ amounts per probability table based on generated random variables. So simulation results would be something like this:

Simulation 1 = array(1, \$35,234,345)
Simulation 2 = array(2, \$34,345,678) through
Simulation MySim = array(MySim, \$xxx,xxx,xxx)

etc how many simulations user defined (for practical reason I will limit it to 1,000)

My idea was then to just use that array to populte a table with results on "Simulation" sheet.

Commented:
Since you can't have \$ amounts as index values in arrays, I assume you mean this:

SimluationArray(1) = \$35,234,345
SimulationArray(2) = \$34,345,678
SimlulationArray(n) = \$xxx,xxx,xxx

This is a 1-dimensional array.  Think of is as a sheet in Excel.  All values are in column 1; row 1 has \$35K, row 2 has \$34K, row 3 has \$???K, ... row n has \$xxxK

Commented:
yes, that's accurate!

Commented:

First, add the ReDim line after getting the number of iterations to run:
``````...
MySims = CInt(SimsEntered)
ReDim array1(MySims)
...
``````

Then update the value after your message box:
``````...            'MsgBox sum1 'show total for testing purposes

array1(i) = sum1
...
``````

then at the very end add this, which will add the result values onto the Simulation sheet's list:
``````...
Sheets("Simulation").Activate
For i = 1 To MySims
Cells(i + 14, 2).Value = i
Cells(i + 14, 3).Value = array1(i)
Next
End Sub
``````
Commented:
The complete sub that seems to work for me is here (cleaned up a bit):
``````Sub Simulation()
Dim i As Long 'Number of simulations
Dim ii As Long 'Number of random var in each simulation for Option 1
Dim k As Long
Dim sum1 As Long
Dim MySims As Long 'Actual number of simulation the model will run
Dim PercentDone As Single
Dim SimsEntered As Variant 'User entered number of simulations
Dim array1() As Long 'Array for storing simulation results for Option 1  ?????

'need to clear the results of prior run from the table first
With Sheet2.ListObjects("Table4")
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
End With

SimsEntered = InputBox("Enter number of Simulations")
If SimsEntered = "" Or SimsEntered <= 0 Or IsNumeric(SimsEntered) = False Then
MsgBox "The number of repetitions must be an integer greater than zero."
Exit Sub
End If

MySims = CInt(SimsEntered)
ReDim array1(MySims)

k = Worksheets("Simulation").Range("C12").Value 'getting number of random variables from user input cell
'need error check if <= 0

Worksheets("Results").Activate

For i = 1 To MySims 'Loop for number of simulation

For ii = 1 To k 'Loop for number of random variable to be generated per each simulation
Worksheets("Results").Cells(ii, 1) = "=rand()" 'generate random variables
Worksheets("Results").Cells(ii, 1).Value = Worksheets("Results").Cells(ii, 1).Value 'Leave value only in the cell

'Look up average amount
Worksheets("Results").Cells(ii, 2).FormulaR1C1 = "=sumproduct((rngCPL <= RC[-1]) * (rngCPH >= RC[-1]) * rngAvAmt)" 'same here
Next ii

sum1 = Application.Sum(Range(Cells(1, 2), Cells(k, 2))) 'sum all results in Column 2

'MsgBox sum1 'show total for testing purposes
array1(i) = sum1

Worksheets("Results").Range("A:B").Clear 'clear the results page to get ready for next simulation

'Track progress
PercentDone = i / MySims

'Update status bar
Application.StatusBar = "Progress: " & Format(PercentDone, "0%") & " Complete"

Next i

Sheets("Simulation").Activate
For i = 1 To MySims
Cells(i + 14, 2).Value = i
Cells(i + 14, 3).Value = array1(i)
Next
End Sub
``````

Commented:
rspahitz,
thank you! I don't know why arrays are so intimidating, it was much simpler than I imagined. Last pair of questions
- are there any tricks to speed up such type of a code? Only thing i could think of is turning off SreenUpdating in the beginning and turning it back on at the end.
- Is there any point to emptying the array at the end with Erase array?

Thanks!!!

Commented: