The Scenario object allows you to store multiple values in a single cell and represents a scenario.
The Scenarios collection consists of Scenario objects and contains all the scenarios of a worksheet.
Table. Methods of the Scenarios Collection
| Method | Description |
| Add | Adds a new scenario. Add(Name, ChangingCells, Values, Comment, Locked, Hidden) • Name — the name of the scenario; • ChangingCells — the range allocated for the scenario’s changing cells; • Values — an array of values entered into the changing cells; • Comment — a text string of comments; • Locked — a logical (Boolean) property. If set to True, modification of the scenario is blocked; • Hidden — a logical (Boolean) property. If set to True, the scenario is hidden. |
| CreateSummary | Adds a new worksheet to the workbook and creates a summary report. CreateSummary(ReportType, ResultCells) • ReportType — the type of report. Permissible values: → xlStandardSummary (a standard outline report); → xlSummaryPivotTable (a PivotTable report). • ResultCells — a reference to the cell or range of cells with formulas dependent on the values of the cells specified in the ChangingCells parameter of the Add method.The report is created on a separate worksheet and is not linked to the source data. It is very useful to assign names to the cells specified in the ResultCells parameter before generating the report. Otherwise, instead of meaningful names, the report will contain less comprehensible cell references. |
Table Methods of the Scenario Object
| Method | Description |
| Show | Displays the scenario by entering the scenario’s values into the changing cells. |
| Delete | Deletes the scenario. |
| ChangeScenario | Changes the group of changing cells in a scenario. ChangeScenario(ChangingCells, Values) • ChangingCells — the group of cells that will act as the new set of changing cells; • Values — an array with new values for the changing cells. |
Table. Properties of the Scenario Object
| Property | Description |
| ChangingCells | Returns the range of the changing cells. Example: ActiveSheet.Scenarios(1).ChangingCells.Select |
| Values | Returns an array of the current values of the changing cells. Example: ActiveSheet.Scenarios(1).Values = Range(« B1:B3 ») or ActiveSheet.Scenarios(1).Values = Array(1, 3, 5) |