PeleFinal

Pele was an amazing soccer (futbol) player. I’m more of an American football fan myself – but I cannot deny the art, grace and class with which Pele played futbol. Pele is pictured here executing a bicycle kick against Belgium in 1968. Maybe what sets Pele and other sports stars apart from the rest is they too begin with the end in mind as suggested by Stephen Covey in The Seven Habits of Highly Effective People.

Today’s post, however, is not about futbol or the latest book being touted by business and leadership pundits. It is about the Goal Seek Method of the Range Object of the Excel Object Model.

Goal Seek

Goal2

Here is a sample formula to calculate Net Sales using 4 inputs: Average Check, Operating Days, Population and Participation. As you see displayed from FORMULATEXT() Function, the 4 inputs are simply multiplied together.


    The FORMULATEXT() function is one of the new functions introduced with Excel 2013. Check them all out here


We can use Goal Seek manually from the Ribbon. In Excel 2013:

Goal3

  1. Click on the Data Tab of the Ribbon
  2. Go to the Data Tools Group
  3. Click on What-If Analysis
  4. Click on Goal Seek

Goal4

I launched Goal Seek. I want to know what participation would need to be to achieve Net Sales of $10K?

Goal5

Holding all other inputs constant, I would need to increase participation from 14.32% to 16.83% to get to $10K in Net Sales. All well and good so far. But what if I need to find values for 12 different months? 52 Weeks? Some other Scenario with 100’s of desired outputs? Time for some VBA!

Goal Seek VBA

Goal6

I cannot find the Goal Seek Method listed as a Member of the Range Class in the Object Explorer in the VBA Editor?


  1. Press [Alt]+[F11] to launch the VBA Editor
  2. Once in the Editor, [F2] to launch the Object Explorer


Hmmm….I’ll check documentation on MSDN:


  1. Object model reference (Excel 2013 developer reference)
  2. Object model reference (Excel 2010 developer reference)
  3. Object model reference (Excel 2003 developer reference) – Compiled Help File


I checked the Developer Reference for Excel 2013 and Excel 2010. I could not find anything on Goal Seek. I downloaded the Excel 2003 Developed Reference as a compiled help file and finally found some documentation on the Goal Seek Method.

Goal7

Goal8

Here’s a quick little snippet I wrote. Even though Goal Seek does not show as a Method of the Range Object in Excel 2013, even though the Goal Seek Method is not documented in the Excel Developer Reference for Excel 2013, it will still work with Excel 2013.

This is not the first time I have seen this. I can’t recall what the last Object and Member were.

Goal Seek Method Requirements

For the Goal Seek Method to work, I must provide the following objects and values:

  1. A Range Object (Cell) that contains a formula
  2. A goal value
  3. And the changing Cell as a Range Object

Well, that makes sense, since the Goal Seek Dialog Box prompted me to manually select or enter those items earlier.

From the requirements list, I see that one strategy would be to use a loop with the .Cells() Property of the Worksheet Object as the Range Objects. With the Cells() Property, I need to pass a Row Index and a Column Index through each iteration of the loop.

The Setup…

Goal9

I added 11 months to the existing data and randomized the participation amounts. Now I would like to find what the participation would need to be for each of the 12 months to increase sales an additional $5K each month.

Goal10

Goal Seek Method…Fixed Range

Here is a basic Goal Seek Snippet that could be used with a fixed Range where the Worksheets and Range Objects are known and do not change:

Option Explicit

Sub SetGoal()

