Vba scenario manager in vba in Excel

Scenario Manager comes under "What If" Analysis of Excel.

As the name indicates, it is a prediction tool that allows users to check output for various expected input values.

Scenario Manager is one among the "What If" Analysis tool that predicts the possible outcome of various scenarios.

A�Scenario�is a set of values that Excel saves and can substitute automatically on worksheet.

If there are several scenarios for which the outcome has to be predicted in order to choose a right plan, then the "Scenario Manager" can be of very much use.

Collect all possible values and substitute the values to see the possible output.

The following example illustrates this:

Financial Prediction 2017

excel vba scenario manager in vba

The following code shows how to create a Scenario Manager in VBA.

Sub Scenariomanager()

Dim sm As Scenario

Dim sn As String

sn = InputBox("Enter a scenario name")

Dim wsheet As Worksheet

Set wsheet = Sheets("Q84")

wsheet.Scenarios.Add Name:=sn, ChangingCells:=wsheet.Range("B12:B13" _

), Values:=Array("67500", "42300"), Comment:="" _

, Locked:=True, Hidden:=False





Note that, the scenario name has to be different, every time the code is tested,else, it will result in an error.

The screenshot of the editor and the output is as shown below.

excel vba scenario manager in vba

excel vba scenario manager in vba


You can find similar Excel Questions and Answer hereunder

1) How to create charts in Excel VBA

2) Vba clear the contents of an entire sheet in Excel

3) How to hide and unhide rows and columns in excel VBA

4) Want to use Microsoft Outlook in Excel. Here some basic explanation to get you started

5) How can I dynamically add a hyperlink using VBA?

6) How can I extract file name from a full path including folder path and file name?

7) How can I export a chart as a gif file?

8) How to use goal seek in Excel. Some good introduction to goal seeking function

9) How do I use Find to determine last occurrence of a string in a WorkSheet range using VBA?

10) How can I sort data using VBA?


Here the previous and next chapter