Programming Excel with VBA and .NET

Work with Scenario Objects

When you need to look at the possible consequences of applying different sets of values to your worksheet calculations, it can be useful to define scenarios for each possibility. You can do this in code by creating a scenario and adding it to the Scenarios collection, which contains all the scenarios for the specified worksheet. For example, you can create one scenario that uses conservative sales results and another that is more optimistic, and then compare what happens to your bottom line.

The following code adds a formula to a worksheet cell to show the sum of a set of values and then creates a scenario for a set of values that is very conservative:

With ActiveSheet
    ' Set cell A6 as the sum of cells A1 through A5.
    .Range("A6") = "=Sum(A1:A5)"
    ' Create a low-value scenario.
    .Scenarios.Add "Low", .Range("A1:A5"), Array(10, 20, 30, 40, 50)
    .Scenarios("Low").Show
End With

The following code creates a second scenario with a set of values that is more optimistic:

With ActiveSheet
    ' Create a high-value scenario.
    .Scenarios.add "High", .Range("A1:A5"), Array(100, 200, 300, 400, 500)
    .Scenarios("High").Show
End With

If you want to change the values in a particular scenario, you can use the ChangeScenario method. The following code upgrades the values in the "Low" scenario:

With ActiveSheet.Scenarios("Low") ' Change the values of the low value scenario. .ChangeScenario ChangingCells:=ActiveSheet.Range("A1:A5"), Values:=Array(15, 25, 35, 45, 55) .Show End With ...