'----------------------------------------------------------------------
'Use Goal Seek Method of the Range Object with a dictator application
'----------------------------------------------------------------------
'Author     : Winston Snyder
'Date       : 3/30/2015
'Website    : http://dataprose.org/
'----------------------------------------------------------------------

    'Declare objects
        Dim wb As Workbook
        Dim ws As Worksheet
  
    'Declare constants
        Const lngROW_SEEK As Long = 4
        Const lngROW_GOAL As Long = 10
        Const lngROW_CHANGE As Long = 5
        Const lngCOL_BEGIN As Long = 6
        Const lngCOL_END As Long = 17
    
    'Declare variables
        Dim i As Long
    
    'Initialize
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Sheet1")
    
    'Goal Seek
        With ws
            For i = lngCOL_BEGIN To lngCOL_END
                .Cells(lngROW_SEEK, i).GoalSeek _
                    Goal:=.Cells(lngROW_GOAL, i).Value, _
                    ChangingCell:=.Cells(lngROW_CHANGE, i)
            Next i
        End With
    
    'Tidy up
        Set ws = Nothing
        Set wb = Nothing
End Sub

Goal11

My Change Row was Row 5, “Participation”. Participation has been increased to the point needed to drive Net Sales on Row 4 to the target “New Sales” on Row 10.

All well and good, but what if I want to:

  1. Choose a different set of values pre-programmed values for the the Goal?
  2. Choose a different input/driver Row

I will need something a bit more flexible.

Goal Seek Method…Dynamic Options

One way to make the code snippet a bit more flexible, is to use a Type:=8 InputBox. The Type:=8 InputBox allows the user to select a Cell on a Worksheet. I first introduced this concept in this post on the LIKE Operator.


    More on Application.InputBox Method (Excel) here


I’ll create a Function and call it 3 times to prompt the user to select a cell that begins the Range for

  1. The Range that contains the formula that I am seeking a goal for.
  2. The Range that contains the target value
  3. The Range that contains an input that is to be be changed to arrive at the target value
Public Function GetUserCell(strPrompt As String) As Range
 
    'Declare variables
        Dim rng As Range
     
    'Users - select a cell on a worksheet
        Set rng = Application.InputBox( _
                    Prompt:=strPrompt, _
                    Title:="Select a Cell", _
                    Default:=ActiveCell.Address, _
                    Type:=8) 'Range selection
                     
    'Get the parent worksheet of the selected cell
        Set GetUserCell = rng
     
    'Tidy up
        Set rng = Nothing

 End Function

Now I can create a Sub(), call the function 3 times, and load my 5 variables based on the user selections.

The final dynamic Goal Seek Sub():

Option Explicit

Sub SetGoalDynamic()

'----------------------------------------------------------------------
'Use Goal Seek Method of the Range Object allow user to choose cells in range for:
'     The formula to be used in determining a solution
'     The goal value
'     An input cell that will be altered by Goal Seek to arrive at the desired solution
'----------------------------------------------------------------------
'Author     : Winston Snyder
'Date       : 3/30/2015
'Website    : http://dataprose.org/
'----------------------------------------------------------------------

    'Declare objects
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim rngRowSeek As Range
        Dim rngRowGoal As Range
        Dim rngRowChange As Range
  
    'Declare constants
        Const strROW_SEEK As String = "Please select a cell in the row that contains the formula you will use for Goal Seek."
        Const strROW_GOAL As String = "Please select a cell in the row that contains the Goal Value that you are trying to find a solution for."
        Const strROW_CHANGE As String = "Please select a cell in the row that contains an input value for the formula."
    
    'Declare variables
        Dim lngRowSeek As Long
        Dim lngRowGoal As Long
        Dim lngRowChange As Long
        Dim lngColBegin As Long
        Dim lngColEnd As Long
        Dim i As Long
    
    'Initialize
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Sheet1")
        
    'Get user inputs
        Set rngRowSeek = GetUserCell(strPrompt:=strROW_SEEK)
        Set rngRowGoal = GetUserCell(strPrompt:=strROW_GOAL)
        Set rngRowChange = GetUserCell(strPrompt:=strROW_CHANGE)
        
    'Get variable values from user inputs
        lngRowSeek = rngRowSeek.Row
        lngRowGoal = rngRowGoal.Row
        lngRowChange = rngRowChange.Row
        lngColBegin = rngRowSeek.End(xlToLeft).Column + 1 'Offset for labels
        lngColEnd = rngRowSeek.End(xlToRight).Column
    
    'Goal Seek
        With ws
            For i = lngColBegin To lngColEnd
                .Cells(lngRowSeek, i).GoalSeek _
                    Goal:=.Cells(lngRowGoal, i).Value, _
                    ChangingCell:=.Cells(lngRowChange, i)
            Next i
        End With
    
    'Tidy up
        Set rngRowSeek = Nothing
        Set rngRowGoal = Nothing
        Set rngRowChange = Nothing
        Set ws = Nothing
        Set wb = Nothing
        
