Many programmers wish to be able to hold objects in memory and access them from Excel worksheets. A calculation may produce a vast amount of data, including for example:

• multiple results;
• arrays that are too large to display in Excel; and
• diagnostic results.

The developer may wish to represent the entire object in Excel by a "handle" of some kind, and then use "getter" functions to inspect the data contained in the object.

For example, a function MonteCarlo(...) may produce a final value, statistical measures indicating the certainty of the value, a very large series of random numbers used by the calculation, and performance figures that show the cost of the calculation. The entire result set can be placed in an object, and exposed in Excel as a numeric handle to the object.

Thus the main function, MonteCarlo(...) returns a string, which is a handle to the result set. Various getter functions, such as MonteCarlo.Value(handle), MonteCarlo.MaxValue(handle) and MonteCarlo.AllValues(handle, start, count) can be used to show the interesting results in the worksheet.

In the picture below, cell E2 contains a formula which returns a handle:

Cells E3, E4 and E5 contain "getter" functions, which get a value from the object represented by the handle.

Cells E7:E26 contain an array formula: {=MonteCarlo.AllValues(E2,1,20)}, which retrieves the first 20 values from the object represented by the handle. The AllValues array is potentially huge, and it may be impossible (or undesirable) to show it in its entirety, so there are two extra parameters to specify the sub-range to retrieve.

As you will see in the following topics, this pattern is extremely easy to implement using XLL+ extensions.

Next: Using object handles >>