Home > Macros > Scenarios

Macros for Excel Scenarios

Use these macros to create a list all the scenarios, or create new scenarios, or update the values for existing scenarios, from a list on the worksheet. Get free workbook with the macros

Introduction

Use the macros on this page to automate your work with Excel Scenarios. For example,

1. create an A-Z list of Scenarios that you previously created
2. create a new Scenario, based on values from the worksheet
3. update Scenario values from worksheet data

All of the examples on this page are based on Department Budget Scenarios from the Macro to Show Excel Scenario page.

In that tutorial, you saw how to automate Scenario selection. Just choose a Scenario name from a drop down list, and a macro automatically shows the values from that Scenareio

Get a List of Excel Scenarios

To list all the Scenarios on a worksheet, you can adapt the following macro. This example lists all the Scenarios from the Budget worksheet, starting the list in cell A2 on the Lists worksheet. It then sorts the list alphabetically.

Note: This list is used as a source for the drop down list of Department, on the Budget sheet

Sub ScenarioList()
''used for Budget Sheet drop down
Dim sc As Scenario
Dim wsBudget As Worksheet
Dim wsLists As Worksheet
Dim iRow As Integer
iRow = 2  'leave row 1 for heading
Set wsBudget = Worksheets("Budget")
Set wsLists = Worksheets("Lists")

wsLists.Columns(1).ClearContents
wsLists.Cells(1, 1).Value = "Scenarios"
For Each sc In wsBudget.Scenarios
  wsLists.Cells(iRow, 1).Value = sc.Name
  iRow = iRow + 1
Next sc

With wsLists
  .Range(.Cells(1, 1), .Cells(iRow - 1, 1)).Sort _
    Key1:=.Cells(1, 1), _
    Order1:=xlAscending, Header:=xlYes
End With

End Sub

Create a New Scenario

The sample workbook has Scenarios for the Finance and the Marketing budgets. In this macro, a Scenario for a new department will be added.

Disable Data Validation error alert

On the Budget worksheet, cell B1 has a data validation dropdown list.

To allow other items to be entered in the cell, follow these steps to turn off the error alert, if it has not been turned off already:

1. Select cell B1 on the Budget worksheet
2. On the Ribbon's Data tab, click Data Validation.
3. On the Error Alert tab, remove the check mark from Show error alert after invalid data is entered.
4. Click OK.

Enter data on worksheet

To create a new scenario, enter its budget data on the Budget sheet. For example, shown below:

• Cell B1: Sales East
• Cell B3: $2,100,000
• Cell B4: $ 1.050,000

Add Error Handling to the Event Code

The Budget worksheet has event code that displays a Scenario when a Scenario name is selected in cell B1. The following revised code will handle errors if a new Scenario name is entered in cell B1.

1. Right-click the Budget worksheet tab, and modify the Worksheet_Change code, as shown below:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
Application.EnableEvents = False

If Target.Address = Range("Dept").Address Then
ActiveSheet.Scenarios(Target.Value).Show
End If

ExitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 1004 Then
MsgBox "That Scenario is not available"
Else
MsgBox Err.Number & ": " & Err.Description
End If
Resume ExitHandler

End Sub

2. Choose File | Close and Return to Microsoft Excel.

Add the CreateScenario code

To create a new Scenario on a worksheet, you can adapt the following code. This example creates a Scenario from the data entered on the Budget worksheet, and uses the department name as the Scenario name.

Sub CreateScenario()
On Error GoTo errHandler
Dim wsBudget As Worksheet
Dim wsLists As Worksheet
Dim rngChange As Range
Dim strName As String
Dim sc As Scenario
Dim lRow As Integer

