Excel -- Scenarios -- Programming

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


You can use 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:
   Scenarios -- Automatically Show   

Download the zipped sample starter file

 

Create a List of 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

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

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. 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 2006 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:
Sub CreateScenario()
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
  If Target.Address = "$B$1" Then
    ActiveSheet.Scenarios(Target.Value).Show
  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. From the Tools menu, choose Macro | Macros
  2. In the list of macros, select CreateScenario
  3. Click the Run button.

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

Download the zipped sample completed file  

 

 

 

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

 

 
 
       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:56 PM