Search Contextures Sites
Custom Search

Excel Scenarios -- Programming

You can use Excel Scenarios to store several versions of the data in a worksheet. To manage them, you can use programming to display, list, and create Scenarios.

Create a List of Excel Scenarios  

Create a New Scenario  

Test the Code  

Download the Sample File

More Tutorials

This example uses the Scenarios created here:    Excel Scenarios -- Automatically Show   

Create a List of Excel Scenarios

To list all the Scenarios on a worksheet, you can adapt the following code. 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.

Sub ScenarioList()
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 example, a Scenario for the Sales 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:

  1. Select cell B1 on the Budget worksheet
  2. On the Ribbon's Data tab, click Data Validation. (In Excel 2003, choose 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 the data on the worksheet

Enter the revised budget data shown below:

A
B
1

Budget 2012

Sales

2

 

 

3

Sales

$ 4,100,000

4

Expenses

$ 2,450,000

5

 

 

6

Profit

=Sales - Expenses

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
If ActiveSheet.Name = Me.Name Then
  If Target.Address = "$B$1" Then
    ActiveSheet.Scenarios(Target.Value).Show
  End If
End If
Exit Sub

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

End Sub

  1. 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("Lists")
Set rngChange = wsBudget.Range("Dept,Sales,Expenses")

strName = wsBudget.Range("Dept").Value
'create the scenario
wsBudget.Scenarios.Add Name:=strName, _
  ChangingCells:=rngChange
'find the first available row
lRow = wsLists.Cells(Rows.Count, 1).End(xlUp).Row + 1
wsLists.Cells(lRow, 1).Value = strName
'sort the list
With wsLists
  .Range(.Cells(1, 1), .Cells(lRow, 1)).Sort _
    Key1:=.Cells(1, 1), _
    Order1:=xlAscending, Header:=xlYes
End With

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 Code

  1. On the Ribbon's View tab, click Macros > View Macros
    (In Excel 2003: From the Tools menu, choose Macro | 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.

Download the Sample File

Download the zipped sample Excel Scenarios completed file  

More Tutorials

Excel Scenarios -- Create and Show  

Excel Scenarios -- Scenario Summaries  

Excel Scenarios -- Automatically Show Scenario  

 

 

More Tutorials

Create and Show Scenarios

Scenario Summaries

Automatically Show Scenarios

 

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: April 6, 2016 4:57 PM