Visual Basic

You are currently browsing the archive for the Visual Basic category.

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

 

Lets revisit Summing Element-Wise Minimum once again. To recap:

“Suppose you plan to buy several computer parts from either BestBuy or Circuit City. The two stores have different prices for each product. If you buy from one store exclusively, you won’t get the best deals possible. To optimize the value of your hard-earned dollar, you want to find the least amount of money necessary to buy one of each part.”

Lets suppose you are not convinced you have truly optimized your hard-earned dollar. Therefore, you decide to add prices for Office Depot, Duane Reade and Walmart.

Download the Sample File

An easy way to solve this problem would be to create a new column which utilizes the MIN() function. But, since you believe in optimization you want it ALL in one single cell. What are your options? I guess you can start creating a crazy array that will be difficult to understand such as the following example which determines the cheapest total price between 3 stores (arrays):

{=SUM(IF([array 1]<[array 2],IF([array 1]<[array 3],[array 1],[array 3]),IF([array 2]<[array 3],[array 2],[array 3])))}

Looks… um… um… right. So outside of adding a column and creating a crazy array formula, what other option is available? Enter Microsoft Visual Basic! It seems that many Excel based users may go pretty far in their careers without ever understanding the super hyper intimidating but useful tool. (Excel 2003 keyboard shortcuts provided)

  • Tools > Macro > Macro… (Alt + F8) and
  • Tools > Macro > Visual Basic Editor (Alt + F11).

So now, wouldn’t it be nice if we could just write a function and refer to a bunch of cells and get exactly what we want? Guess what? With Visual Basic, you can… With the Visual Basic code below, you can add as many stores as you like while solving for the optimal price.  It is important to note that everyone has their own programming style, you can feel a glimpse of my style with the code below.

Download the Sample File

I certainly encourage you to download the sample file because I have provided comments within the Visual Basic Module. That said, I do want to make a few quick notes.

  1. A Visual Basic function (if non-Private) can be called from the Excel Spreadsheet directly. The spreadsheet contains the formula, =minSumItNow([Array 1 ... Array N]).
  2. Visual Basic Function differs from Sub because Function must return a value. The value is set by assigning the Function name.
  3. [Array Name].Rows.Count and [Array Name].Columns.Count are very useful. This allows the user to understand the size (vertically and horizontally) of arrays. This is useful because users do like to include a range of cells as an input. (For Each is useful as well and will be discussed in a later post)
  4. Visual Basic might seem like more work to learn, but the rewards are plentiful. Learn Visual Basic to:
    • Impress your colleagues.
    • Work more efficiently.
    • Automate tedious tasks.
    • Unlock the true power of Excel.

Download the Sample File