VBA and Excel for Engineers and Scientists – Part 3

Monte Carlo Simulation Example

Download the Monte Carlo Simulation excel spreadsheet example and follow along if you wish.

So now that we have seen how to work with Ranges, let’s look at a practical if somewhat trivial example to illustrate how to use the range object and to show how to implement a very powerful analysis tool, the Monte Carlo Simulation. In the sample spreadsheet, we will take a simple bearing capacity calculation seen in Figure X below, and introduce two parameters that we wish to vary according to a normal distribution, the friction angle phi, and the bulk or total unit weight of the soil underneath the footing, gamma. For the geotechs in th bunch, you’ll notice for this simple problem I didn’t use any bearing capacity factors.

Figure 3 – Monte Carlo Example Layout

The actual Phi and Gamma values used to compute the allowable bearing capacity are in D4 and D5 respectively. For the simulation, we will use VBA to control the value in those cells based on a normal distribution. To define the statistical variation, we need mean and standard deviation values highlighted in Green in the Figure.  Before I get to the VBA code, Figure X shows you the Output tab where the results of the Monte Carlo simulation will be placed. The range is named “Output” and extends down for 1000 rows or so. If you need more rows for your simulation, just modify the named range.

Figure 4 – Monte Carlo Simulation Output

So on to the VBA code. Let’s look at the code piece by piece, and then I’ll show you the entire subroutine. I’ll omit the declarations, that should be pretty self explanatory. So first off, we need get the number of iterations to run in our model and set the Range object references to the cells we will need for our simulation. I could have easily setup the iterations to come from a cell in the spreadsheet, but I just got lazy.

Listing 6: Monte Carlo Simulation – Range Objects and Iterations

'Number of iterations for the simulation lngIterations = 1000  'Set the range objects to the named ranges Set rngPhi = Range("phi") Set rngPhiMean = Range("phi_mean") Set rngPhiSigma = Range("phi_sigma") Set rngGamma = Range("gamma") Set rngGammaMean = Range("gamma_mean") Set rngGammaSigma = Range("gamma_sigma") Set rngQall = Range("qall") Set rngOutput = Range("Output") 

Then, we need to clear out the results from any previous simulations and tell VBA to initialize the random number generator.

Listing 7: Monte Carlo Simulation – Clear Output and Randomize

'Clean out any previous runs rngOutput.ClearContents  'Initialize the random number generator Randomize  'You might need to uncomment the following 'line for more complex models, just don't 'forget to switch the calculation method 'back when you're done 'Excel.Application.Calculation = xlCalculationManual 

Next, we start the loop for each iteration. First we need to get a value for Phi and Gamma using the NORMINV Excel Worksheet Function. You might be wondering why I didn’t just program that equation into the cells for Phi and Gamma. That would have worked fine, but this way if you just want to run a specific value of Gamma and Phi, you can type it right in and not worry about messing up your formulas.

The NORMINV just returns an inverse of the cumulative normal distribution defined by a specified mean and standard deviation. We will simply pass in a random number (between 0 and 1) for the probability, and viola!

Listing 8: Monte Carlo Simulation – Loop through each Iteration

For R = 1 To lngIterations   'Phi   rngPhi.Value = Round(Excel.WorksheetFunction.NormInv(Rnd(), _                  rngPhiMean.Value, rngPhiSigma.Value), 2)   'Gamma   rngGamma.Value = Round(Excel.WorksheetFunction.NormInv(Rnd(), _                    rngGammaMean.Value, rngGammaSigma.Value), 3)   'If you set the calculation method to manual or it was   'already set at manual, you need to uncomment the next line   'Excel.Calculate 'Recalcs the formulas 

Finally, we record the actual Phi and Gamma value used and the resulting allowable bearing capacity into the Output range.

Listing 9: Monte Carlo Simulation – Store Results and Close Loop

    'Now put output (.Cells is used in the form Row, Column)     rngOutput.Cells(R, 1).Value = R     rngOutput.Cells(R, 2).Value = rngPhi.Value     rngOutput.Cells(R, 3).Value = rngGamma.Value     rngOutput.Cells(R, 4).Value = Round(rngQall.Value, 2) Next 

The Whole Enchilada

Listing 10 shows the entire subroutine for the Monte Carlo simulation example. You can download the entire spreadsheet if you like.

Listing 10: Monte Carlo Simulation Example – Full modMonteCarloExample Code

Option Explicit  Sub MonteCarloBC()     'First, declarations     Dim rngPhi As Range     Dim rngGamma As Range     Dim rngPhiMean As Range     Dim rngPhiSigma As Range     Dim rngGammaMean As Range     Dim rngGammaSigma As Range     Dim rngQall As Range     Dim rngOutput As Range     Dim R As Long 'Output Row (iteration) counter          Dim lngIterations As Long          'Number of iterations for the simulation     lngIterations = 1000          'Set the range objects to the named ranges     Set rngPhi = Range("phi")     Set rngPhiMean = Range("phi_mean")     Set rngPhiSigma = Range("phi_sigma")     Set rngGamma = Range("gamma")     Set rngGammaMean = Range("gamma_mean")     Set rngGammaSigma = Range("gamma_sigma")     Set rngQall = Range("qall")     Set rngOutput = Range("Output")          'Clean out any previous runs     rngOutput.ClearContents          'Initialize the random number generator     Randomize               'You might need to uncomment the following     'line for more complex models, just don't     'forget to switch the calculation method     'back when you're done     'Excel.Application.Calculation = xlCalculationManual          For R = 1 To lngIterations         'Phi         rngPhi.Value = Round(Excel.WorksheetFunction.NormInv(Rnd(), _                        rngPhiMean.Value, rngPhiSigma.Value), 2)         'Gamma         rngGamma.Value = Round(Excel.WorksheetFunction.NormInv(Rnd(), _                          rngGammaMean.Value, rngGammaSigma.Value), 3)         'If you set the calculation method to manual or it was         'already set at manual, you need to uncomment the next line         'Excel.Calculate 'Recalcs the formulas                  'Now put output (.Cells is used in the form Row, Column)         rngOutput.Cells(R, 1).Value = R         rngOutput.Cells(R, 2).Value = rngPhi.Value         rngOutput.Cells(R, 3).Value = rngGamma.Value         rngOutput.Cells(R, 4).Value = Round(rngQall.Value, 2)     Next          'Uncomment the following line if necessary     'Excel.Application.Calculation = xlCalculationAutomatic      End Sub