It's 2005 and it should be possible to do in Excel what has long been
possible to do in every other programming language - namely create *reusable*
funtions (worksheet regions). Rather than create these functions with code,
it should be possible to do it as a spreadsheet. In the dream world, this
capability would make it so that people could take thier handy spreadsheet
modules, convert them to worksheet modules, package them, and have them added
to a user's function/module sheet palette. Need a pareto chart? Just drag
the worksheet module off of the palette on to the work sheet area, connect up
the input ranges, and presto - a chart is nested in your very own worksheet
area right where you need it.

The idea is follow the paradigm of the Pivot Table which overlays a range on
a spreadsheet. This new region, a sub sheet or worksheet module, would have
an input bar, an output bar, and a worksheet region in between. Preferably,
the input bars could be horizontal or vertically oriented and transposable.
The contents in the worksheet regions would be written in terms of the input
bar values or ranges. In otherwords, if the input bar has a regions
specified as input, then the formulas in the worksheet region would access
that region with an implicit INDIRECT() call.

In fact, using INDIRECT(), it is possible to mimic this effect. However,
the region is not friendly in terms of reuse because of problems with
absolute and relative references. What would be needed is a mechansim
whereby absolute references can be made absolute only with respect to the
worksheet frame. One approach would be to use scoped absolute references (
SubSheet::$C$2:$D$4).

In terms of layout, consider the following:

===[UserFile.xls]=======
//
/// User's existing-and-ever-so-carefully-developed worksheet area
//
// User's precions data is in cells B5:V27
//
// User has plopped the Histogram Module here and selected thier data
region in the input bar.
//
*****SUBSHEET HISTOGRAM *********
*[INPUT RANGE: HistData = B5:V27]
*
* Cell: MaxColors = 256
*
* /// Calculate Bins; =Count(HistData) , =if(temp HISTOGRAM::MaxColors,
.... )etc.
*
* // Count items in Bins; =SumIf(HistData<Bin1Lower, HistData, ....)
*
* // Embedded Chart of Histogram
*
*[OUTPUT RANGE: none]
//
//// User's worksheet area continued...
//




----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc