Excel Tip of the Week #166 - VBA Case Study: Creating scenarios

Hello and Happy New Year - and welcome back to the Excel Tip of the Week.  This week, we have a Developer post in which we are returning to our occasional series examining some beginner VBA coding.  In particular, we are going to look at how to use VBA to automate a process of re-running a random model many times, and recording the results.

You may want to revisit revisit TOTW #81 for a refresher on how to install and run VBA code.

The setup

We are going to need a model with a random element to it - perhaps a RAND or RANDBETWEEN that is driving a stochastic model of some kind.  We are going to use the VBA to scoop up the particular value we want, copy it in to a cell on a dedicated outputs sheet, and then rinse and repeat a given number of times.  This is a useful approach for so-called Monte Carlo analysis.

We start with our basic hygiene and a few variables:

Sub ScenarioGenerator()
Dim SampleSize As Integer
Dim TargetCell As String
Dim Counter As Integer
Dim Carrier As Variant

End Sub

The variables are here used for (in order): setting the size of the sample; noting the address of the cell to be captured; a counter for how many sample items will be recorded; and a spare variable for storing the values while they are being copied across.

We need to ask the user what cell is going to be copied, and how many times to run the scenario.  We can that by setting those variables via input boxes, which are easy to create:

SampleSize = InputBox("How many generations should be run?")
TargetCell = InputBox("Which cell result should be recorded?")

The text in the quotation marks is the prompt that will be displayed to the user.

We will be switching tabs a lot here, and to make the screen neater, we are going to turn off the updating of the screen for the duration of the macro:

Application.ScreenUpdating = False

[code goes here]

Application.ScreenUpdating = True

This will help keep things neat.

Finally, we need code for the actual copy / pasting process.  This will be done with a loop, covered in TOTW #147, but slightly differently:

For Counter = 1 To SampleSize

[process goes here]

Next Counter

Finally, we need to look at how to copy a value from a cell, and paste it into another.  We are going to do this by putting the value of our cell into the spare variable Carrier, that we made earlier:

Carrier = Range("Model!" & TargetCell).Value

Note that here we have entered the name of the origin sheet directly, as Model; you could either have this be a different name, or have the name entered along with the cell reference earlier in the code.  Now, for the paste:

Range("Output!A1").Offset(Counter - 1, 0).Value = Carrier

Again, the output sheet here has been specifically named, but this could be changed or set elsewhere.  We are also using the offset operator to move down column A in the Outputs sheet, putting each output in the next cell.

The complete sub is as follows:

Sub ScenarioGenerator()
Dim SampleSize As Integer
Dim TargetCell As String
Dim Counter As Integer
Dim Carrier As Variant

SampleSize = InputBox("How many generations should be run?")
TargetCell = InputBox("Which cell result should be recorded?")

Application.ScreenUpdating = False

For Counter = 1 To SampleSize
   Carrier = Range("Model!" & TargetCell).Value
   Range("Output!A1").Offset(Counter - 1, 0).Value = Carrier
Next Counter

Application.ScreenUpdating = True

MsgBox ("Done!")

End Sub

 

Previous post
TOTW index
Next post

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

Anonymous