# Excel Workbook

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.

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:

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()

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()

Private Sub CheckGoalSeek()

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()

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

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

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

Range( ByChangingCell ).Value = 0

Range( GoalSeekCell ).GoalSeek Goal:=0, ChangingCell:=.Range( ByChangingCell )

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