Recently I needed to create a stop and resume process for an application at work. This is probably pretty basic stuff for most developers but took me some time to figure out. To do so, I first added a "Show Form" button to a worksheet. When the Show Form button is hit, it calls a userform that contains four buttons that control a simple looping procedure. The first button starts the procedure, the second stops it, the third button resumes it, and the fourth exits the procedure altogether. As the procedure loops through its values, those values are recorded in cell A1.

The procedure works by using "DoEvents" to read the value of the iStop variable. When the Go button is hit, the iStop variable is set to False. When the Stop button is hit, iStop is set to True and the procedure is forced to exit the loop.

The userform is below:

Option Explicit
Public iLoop As Integer
Public iStop As Boolean
Public Rng As Range

Sub DoLoop()
    Do Until iLoop = 15000
        Rng.Value = iLoop
        iLoop = iLoop + 1
        DoEvents
        If iStop = True Then Exit Sub
    Loop
    Rng.Value = 0
    Unload Me
End Sub

The code tied to the Go button is:

Private Sub btnGo_Click()
    iStop = False
    Set Rng = ActiveSheet.Range("A1")
    iLoop = 1
    Call DoLoop
End Sub

The code tied to the Stop button is:

Private Sub btnStop_Click()
    iStop = True
End Sub

The code tied to the Resume button is:

Private Sub btnResume_Click()
    iStop = False
    Call DoLoop
End Sub

Aned finally the code tied to the Quit button is:

Private Sub btnQuit_Click()
    iStop = True
    Rng.Value = 0
    Unload Me
End Sub

One of my frustrations with Excel's Goal Seeking feature is that you need to hand enter the "To value" input as opposed to giving you the option to tie the value directly to a value in a cell (see the screenshot below).

Recently I created a simple tool which allows the user to get around this limitation. Screenshots of the tool appear below. Note the two option buttons that appear on the bottom right-side of the tool.

In the first state shown below, the user can tie a cell value to the to the second input box. In this mode a RefEdit control is used to capture the cell value.

In the second state, the tool works very similar to Excel's built-in feature. In this mode an input box is used to capture the cell value.

Several buttons appear at the bottom of the tool. After entering the three required inputs, hit the Apply button to start the goal seeking process. If the tool can't find an answer quickly, you can use the Stop button to halt the procedure. After the tool has returned a result, the Undo button can be used to put the values back to their original state.

The tool is currently designed to work with Excel 2003 and has not been tested by anyone other than myself. It can be used as-is or converted to an add-in. If you would like the file, feel free to download it via the link below.

Many people see the markets moving up or down but have no idea of risk or rate of return of their personal investments. Andreas Steiner's web site provides a wealth of information on investment performance and many Excel templates that you can download for free. Keep in mind that that header on the downloads page states The downloads provided on this page are free and designed for educational purposes. They are meant to be illustrations to be used at your own risk, not productive applications.

There are a number of Excel book excerpts available around the web:

• Links to excerpts from four of John Walkenbach's 2007 books are available at The Spreadsheet Page.
• From the book titled Microsoft Office Excel 2003 Programming Inside Out, the chapter titled Creating Advanced User Forms is available.
• Several chapters are available from the book titled Microsoft Excel Data Analysis and Business Modeling. They are Determine the Optimal Product Mix with Solver, Introduction to optimization with the Excel Solver Tool, and Pick Your Fantasy Football Team with Solver.
• From the book titled Excel 2007 VBA Programmer's Reference, the chapter titled Primer in Excel VBA is available via Wiley.
• Links to excerpts from several chapters of the book titled Professional Excel Development are available at Stephen Bullen's Office Automation site. The chapter on RibbonX might be of special interest given the changes in Excel 2007.

Below are a few blog posts that I found especially useful:

• Jon Peltier recently posted articles titled 9 Steps to Simpler Chart Formatting and Overlapped Bar Chart - Thinner Bars in Front.
• John Walkenbach recently posted an article dealing with Color Contrast Functions with a tip titled Quantifying Color Choices. I found this post to be useful because many of the people I work with tend to use multiple background colors to highlight different types of information. As they use more and more colors, their spreadsheets become more and more distracting and difficult to read. On top of that, color printers don't always replicate the spreadsheet colors as intended so your printed copies look totally different from what you see on the screen.

