This guide discusses using the RiskAMP Add-in with VBA. Using VBA, it's possible to execute any code or function at each iteration of a simulation.
The examples in this guide use the RiskAMP Monte Carlo add-in; if you don't have the add-in already, you can download a free trial version from our download page.
The RiskAMP Add-in is a compiled, native C++ library. To call functions in the add-in from VBA requires an unusual syntax – you need to use the Excel VBA function Application.Run. The first argument is the name of the function, and any subsequent arguments are passed to the function.
For example, to generate a normally-distributed random value with mean of 100 and standard deviation 10, the syntax would be
r = Application.Run( "NormalValue", 100, 10 )
To simplify this a bit, we've created a function library that you can import into your own projects which provides wrapper functions for most of the common RiskAMP functions. With the function library, the code looks like this:
r = RiskAMP_NormalValue( 100, 10 )
Download the function library (you may need to right-click this link and select "save as…"):
To import the function library into your project, open the VBA window in Excel (Tools -> Macro -> Visual Basic Editor or Alt+F11). Right-click the left-hand window (with the tree of worksheets) and select "Import File…", then locate the function library on your disk.
Running a simulation from VBA requires just one call to the simulation function. If you have the function library installed in your project, to run a simulation use this code in your VBA method:
RiskAMP_RunSimulation 1000
The argument (1000) is the number of trials to use in the simulation. The above function will run a simulation as if you had started it from the Excel menu, but will not allow you to execute VBA code during the simulation. To do that, use the stepped simulation functions, described in the next section.
A stepped simulation uses functions that explicitly tell the add-in when to run each iteration. This allows you to write any VBA code in between iterations, to execute custom functions, access or store data, or anything else that's possible with VBA.
There are three functions used in running a simulation: you can think of these as start, stop, and next trial. This code should execute in a loop for the desired number of trials.
Even though there's a loop in VBA, the Add-in needs to know the number of trials. It's important that both the loop and the start function use the same number of trials, or the simulation may never finish. It can be useful to use a variable to avoid any errors here.
The following code executes a stepped simulation with 250 trials. At each iteration, the code does some calculation on the spreadsheet (this is a simple example, but any code or function call could be used).
This example assumes that the function library is imported (see above).
' This function runs a stepped simulation with 250 ' trials. At each trial, it performs a calculation ' based on some values in the spreadsheet. Private Sub MyFunction() ' this variable holds the number of trials in the simulation NumberOfTrials = 250 ' start the simulation with the desired number of trials RiskAMP_BeginSteppedSimulation (NumberOfTrials) ' use a loop to run the simulation for the same number of trials For i = 1 To NumberOfTrials ' any VBA code can be run here; in this example, we do ' a simple calculation on some spreadsheet values. x = Range("B2").Value y = Range("C2").Value Range("B2").Value = (x + y) + 1 ' this call advances the simulation by one step RiskAMP_IterateSimulationStep Next i ' when the loop is complete, finish the simulation RiskAMP_FinishSteppedSimulation End Sub