On 25/02/2013 12:53 PM, bart wrote:
>  >When you start to look at a spreadsheet as a program,
>  >you realise that as a programming language,
>  >it's pretty damned weak.
>  >In particular, it is impossible to define new data types,
>  >and it has impossible to define subroutines.
>
> The lack of subroutines is the main concern of the paper,
> but a new data types (structures) can be simulated easily in Excel.
> Just put all the fields in the block of cells - and you have it.
> The one can access it by a reference.

Or, you /could/ do that if the indirect cell lookup functions weren't so 
hard to use.

>  >Trouble is... Well, actually there's a whole *host*
>  >of problems. Problem
>  >#1 is that your new function might be called twenty billion times.
>  >You're not going to display twenty billion worksheet tabs!
>  >The solution
>  >they came up with as that only one "instance"
>  >of each function sheet can
>  >be visible at once.
>
> It would be probably better to call it not an instance,
> but a function definition.
> The user's concern would be only to copy a working code
> to the function sheet and somehow indicate the locations
> of the parameters and the result.
> The extension (could be in a form of xll) should then
> take care of some kind of translation of the algorithm,
> derived from the function sheet, into, say,
> a sequence of calls and store it in the memory.
> The biggest problem is to make Excel to recognize
> the names of the new functions.
> But just for the working demonstration they could use a single
> function, say, ufun("function-sheet-name",params) which return a matrix.

They say the idea is to click on a cell and say "extract as function", 
and it'll generate the appropriate function sheet, and replace the 
original cell with a reference to that. Which *sounds* very nice... but 
they never actually implemented it.

>  >Problem #3 is how to support functions that take *ranges* of cells as
>  >input. Their solution was to make a cell matrix a first-class value,
>  >so that a single cell can contain an entire matrix.
>  >And since a matrix is made up of cells,
>  >matrices can be nested arbitrarily deep.
>
> This is not a big problem after all:
> Excel functions do support ranges
> (and expressions that operate on ranges)
> as their arguments.

Yeah, they chose to not allow user-defined functions to take cell ranges 
as input - which would seem the /logical/ solution to me. The trouble 
is, each function input becomes a cell on the function sheet. It's 
notoriously hard to deal with variable table sizes in a spreadsheet.

Another possibility would be to define each cell-range input as an 
entire virtual sheet in its own right - but designing a nice GUI for 
that would be difficult...

Post a reply to this message