Search Contextures Sites

Excel Scenarios -- Programming

 

Create a List of Excel Scenarios  
Create a New Scenario  
Test the Code  
 


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.

This example will automate the Scenarios created here:
   Excel Scenarios -- Automatically Show   

Download the zipped sample Excel Scenarios starter file

 

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 at right.

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

Download the zipped sample Excel Scenarios completed file  

 

 

 

1. Excel Scenarios -- Create and Show  
2. Excel Scenarios -- Scenario Summaries  
3. Excel Scenarios -- Automatically Show Scenario  
4. Excel Scenarios -- Programming  

 
   

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2012
All rights reserved.

 
Last updated: April 24, 2011 12:04 AM