How to Reproduce Your Excel Work Across Many Categories, Part 3 of 3

Part 1 of this series about analytical spawning with Excel described how Excel users often need to spawn one model or report across many related categories of data…across many regions, products, departments, or whatever. And they need to print the results or capture them for further analysis.

Part 2 of this series about Excel spawning showed simple layouts for each of the four worksheets needed to spawn the results: Control, Data, Model, and Results.

This final post in the series explains the macro that makes the whole thing work.

Click the button to download a free copy of the worksheets and macro discussed in this series.

The macro loops through each row of data in the database, enters the current count in the RowNum cell, then recalculates the workbook. When it does so, the model pulls the data for the current row from the database, analyzes it, and returns the results to the Results range.

Then the macro performs two optional tasks, as determined by values in the Control sheet:

1. If the value of Capture is TRUE, the macro copies values and formats from the Results range to the Results worksheet.
2. If the value of Print is TRUE, the macro prints the Model worksheet.

As you can see, I call the macro SpawnResults. It begins by dimensioning a number of variables that the macro will use. These variables use Hungarian notation, where “rng” indicates a range, “n” indicates an integer, “wbk” indicates a workbook, and “wks” indicates a worksheet.

The first line below freezes the screen while the program runs. When the program is done, the screen automatically unfreezes. The set statements set up various objects that the macro will use.

This line clears the prior results, if they exist. If we didn’t have the line, our new set of results could include results from a previous run.
These are three critical lines. We begin the looping process. We write the current row number to the RowNum cell in the Control sheet. And then we recalculate the workbook.

Here are the two optional tasks. The first runs if the value in the Capture cell is TRUE, and the second runs if the Print range is TRUE.

Finally, the macro loops back to process the next row number.

After you set up the macro, it will be easier to run if you set up a button to launch it from the Control sheet. To do so, first activate the Control sheet, and then in New Excel, first make sure that your Developer tab is displayed.

…To display the Developer tab in Excel 2010…

1. Click the File tab.
2. Click Options.
3. In the categories pane of the Excel Options dialog, click Customize Ribbon.
4. In the list of main tabs, select Developer.
5. Click OK to close the Options dialog box.

…To display the Developer tab in Excel 2007

1. Click the Microsoft Office Button.
2. Click Excel Options.
3. In the categories pane, click Popular.
4. Select Show Developer tab in the Ribbon.
5. Click OK to close the Options dialog box.

Then with the Developer tab visible in your Ribbon, choose Developer, Controls, Insert, Button (Form Control)

And then…

1. Draw a button similar to the one shown here.
2. Choose the macro name SpawnResults as the Macro Name in the Assign Macro dialog.
3. Choose OK.
4. The button probably will display “Button 1”. Click on the word “Button” and then press Backspace and Delete as needed to delete all text in the button. (You also should be able to select the text with your mouse and delete it, but that’s often difficult to do.)
5. Type Spawn as shown.
6. Click any cell to deactivate the button.

Excel spawning is a powerful capability that you can apply to many reports, analyses, forecasts, and models in Excel.

So after you have this sample version set up, make sure you play around with it. I’m sure you’ll see how powerful this analytical approach can be.