End Sub

Tidy Up

That’s it for today, hopefully something helpful for you in this post. I use Goal Seek a bit when forecasting and budgeting and my supervisor asks me for some options to achieve certain desired targets or we get push back upstream.

How do you use Goal Seek?

Btw, as of today, Monday, March 30,2015, the first NFL Preseason game is only 132 days away. Sunday night, August 9, 2015. Hang in there!

, , , , , , , , , ,

AdvanceFinal500

AAAAAARRRRGGGGHHHHH!!!!! I just hate reviewing an Excel Workbook with lots of tabs and the ActiveCell is different on every tab. Let’s look at some ways to fix this. Before I can get going, I need to:

  1. Add several copies of a worksheet to the active workbook
  2. Randomize the ActiveCell on each worksheet



    edit: While my main point of this post is the GoTo Method of the Excel Application Object, I touch on many other items as well

  1. Copy Method of the Worksheet Object
  2. Cells Property of the Range Object
  3. Cells Property of the Worksheet Object
  4. Find Method of the Range Object
  5. Range Property of the Worksheet Object
  6. Select..Case Statement
  7. Application Worksheet Function Randbetween
  8. InputBox Method of the Excel Application Object
  9. VBA Instr() Function


Add Worksheet Copies

I downloaded a sample P&L Statement from the Internet. Now I just want to add 29 copies of the P&L Statement to the workbook.

PLStatement

Option Explicit

Sub Foo()

    'Declare variables
        Dim wb As Workbook
        Dim wsPL As Worksheet
    
    'Excel environment
        With Application
            .ScreenUpdating = False
        End With
        
    'Initialize
        Set wb = ThisWorkbook
        Set wsPL = wb.Worksheets("PL_CC")
    
    'Add worksheets
        Call CopyWorksheets(wb:=wb, _
                            wsSource:=wsPL, _
                            NumberOfCopies:=30)

    'Tidy up
        'Destroy objects
            Set wsPL = Nothing
            Set wb = Nothing
            
        'Excel environment
            With Application
                .ScreenUpdating = True
            End With
        
End Sub

Private Sub CopyWorksheets(wb As Workbook, _
                           wsSource As Worksheet, _
                           NumberOfCopies As Long)
    
    'Declare variables
        Dim i As Long
                           
                                   
    'Make copies of worksheet
        For i = 2 To NumberOfCopies
            wsSource.Copy _
                After:=wb.Worksheets(Worksheets.Count)
                ActiveSheet.Name = wsSource.Name & i
        Next i
End Sub

Great! Added 29 copies of the P&L Statement and renamed each worksheet.

PLStatementsMult

Randomize The ActiveCell

Since I made copies of a worksheet the ActiveCell is the same on every worksheet. I want to randomize the ActiveCell before I create some code to set the ActiveCell on every worksheet. The P&L Statement I am using has 15 Columns and 77 Rows. But I would like to determine those values with some VBA so that my code is more dynamic and will wok with any worksheet that I use in the future.

First I’ll check the UsedRange

Private Sub GetUsedRange(ws As Worksheet)

    'Declare variables
        Dim rng As Range
        
    'Create range object
        Set rng = ws.UsedRange
        
    'Print adddress
        Debug.Print rng.Address
        
    'Tidy up
        Set rng = Nothing
  
End Sub

Returns:

$A$1:$Y$77

That’s not what I want. I’m looking for $O$77 as the last used cell.

Last Used Cell Function

This function returns the correct last used cell:

