Automatic Goal Seek

This article demonstrates a simple VBA method to utilize the Goal Seek tool automatically, similar to a native Excel formula.

Goal Seek is an Excel built-in tool that uses numeric approximation methods to “back solve” for an input value to a system of formulas when given a desired output value.  If you are not familiar with Goal Seek, many tutorials online can guide you to using this tool.  I hate it, and try to avoid it at all costs… but like everything else that I hate, there come times when I just have to face it like a man.

The problem?  For one, it’s not transparent, and we can’t exactly tell what Excel did in the background.  But more importantly, it’s a pain to apply and to udpate.  While all other formulas can automatically re-calculate upon changes to the input values, Goal Seek requires you to click on the menu to bring up the dialog box, then fill out three input boxes manually.

See the spreadsheet above.  Everything in blue are input values to this dynamic model, which can affect the results.  We want to set E14 to a value specified in D3, so these two cells correspond to the first two parameters of Goal Seek (“Set cell” and “To value“).  As an alternative, we put the difference between the two in F6, and set this cell to 0 (x=y is equivalent to x-y=0).  This approach will slightly simplify the coding later on, and is an easier check for the user.

Download the Sample File

The “Necessary Adjustment” in D6 plays an accomondating role in adjusting each of the values so that the final calculation reaches target.  For Goal Seek, it is the third parameter “By changing cell“.  Initially we have no idea what this adjustment should be, and leave it at 0.  At this time, F6 is showing 0.8%, indicating that the necessary criteria haven’t been fulfilled.

Let’s do a Goal Seek now – set F6 to 0 by changing D6.  If you record this into a macro, say Macro1, you can quickly repeat it by using an assigned shortcut key stroke, or hook it up with a command button for an easy UI.  A lot of people do exactly this as an enhancement to the otherwise awkward model, but it’s not good enough.  Let’s automate it.

Press Ctrl+F11 to bring up the Visual Basic Editor to see the embedding code to Macro1, and you’ll see this line of code:

Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)

Fair enough.  It’s almost in plain English, and shouldn’t take an VBA expert to understand.  The next step is to copy and paste this line out of Macro1, to somewhere more automatically executable.

Browse to “Sheet1” under “Microsoft Excel Objects“.  Either type or use the drop-down box to create the following VBA event:

 Private Sub Worksheet_Calculate()
   
 End Sub

This subroutine will be called every time the “calculate” event is triggered on this worksheet.  Depending on whether you set the calculation to Automatic or Manual in Tools->Options, this happens when any formula-based cell changes value to a new input value.  The idea is to paste the Goal Seek code here so it will be called whenever F6 may change; however, let’s keep it in a separate function for the sake of good programming practice:

Private Sub Worksheet_Calculate()
   CheckGoalSeek
End Sub

Private Sub CheckGoalSeek()
   Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)
End Sub

In the Visual Basic editor, it should look like this:

Now give it a try!  Change any of the blue input values and you’ll see Goal Seek working by itself to come up with the new adjustment values.  Awesome!  We’re done now in terms of automating a tedious task.

Note: Always save your work before you test out the macro.  depending on the version of your Excel, the repetitive call to Goal Seek may result in a run-time error.  However, the next part of this code will solve the problem.

Have you noticed how slow this enhancement has made your model?  Maybe not, if you have a relatively new computer or a small workbook like the sample file.  But if you add some temporary debugging code to the function, you’ll notice how many times your function is (unnecessarily) being triggered when you change any of the input values:

Private Sub CheckGoalSeek()
   Range(“F3″).Value = Range(“F3″).Value + 1
   Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)
End Sub

Give it another run and, hey:

You just changed one input, and Goal Seek ran close to 2,000 times!  Why???

The subroutine Worksheet_Calculate is triggered every time any function calculates a new value.  There are six cells (in column E as well as cell F6) that depend on the changing cell D6.  As Goal Seek cycles through possible values as the input, all these six cells have to update – and in turn call up more instances of Goal Seek.

Imagine you want to paint a wall red.  Every time you see that the wall isn’t entirely red, you call up a painter to do the job.  As soon as the first painter gets to work, you realize that it’s still not entirely red, so you call up another one.  Even when you’ve got 30 painters to simultaneously working on the wall (if that’s physically possible), there might still be a white spot remaining and you make yet another phone call… chances are, the job would be complete before the last five painters arrive on site.  This algorithm gets the job done, but heck, is super inefficient.  Add a little check to the process and avoid ever calling more than one painter!

