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.
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.
3. Click on <Add> to add another Scenario.
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%
6. Click on <Add> to add another Scenario.
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.
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.
|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 Dim lReply As Long '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 MsgBox rRange.Address 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