Class Scenarios (Excel VBA)

A collection of all the Scenario objects on the specified worksheet. To use a Scenarios class variable it first needs to be instantiated, for example

Add

Creates a new scenario and adds it to the list of scenarios that are available for the current worksheet.

A scenario name must be unique; Microsoft Excel generates an error if you try to create a scenario with a name that's already in use.

Add (Name, ChangingCells, Values, Comment, Locked, Hidden)

Worksheets("Sheet1").Scenarios.Add Name:="Best Case", _ 
 ChangingCells:=Worksheets("Sheet1").Range("A1:A4"), _ 
 Values:=Array(23, 5, 6, 21), _ 
 Comment:="Most favorable outcome."

Arguments

The following arguments are required:

Name (String) - The scenario name.

ChangingCells (Range) - A Range object that refers to the changing cells for the scenario.

Optional arguments

The following arguments are optional

Values (Scenario) - An array that contains the scenario values for the cells in ChangingCells. If this argument is omitted, the scenario values are assumed to be the current values in the cells in ChangingCells.

Comment (Comment) - A string that specifies comment text for the scenario. When a new scenario is added, the author's name and date are automatically added at the beginning of the comment text.

Locked (Boolean) - True to lock the scenario to prevent changes. The default value is True.

Hidden (Boolean) - True to hide the scenario. The default value is False.

Count

Returns a Long value that represents the number of objects in the collection.

Dim scns As Scenarios: Set scns = 
Dim lngCount As Long
lngCount = scns.Count

CreateSummary

Creates a new worksheet that contains a summary report for the scenarios on the specified worksheet.

CreateSummary (ReportType, ResultCells)

Worksheets("Sheet1").Scenarios.CreateSummary ResultCells := Worksheets("Sheet1").Range("C4:C9")

Arguments

Optional arguments

The following arguments are optional

ReportType (XlSummaryReportType) - Specifies whether the summary report is a PivotTable or a standard summary.

Possible return values are xlStandardSummary - List scenarios side by side, xlSummaryPivotTable - Display scenarios in a PivotTable report.

ResultCells (Range) - A Range object that represents the result cells on the specified worksheet. Normally, this range refers to one or more cells containing the formulas that depend on the changing cell values for your model; that is, the cells that show the results of a particular scenario. If this argument is omitted, there are no result cells included in the report.

Item

Returns a single object from a collection.

Item (Index)

Index: The name or index number for the object.

Dim scns As Scenarios: Set scns = 
Dim scn As Scenario
Set scn = scns(Index:=1)

Merge

Merges the scenarios from another sheet into the Scenarios collection.

The value of a merged range is specified in the cell of the range's upper-left corner.

Merge (Source)

Source: The name of the sheet that contains scenarios to be merged, or a Worksheet object that represents that sheet.

Dim scns As Scenarios: Set scns = 
scns.Merge Source:=