MSN Money and Yahoo Finance are two good resources for those that like to follow stock trends by creating their own tables or charts in Excel. To retrieve a history of the performance of a selected stock, start at the Home page for either site. Enter a stock symbol into the "Get Quotes" input box. I started with Berkshire Hathoway's Class A shares, which closed yesterday at $115,500 / share (the highest price of any U.S. stock).

After entering the symbol into MSN Money, you get a page that contains the following:

The Yahoo Finance page looks similar to the MSN Money page:

Both sites have sidebars on the left side of their pages that allow you to access historical data. In the MSN Money sidebar, look under Charts - Historical. When you click on that link you get a chart that shows the history for the time period you selected. To retrieve the chart data, scroll down to the bottom of the page and click on the "View price history with dividends / splits" link.

In Yahoo Finance, look under Quotes - Historical Prices. Yahoo Finance offers a greater amount of price detail for download than MSN Money does. For example, you can get historical prices by day, week, or month as well as select your own time periods. The screenshot below shows the daily level of detail that's available.

Don't worry if you can't afford the $115,500 / share Class A stock. You can always pick up a few shares of the Class B stock for approximately 1/30 of the price of the Class A shares. Yesterday the Class B shares closed at $3,850.

As reported by the Dallas Morning News and CNN, 375 teachers were laid off, 450 reassigned to other schools, and 40 counselors and assistant principals were let go due to a massive miscalculation in the budget.

You originally project a $64 million shortfall but now it's $84 million? Where did the other $20 million come from?

I find it hard to believe that the finance and accounting staff would not have access to some type of salary data. And you're telling me that, after "X" amount of years that the school district has been in existence, we're just now discovering that we have systems in place that don't talk to each other? That's why we can't get the right salary data? If that's true, we seemed to get by with no problems in the past using these same systems - what's different now?

That's precisely why finance and accounting staff should build the budgets. The administrative function is to review and approve the budgets.

It's my guess that the finance and accounting staff had very little to do with building the budgets. And after they were created, the finance and accounting staff we're probably not asked to do a high-level review for reasonableness or a technical review for accuracy. After all, simple variance analysis techniques would have revealed a shortfall of this magnitude.

As seems to be more and more the norm these days, good hardworking people (in this case - the teachers and others) are punished due to the incompetence of management. It's management's job to put processes in place so that these types of things don't occur. But rather than management loosing their jobs, I'm betting that after it's all over and done they'll do just fine.

A chart that I have to show each month combines a trend of current results with a forecast. As an example, for the month of May 2008 the chart would look similar to this:

Fast-forwarding a few months, in August 2008 it would look similar to this:

By comparing the two examples, you can see that the "Actual" line (red line) expands each month reflecting current results. Conversely, the "Plan" line (blue line) contracts as the year moves forward. A marker and label indicating the Actual and Plan net operating income is always shown for the current month.

Although the chart appears to be Line chart, it really is a combination Line-XY (Scatter) chart. The chart can be automated so that it automatically updates as the most current data is entered. To do so, the screenshot below shows the setup:

The Actual and Plan data are entered into the range C4:D15. The Chart Source and Data Labels Source ranges contain formulas that allow the chart to automatically update. At the start of the year enter your Plan data into the range D4:D15. At the end of each month the results are entered into the range C4:C15.

There are six different formulas that drive the chart. The first two formulas drive the Actual and Plan lines.

The first formula, which drives the Actual series, is entered into cell F4 and then copied through the range F4:F15. The formula is:

=IF(C4,C4,NA())

The second formula, which drives the Plan series, is entered into cell G4 and then copied through the range G4:G15. The formula is:

=IF(COUNT($C$4:C4)=COUNT($C$4:$C$15),D4,IF(C4,NA(),D4))

The remaining four formulas drive the points for the current month and use an XY (Scatter) chart as their source. The following formulas appear in the following cells:

Actual Label Sources

Cell J5: =COUNT($C$4:$C$15)

Cell J6: =OFFSET($C$3,COUNT($C$4:$C$15),0)

Plan Label Sources

Cell J10: =COUNT($C$4:$C$15)

Cell J11: =OFFSET($D$3,COUNT($C$4:$C$15),0)

Finally, the chart source looks like this:

Line Chart Sources

Actual Line Source

Plan Line Source

XY Chart Sources

Actual Marker and Label

Plan Marker and Label