Private Function GetLastUsedCell(ws As Worksheet) As Range

    'Declare variables
        Dim rngLastUsedCell As Range
        
    'Create range object from last used cell
        Set rngLastUsedCell = ActiveSheet.Cells.Find(What:="*", _
                                               After:=ws.Cells(1, 1), _
                                               LookIn:=xlFormulas, _
                                               LookAt:=xlPart, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlPrevious, _
                                               MatchCase:=False)

    'Pass object to function
        Set GetLastUsedCell = rngLastUsedCell
    
    'Tidy up
        Set rngLastUsedCell = Nothing
        
End Function

Returns:

$O$77

Perfect! That’s what I was looking for. Now I need to find the first used cell.

First Used Cell Function

In pure violation of the recommendation of Bovey et. al., in their landmark tome, Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition), I generally begin all of my worksheets at $A$1, but not always. So I need a function to find the first used cell on the worksheet no matter what worksheet I throw at the function.

Private Function GetFirstUsedCell(ws As Worksheet) As Range

    'Declare variables
        Dim rng As Range
        
    'Create range object from the first cell in the UsedRange
        Set rng = ws.UsedRange.Cells(1, 1)

    'Pass object to function
        Set GetFirstUsedCell = rng
    
    'Tidy up
        Set rng = Nothing
        
End Function

Returns:

$A$1

Excellent! That’s what I was looking for. Now that I have the first and last cells of the true UsedRange, I need to use the first cell and last cell to create a range of everything.

Create A Big Range

Private Function GetBigRange(ws As Worksheet, _
                             rngStart As Range, _
                             rngStop As Range) As Range
                             
    'Declare variables
        Dim rng As Range
        
    'Creat a range object from start and stop range positions
        Set rng = ws.Range(rngStart, rngStop)
        
    'Pass range object to function
        Set GetBigRange = rng
        
    'Tidy up
        Set rng = Nothing
                             
End Function

Returns:

$A$1:$O$77

Awesome! Now, I can use the entire range to generate random values to use for the ActiveCell.

Get Values

I’ll need to call a get value function 4 times to get the first row, the last row, the first column and the last column.

Here’s the function:

Private Function GetValueFromRange(rng As Range, _
                                   strType As String) As Long
    
    'Declare variables
        Dim x As Long
        
    'Get value depending on type
        With rng
            Select Case strType
                Case "FirstRow"
                    x = .Row
                Case "LastRow"
                    x = .Rows.Count
                Case "FirstColumn"
                    x = .Column
                Case "LastColumn"
                    x = .Columns.Count
            End Select
        End With
        
    'Pass value to function
        GetValueFromRange = x
     
End Function

And here is how I called the function 4 different times:

'Get values form total used range
        BeginRow = GetValueFromRange(rng:=rngAll, _
                                     strType:="FirstRow")
                                     
        EndRow = GetValueFromRange(rng:=rngAll, _
                                   strType:="LastRow")
                                   
        BeginColumn = GetValueFromRange(rng:=rngAll, _
                                   strType:="FirstColumn")
                                   
        EndColumn = GetValueFromRange(rng:=rngAll, _
                                   strType:="LastColumn")

