Using the RiskAMP Add-in with VBA

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.

Calling Add-in Functions From VBA

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 VBA function ExecuteExcel4Macro.

For example, to generate a normally-distributed random value, the syntax is

r = Application.ExecuteExcel4Macro("NormalValue(100,10)")

To make this easier, 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..."): RiskAMP_VBAModule.bas

To import the function library into your project, open the VBA window in Excel (Tools -> Macro -> Visual Basic Editor). 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

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( 200 )

The argument (200) 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.

Stepped Simulations

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 trial
        
        RiskAMP_IterateSimulationStep
    
    Next i

    ' when the loop is complete, finish the simulation

    RiskAMP_FinishSteppedSimulation
    
End Sub