Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Ozgrid Excel Newsletter. Excel Newsletter Archives  

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

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.

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