Private Sub CheckGoalSeek()
   Static isWorking As Boolean

   If Round(Range(“F6″).Value, 6) <> 0 And Not isWorking Then
      isWorking = True
      Range(“F3″).Value = Range(“F3″).Value + 1
      Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)
      isWorking = False
   End If
End Sub

Adding in the static variable helps flagging whether the Goal Seek function has been employed.  The first time this function is called, it raises the flag and will not release it until the full iteration of Goal Seek completes.  During this time, this subroutine would still be called 2,000 instances, but it would simply bypass all the code and exit.  Try again and F3 increments only once per change of input values, indicating that the Goal Seek isn’t running wild to waste your computer’s resources.  That’s what we wanted, and now we can remove that debugging code.

The first part of the If statement checks whether the solution already exists – i.e. if Worksheet_Calculate is being triggered by some formula updates that are completely irrelevant to this system.  If so, there’s no need to call Goal Seek at all.

Here’s another catch: if you want a robust model, you’ve gotta consider any potential for error.  How about an invalid input value?  Keeping the target overall raise at 5% and changing the max raise to 0% is for sure an impossible scenario, and Goal Seek goes crazy as follows:

We realize that wasn’t very smart and change the max back to 6%… and geez it doesn’t work!  The adjustment factor had been thrown too far off anything reasonable, and now Goal Seek doesn’t know what to do with such an awkward initial guess.  Our model is stuck, and that ain’t cool.  A quick way to fix this is to reset the initial guess every time we call Goal Seek:

Private Sub CheckGoalSeek()
   Static isWorking As Boolean

   If Round(Range(“F6″).Value, 6) <> 0 And Not isWorking Then
      isWorking = True
      Range(“D6″).Value = 0
      Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)
      isWorking = False
   End If
End Sub

It’s a slight waste of resources if educated and reasonable input values can be expected at all times, but definitely a worthy investment if you want to prevent ever getting “stuck”.

Lastly, it’s dangerous to use absolute cell references in macros.  If columns or rows are added or removed, the key cells would have changed locations (from D6 to D7, for example) and the macro will get confused!  The best practice is to name the cells and refer to them by name, like the following:

Private Sub CheckGoalSeek()
   Static isWorking As Boolean

   If Round(Range(“GoalSeekCell“).Value, 6) <> 0 And Not isWorking Then
      isWorking = True
      Range(“ByChangingCell“).Value = 0
      Range(“GoalSeekCell“).GoalSeek Goal:=0, ChangingCell:=.Range(“ByChangingCell“)
      isWorking = False
   End If
End Sub

Now we have a robust macro that automatically updates Goal Seek!  Enjoy!

Download the Sample File

 

yay goal seek! put in a tutorial for that menu thingie. that’s pretty shweet. ;)

Thanks for a great solution! However, this removes the ability to use the Undo command. Do you know of a way to have the GoalSeek routine run automatically without wiping out Undo?

man, people really love goal seek.. you see the stats behind this puppy? haha, probably should have other “BASIC” excel/vb stuff if we want to increase hits.

Peter,

This is EXACTLY what I was looking for, so thanks for that.

My problem now is that the sheet that contains the Goal Seek code contains some input fields that I want several unspecified users to be able to change, and a load of cells that I don’t want them to change.

It seems, however, that if I use the standard Tools / Protect Worksheet, as I would usually do, that this stops the Goal Seek code from working too, as it appears it doesn’t have permission to change the cells it needs to.

Any suggestions on how to fix this?

Thanks,

Mark

this is great; great information

Hi, I am having a problem with this code unfortunately. When I run

Private Sub CheckGoalSeek()
Range(”F6″).GoalSeek Goal:=0, ChangingCell:=Range(”D6″)
End Sub

– it works fine, except of course, it does not auto-calculate.

However, when I add in the worksheet_calculate function, i.e.

Private Sub Worksheet_Calculate()
CheckGoalSeek
End Sub

Private Sub CheckGoalSeek()
Range(”F6″).GoalSeek Goal:=0, ChangingCell:=Range(”D6″)
End Sub

The whole thing just stops working, even when I just run the checkgoalseek() function alone (Which has not been touched when I added in worksheet_calculate).

I immediately get the error message:
“Run-time error ‘-2147417848 (80010108)’:

Method ‘GoalSeek’ of object ‘Range’ failed

The frustrating thing is, everything is working fine until I add in worksheet_calculate, and then once I do the whole thing just falls apart, including the previously fine checkgoalseek() function!

Please help!!
Thanks so much.