Returns:

  • BeginRow 1
  • EndRow 77
  • BeginColumn 1
  • EndColumn 15
  • Gnarly! Now, I have the values that I can pass to a function to generate a random cell reference.

    Get Random Values

    Now that I have high-low pairs for rows and columns, I can use the Worksheet Function Randbetween to generate some random values for the row and column numbers.

    Private Function GetRandomValue(ValueLow As Long, _
                                    ValueHigh As Long) As Long
    
        'Declare variables
            Dim x As Long
            
        'Generate a random value
            x = Application.WorksheetFunction.RandBetween(ValueLow, ValueHigh)
            
        'Pass value to the function
            GetRandomValue = x
    
    End Function
    

    And here is how I call the function to get a random row and a random column:

                        'Get random row
                            RandomRow = GetRandomValue(ValueLow:=BeginRow, _
                                                       ValueHigh:=EndRow)
                                                       
                        'Get random column
                            RandomColumn = GetRandomValue(ValueLow:=BeginColumn, _
                                                          ValueHigh:=EndColumn)
    

    Go There!

    Once I have a Row and Column number, I can use the Cells property of the Worksheet Object in conjunction with the GoTo Method of the Excel Application Object to go to the desired cell:

                        'Go to the cell
                            Application.GoTo ws.Cells(RandomRow, RandomColumn), _
                                                      Scroll:=True
    

    ActiveCellOutput

    Bam! I sent the Worksheet Name and the ActiveCell Address to the Immediate Window.

    Let’s Go!

    Recall, the end in mind is to set the ActiveCell to the same cell on every sheet to aid in our visual review of each worksheet in the workbook. I introduced the Code Snippet just a few lines up:

                        'Go to the cell
                            Application.GoTo ws.Cells(RandomRow, RandomColumn), _
                                                      Scroll:=True
    

    So I just need to pass 2 values of datatype long and I can go to any cell I want. So to GoTo R1C1 is merely:

                        'Go to the cell
                            Application.GoTo ws.Cells(1, 1), _
                                                      Scroll:=True
    

    Pow! The ActiveCell on every Worksheet is now R1C1 (A1). The review is much more pleasurable.

    But I want flexibility!

    What if you want to set the ActiveCell as $R$10 on every Worksheet, next time you want $C$12 – you get the point – enter the InputBox.

    Get Input

    I previously demonstrated a function to allow the user to select a cell here. Read more about using InputBoxes and the various types on MSDN

    For my purposes, I want the user to select a cell, so I will use Type:=8 for my InputBox:

    Public Function GetCell(ws As Worksheet) As Range
         
        'Declare variables
            Dim rng                         As Range
         
        'Users - select a cell on a worksheet
            Set rng = Application.InputBox( _
                                           Prompt:="Please select a cell on the worksheet", _
                                           Title:="Select a cell", _
                                           Default:=ActiveCell.Address, _
                                           Type:=8) 'Range selection
             
        'Pass the range object to the function
            Set GetCell = rng
         
        'Tidy up
            Set rng = Nothing
    
     End Function
    

    Now, I will test the GetCell Function:

    Option Explicit
    
    Sub testit()
    
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim C As Range
        
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("PL_CC")
        Set C = GetCell(ws:=ws)
        
        Debug.Print C.Address
        
        Set C = Nothing
        Set ws = Nothing
        Set wb = Nothing
    
    End Sub
    

    And spin the test code:

    SelectCellPrompt

    I am prompted to select a cell on the worksheet. I clicked on cell $C$8.

    ClickCellOutput

    Shazam! That worked perfectly! I can move the Function into production and dynamically let the user choose what should be the ActiveCell on every Worksheet.

    Option Explicit
    
    Sub SetActiveCell()
        
        'Declare variables
            Dim wb As Workbook
            Dim ws As Worksheet
            Dim wsPL As Worksheet
            Dim C As Range
            Dim userRow As Long
            Dim userColumn As Long
        
        'Initialize
            Set wb = ThisWorkbook
            Set wsPL = wb.Worksheets("PL_CC")
            Set C = GetCell(ws:=wsPL)
        
            With C
                userRow = .Row
                userColumn = .Column
            End With
        
        'Set the ActiveCell on each worksheet
            For Each ws In wb.Worksheets
                If InStr(ws.Name, "PL") Then
                    Application.GoTo ws.Cells(userRow, userColumn), _
                                    Scroll:=True
                End If
            Next ws
        
        'Tidy up
            Set C = Nothing
            Set wsPL = Nothing
            Set wb = Nothing
            
    End Sub
    

    ActiveCellC8

    All worksheets with “PL” as part of the name are now active on cell $C$8. Much easier to review!

    Tidy Up

    I’m outta here. I just landed on Boardwalk and the kid has the monopoly with a hotel – I’m pretty sure I’m bankrupt!

    BankruptFinal

    , , , , , , , , ,