Set wsBudget = Worksheets("Budget")
Set wsLists = Worksheets("ListsOn Error GoTo errHandler
Dim wsBudget As Worksheet
Dim wsLists As Worksheet
Dim rngChange As Range
Dim strName As String
Dim sc As Scenario
Dim lRow As Integer

Set wsBudget = Worksheets("Budget")
Set wsLists = Worksheets("Lists")
Set rngChange = wsBudget.Range("Dept,Sales,Expenses")

strName = wsBudget.Range("Dept").Value

'create the scenario
wsBudget.Scenarios.Add Name:=strName, _
ChangingCells:=rngChange

'add to Scenario list
''used for drop down
ScenarioList

'add to Value Settings list
ScenarioListSet

Exit Sub

errHandler:
If Err.Number = 1004 Then
MsgBox "The Scenario could not be created." _
& vbCrLf & "Please try a different name."
Else
MsgBox Err.Number & ": " & Err.Description
End If

End Sub 

Test the Create Scenario Macro

1. On the Ribbon's View tab, click Macros, then click View Macros
2. In the list of macros, select CreateScenario
3. Click the Run button.
4. (optional) Add a button to the worksheet, to run the CreateScenario Macro, as shown below.

Note: When opening the workbook, you will have to enable macros in order to use this code.

Update Scenarios from Worksheet

Usually, to make changes to the values in a Scenario, you have to open the Scenario Manager, and go through a few screen, to get to the Scenario Values dialog box.

For example, here is the screen where you can edit the Scenario values for the Marketing department's budget data.

In a workbook with multiple Scenarios, it could take a long time to edit each one manually, going through a series of steps.

Worksheet Settings List

To make it easier to update Scenarios, the Excel sample file, that you can download at the end of this page, has a value settings list on a worksheet, where you can quickly view and edit the data for each Scenario.

• Note: Thank you to Khushnood Viccaji (LinkedIn profile), who suggested a macro for updating Excel scenario values, and sent me an example of his code and settings table.

In the settings list shown in the screen shot shown:

• scenario names are in column B
• Scenario values, in column C and Column D, can be revised

Note: For your own Scenario workbooks, you could revise the list, and the macro code, to include more value columns.

Update Scenario Values

After you make changes to any of the Scenario value settings, click the macro button on the Settings worksheet.

That runs a macro named ScenarioUpdate (shown below), to update each Scenario with the data from the Settings list on the worksheet.

Here is the code for the ScenarioUpdate macro. The code is stored on a regular code module, named modSettings.

Sub ScenarioUpdate()
'update Scenarios from values
'  on Settings sheet
On Error GoTo errHandler
Dim wsBgt As Worksheet
Dim wsSet As Worksheet
Dim rngVal As Range
Dim rngChg As Range
Dim c As Range
Dim cVal As String
Dim myDept As String

Set wsBgt = Worksheets("Budget")
Set wsSet = Worksheets("Settings")
Set rngVal = wsSet.Range("ValueSet")
Set rngChg = wsBgt.Range("Dept,Sales,Expenses")
myDept = wsBgt.Range("Dept").Value

For Each c In rngVal.Columns(1).Cells
    cVal = c.Value
    wsBgt.Scenarios(c.Value).ChangeScenario _
        ChangingCells:=rngChg, _
        Values:=Array(c.Value, _
          c.Offset(0, 1).Value, _
          c.Offset(0, 2).Value)
Next c

 wsBgt.Scenarios(myDept).Show

Exit Sub

errHandler:
  If Err.Number = 1004 Then
    MsgBox "The " & cVal _
      & "Scenario could not be updated."
  Else
    MsgBox Err.Number & ": " & Err.Description
  End If

End Sub

Add New Scenarios to Settings List

The sample workbook has another macro, named ScenarioListSet, which runs automatically, when you use the Create Scenario button.

Near the end of the CreateScenario macro, the following line runs the ScenarioListSet macro

'add to Value Settings list
ScenarioListSet

The ScenarioListSet macro (code shown below) adds the new scenario, and its values, to the Scenario Value Settings list, and sorts the Scenario names, A to Z.

Here is the macro code, which is stored on the regular code module, named modSettings

Sub ScenarioListSet()
''add new Scenario to Settings list

Dim wsBgt As Worksheet
Dim wsSet As Worksheet
Dim rngVal As Range
Dim rngChg As Range
Dim myRow As Long
Dim myCol As Long
Dim lastRow As Range

Set wsBgt = Worksheets("Budget")
Set wsSet = Worksheets("Settings")
Set rngVal = wsSet.Range("ValueSet")
Set rngChg = wsBgt.Range("Dept,Sales,Expenses")

Set lastRow = rngVal.Rows(rngVal.Rows.Count)
myRow = lastRow.Row
myCol = rngVal.Columns(1).Column

rngChg.Copy
wsSet.Cells(myRow + 1, myCol).PasteSpecial _
   Paste:=xlPasteValues, Transpose:=True
lastRow.Copy

wsSet.Cells(myRow + 1, myCol).PasteSpecial _
  Paste:=xlPasteFormats

'reset the range
Set rngVal = wsSet.Range("ValueSet")

rngVal.Sort _
    Key1:=rngVal.Cells(1, 1), _
      Order1:=xlAscending, _
      Header:=xlNo

End Sub

Download the Sample File

Click this link to get the completed Excel Scenarios Macros workbook. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the code.

More Scenario Tutorials

Create and Show Scenarios

Scenario Summaries

Automatically Show Scenario