This is very helpful. I have a question. I have a workbook with multiple worksheets. I tried to use the above sub in each of the worksheets. The problem I face is when I calculate (manual, Shift + F9), VBA runs all the subs in all the worksheets, i.e..if sheet1 and sheet2 have the same above sub code and I update sheet2, its executes the sub in both sheet 1 and sheet 2. Appreciate any help/suggestions? Thanks a lot

This looks a wonderful idea but I have typed in the code as accurately as I can but when I try to run the program I get the error statement “End If without block If”
What am I doing wrong?

Also in the line ‘If Round(Range(“F6″).Value,6)…………..’
What does the ’6′ represent?

Cool! Excellent article!

maaaan, can you imagine how long i’ve been searching for this?!?!
thanks A LOT!

This is truly helpful. Thanks for taking the time to share your approach.
Sincerely, UBS

Thank you ! wonderful.

On the last code part, I think there is an extra “point” that should be deleted before “range”

ChangingCell:=.Range(”ByChangingCell“)
should be
ChangingCell:=Range(”ByChangingCell“)

This works fine, but if you try to build it into an Excel user-defined function, doesn’t work so well. That would be handier than having it auto calculate or even worse, have to use that stupid dialogue box. With a user-defined function, all you have to do is type the function in a cell, pick out the two references (value to change, value to converge to), and let her rip. Could also have many instances on a sheet. Anyway, my attempt, but doesn’t work (although it doesn’t crash, either!).

Function CheckGoalSeek2(GoalSeek, ByChanging)
Dim GoalSeekCell: Dim ByChangingCell

Static isWorking As Boolean
GoalSeekCell = GoalSeek.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)
ByChangingCell = ByChanging.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)

With ActiveSheet
If Round(.Range(GoalSeekCell).Value, 4) 0 And Not isWorking Then ’rounds to four places, and checks for errors
isWorking = True
.Range(ByChangingCell).Value = 0
.Range(GoalSeekCell).GoalSeek Goal:=0, ChangingCell:=.Range(ByChangingCell) ‘doesn’t use Excel named ranges, reference instead
isWorking = False
End If
End With
Debug.Print isWorking; ” “; ByChangingCell; ” “; GoalSeekCell
CheckGoalSeek2 = “finished”
End Function

Private Function Round(Number, numb_digits)
Round = Application.WorksheetFunction.Round(Number, numb_digits)
End Function

hi all:

nice write-up; thanks

please bear with my ignorance, but at the very end where yo get rid of the absolute cell references, and change F6 to GoalSeekCell and D6 to ByChangingCell … i don’t see how Excel knows that, e.g., GoalSeekCell is F6, etc.

i do see in the worksheet proper, if i highlight F6, up in the formula bar area (a style?) it says GoalSeekCell …

can you explain?

again, thanks

my bad (been a loooong week)

re-read the narrative AND looked again at the file proper … simple named ranges

sorry about that

Hey Peter, I used this article in order to solve a problem of mine. here is the code..

Private Sub Worksheet_Calculate()
CheckGoalSeek
End Sub

Private Sub CheckGoalSeek()
Static isWorking As Boolean

If Round(Range(“GoalSeekCell”).Value, 5) 0 And Not isWorking Then
isWorking = True
Dim X As String
X = Range(“B311″).Value
Range(“ByChangingCell”).Value = 0
Range(“GoalSeekCell”).GoalSeek Goal:=X, ChangingCell:=Range(“ByChangingCell”)
isWorking = False
Range(“G12:G310″).Select
Selection.Copy
Range(“B12″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
End Sub

BUT I get a runtime error 80010108…..

any ideas???

I have this function:

f(x) = (1 / root(2 * PI)) * exp((-1 / 2) * x^2) – (PI /root(3)) * exp((-PI /root(3)) * x) / (1 + exp((-PI /root(3)) * x))

I know that f(x) = 0 has two roots, say x1 and x2.

How can I use Excel (gol seek or macro) to find x1 and x2?

Thanks a lot,

Marco
(elemento82@gmail.com)

Thank you for describing how to do this in detail. The screenshot with the subroutine in Sheet1, and the addition of the static isWorking variable, were very helpful.

How to change the macros for several goal seek in one sheet?

To whom it may concern

Please can you assist in automating the goal seek function on the following spreadsheet – i am not familiar with Visual Basics and can’t manage to do this as per your tutorial on my own. I am completely lost.

I’ve uploaded the spreadsheet to my server, the link is:

http://www.reflectbrokers.com/FNA – INCOME NEEDS.xlsx

I will really appreciate it if you guys could work your magic (goal seek is shown on the page)

Thank you very much.

Kind Regards,
Ettienne le Roux