## SCENARIOS

Scenarios form part of the analyzing tools that Excel has to offer. An example of how Scenarios could be used effectively could be a budget worksheet with this years values in it and various calculations. Using the Scenario Manager you could create a current scenario with this years values, a second scenario with last years values in it and yet a third with next years forecasted values in it. You can then gauge the effects on your calculations by moving between the different scenarios easily. You can even import scenarios from other workbooks if you want to and easily create summaries of your Scenarios.

Open the attached workbook. The cells with a yellow background are the cells containing formulas. Using the Scenario Manager, we will look at the changes to these formulas as we substitute different values into our worksheet.

The first thing you need to do is to set up a default scenario. To do this go to Tools>Scenarios. On the Scenario Manager select <Add> then give your scenario a name, such as Budget 2009.

1. Tab to <Changing cells:> and then select cells B1, B3:B7 and E3. Click <OK>.
2. You will see in the Scenario Values dialog the cell references (as absolute cell references) and to the right of each cell reference the values you selected.
4. We will call this Scenario Budget 2008. Click on <OK>
5. This time we will enter in the following 2008 figures to the right of their corresponding cell references.

B1 = 2008
B3 = 32000
B4 = 24380
B5 = 4600
B6 = 17500
B7 = 96740
E3 = .12 or 12%

7. We will call this Scenario Budget 2010. Click on <OK>
8. This time we will enter in the following 2008 figures to the right of their corresponding cell references.

B1 = 2010
B3 = 38600
B4 = 31280
B5 = 8100
B6 = 22000
B7 = 116900
E3 = .13 or 13.5%

9. Click on <OK>
10. You will now see all three of your Scenarios in the Scenario Manager.
11. Click on Budget 2008 and then select <Show>.

You should see the 2008 figures substituted in the appropriate cells in your worksheet, therefore allowing you to gauge the changes on your calculations.

As an added feature you can create a summary of the Scenarios you have created either as an outline, or as a Pivot Table report. On the Scenario Manager, click on <Scenario Summary> and check out both options.

You could also Merge Scenarios from either different worksheets or workbooks. Click on the <Merge> button to see the options.

### USING NAMES

To make your Scenarios a little more user friendly and easier to work with, you could name the cells. Try this:

1. Select cells A1:B7
2. Go to Insert>Name>Create
3. In the Create Names dialog, select Left Column (this option is the default and may already be ticked)
4. Click on OK.
5. Select cells D3:E3 and do the same

Now go into your Scenario Manager (Tools>Scenarios) and select on any of the three Scenarios, then select <Edit> then <OK> and you will see names in lieu of cell references.

EXCEL VBA. InputBox Function

This month we will look into the INPUTBOX. As you may, or may not know, there are multiple types we can use. The code below will show you some of these types. We will kick off we the most common use of an INPUTBOX, collect text

```Sub StandardInputBox()
Dim strName As String

'InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
strName = InputBox("Enter you name.", "NAME COLLECTOR")
'Exit sub if Cancel button used or no text entered
If strName = vbNullString Then Exit Sub

MsgBox "Hello " & strName

End Sub
```

Now we can look at how we can use Application.InputBox with different types.

Type Meaning
0 A formula
1 A number
2 Text (a string)
4 A logical value (True or False)
8 A cell reference, as a Range object
16 An error value, such as #N/A
64 An array of values
```Sub NumbersOnly()
Dim dNum As Long

'expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
dNum = Application.InputBox("Enter you age.", "AGE COLLECTOR", , , , , , 1)
'Exit sub if Cancel button used
If dNum = 0 Then Exit Sub

'As we have used Type 1 Inputbox, Excel will handle any text entered
MsgBox "You are " & dNum & " Years old."

End Sub

Sub RangeAsObject()
Dim rRange As Range

'We use On Error so we ignore run time errors
On Error Resume Next
'expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
Set rRange = Application.InputBox("With you mouse, select a range of cells.", _
"RANGE COLLECTOR", , , , , , 8)
On Error GoTo 0

'Check if range is valid
If rRange Is Nothing Then
'If range not valid, ask if they wish to retry
rRange = MsgBox("Non valid range. Try again?", vbOKCancel + vbQuestion)
If rRange = vbCancel Then 'No retry
Exit Sub
Else 'retry
Run "RangeAsObject"
End If
Else
End If
End Sub
```

Now let's see how we can the INPUTBOX to evaluate a chosen range with an expression collected from a user.

```Sub CollectExpressions()
Dim strExpression As String
Dim rRange As Range

On Error Resume Next
Set rRange = Application.InputBox("With you mouse, select a range of cells to evaluate.", _
"RANGE COLLECTOR", , , , , , 8)
On Error GoTo 0

If rRange Is Nothing Then
rRange = MsgBox("Non valid range. Try again?", vbOKCancel + vbQuestion)
If rRange = vbCancel Then 'No retry
Exit Sub
Else 'retry
Run "CollectExpressions"
End If
End If

strExpression = InputBox("Enter you expession.E.g. >5 or <10", "EXPRESSION COLLECTOR")
If strExpression = vbNullString Then Exit Sub

MsgBox "Using your expression." & vbNewLine _
& "COUNTIF = " & WorksheetFunction.CountIf(rRange, strExpression) & vbNewLine _
& "Top left cell = " & Evaluate(rRange(1, 1) & strExpression) & vbNewLine

End Sub
```

Hopefully, you find some uses for different INPUTBOX types. See ya next month :)

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Become an ExcelUser Affiliate & Earn Money

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft