Class Scenario (Excel VBA)

The class Scenario represents a scenario on a worksheet. To use a Scenario class variable it first needs to be instantiated, for example

For Each

Here is an example of processing the Scenario items in a collection.

Dim scns As Scenarios: Set scns = 
For Each scn In scns.Merge Source:=
	With scn
		
	End With
Next scn

ChangeScenario

Changes the scenario to have a new set of changing cells and (optionally) scenario values.

If you specify Values, the array must contain an element for each cell in the ChangingCells range; otherwise, Microsoft Excel generates an error.

ChangeScenario (ChangingCells, Values)

Worksheets("Sheet1").Scenarios(1).ChangeScenario ChangingCells:=Worksheets("Sheet1").Range("A1:A10")

Arguments

The following argument is required

ChangingCells (Range) - A Range object that specifies the new set of changing cells for the scenario. The changing cells must be on the same sheet as the scenario.

Optional arguments

The following argument is optional

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

ChangingCells

Returns a Range object that represents the changing cells for a scenario.

Dim scn As Scenario: Set scn = 
Dim rngChangingCells As Range
Set rngChangingCells = scn.ChangingCells

Comment

Returns or sets a String value that represents the comment associated with the scenario.

The comment text cannot exceed 255 characters.

Worksheets("Sheet1").Scenarios(1).Comment =  "Worst case July 1993 sales"

Delete

Deletes the object.

Dim scn As Scenario: Set scn = 
scn.Delete

Hidden

Returns or sets a Boolean value that indicates if the scenario is hidden.

The default value for this property is False. Don't confuse this property with the FormulaHidden property.

Dim scn As Scenario: Set scn = 
scn.Hidden = True

Index

Returns a Long value that represents the index number of the object within the collection of similar objects.

Dim scn As Scenario: Set scn = 
Dim lngIndex As Long
lngIndex = scn.Index

Locked

Returns or sets a Boolean value that indicates if the object is locked.

This property returns True if the object is locked or False if the object can be modified when the sheet is protected.

Dim scn As Scenario: Set scn = 
scn.Locked = True

Name

Returns or sets a String value representing the name of the object.

Dim scn As Scenario: Set scn = 
scn.Name =

Show

Shows the scenario by inserting its values on the worksheet. The affected cells are the changing cells of the scenario.

Dim scn As Scenario: Set scn = 
Dim booShow As Boolean
booShow = scn.Show

Values

Returns a Variant array that contains the current values of the changing cells for the scenario.

Values (Index)

Index: The position of the value.

Dim scn As Scenario: Set scn = 
Dim varValues As Variant
varValues = scn.Values