 |
|
|
|
|
|
| |
| |
|
|
|
|
| |
| |
|
|
http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Am 23.02.2013 17:17, schrieb Orchid Win7 v1:
> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
No connection from here.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
I couldn't connect either--"The connection has timed out."
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
> Am 23.02.2013 17:17, schrieb Orchid Win7 v1:
>> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
>>
>
> No connection from here.
>
It's working from here. I guess the Internet hamsters have gone back on
their wheel.
The paper talks about user-defined functions in Excel for example, if
column a has temperatures in Celcius, and you want column B to have them
in Farenheit, you can easily type "=A1*9/5+32" in B1 and copy that the
whole way down, but their ide was to create a function called, say
"C2F()" that did that and then B1 would be: ="C2F(A1)".
Of course, this is a trivial example, but if you need to revisit your
formula, modify one user-defined function would a lot simpler than
having to hunt down every cell in the spreadsheet where it might have
been used.
To Andy's point of it not catching on, The authors make it quite clear
in the abstract that they don't like the idea of a full-fledged
programming language being bolted-on to Excel. I guess they got shot
down when the Powers-That-Be(tm) decided that VBA would be the way to go.
--
/*Francois Labreque*/#local a=x+y;#local b=x+a;#local c=a+b;#macro P(F//
/* flabreque */L)polygon{5,F,F+z,L+z,L,F pigment{rgb 9}}#end union
/* @ */{P(0,a)P(a,b)P(b,c)P(2*a,2*b)P(2*b,b+c)P(b+c,<2,3>)
/* gmail.com */}camera{orthographic location<6,1.25,-6>look_at a }
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 02/23/2013 10:17 PM, Orchid Win7 v1 wrote:
> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
>
An interesting idea. However, it looks contradictory:
to have all calculations visible in cells on a function instance sheet
and to be faster than VBA function at the same time?
There is even a speed-up option in VBA to switch off cell updating.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 23/02/2013 11:01 PM, bart wrote:
> On 02/23/2013 10:17 PM, Orchid Win7 v1 wrote:
>> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
>>
>>
> An interesting idea. However, it looks contradictory:
> to have all calculations visible in cells on a function instance sheet
> and to be faster than VBA function at the same time?
> There is even a speed-up option in VBA to switch off cell updating.
I don't think this is the problem. You can view the function sheet while
you're developing it, and then turn it off when you have hundreds of
instances of that function.
I think the real problem is more... Well, let me put it this way. I just
tried to explain to my imaginary friend how this extension works. I
spent about half an hour trying to come up with a simple way to explain
it. I couldn't find one.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 23/02/2013 10:07 PM, Francois Labreque wrote:
> To Andy's point of it not catching on, The authors make it quite clear
> in the abstract that they don't like the idea of a full-fledged
> programming language being bolted-on to Excel. I guess they got shot
> down when the Powers-That-Be(tm) decided that VBA would be the way to go.
Reading the conclusion, it appears they never actually wrote the running
code. They had a PowerPoint mockup, a demo written in Flash, and so on.
But they never actually implemented a real Excel extension that really
did work.
Also, the report makes quite clear that VBA already exists today. The
problem is, the number of people who understand spreadsheets vastly
outnumbers the number of people who can work something as abstract as
VBA (or any other purely text-based language). Hence the motivation for
this paper.
I guess MS figured there was no market for it. Which is not an
unreasonable conclusion...
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 23/02/2013 04:17 PM, Orchid Win7 v1 wrote:
> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
For those unable to read the paper, a short summary...
The number of people who use spreadsheets dwarfs the number of
professional computer programmers. And yet, some of these spreadsheets
are extremely complex, sophisticated constructions. In the main, these
are built not by trained programmers, but by accountants, salesmen,
purchasing associates, and anybody else who has a need to do complicated
numerical calculations.
A complex spreadsheet can be considered to be a "program", the formulas
in the cells being its "source code". And yet, accountants don't
generally sit down and learn to write code in C, C++, Java, Perl,
Python, R, or even something like Mathematica which is heavily
problem-oriented rather than machine-oriented. Why is that?
Arguably a spreadsheet is easier to comprehend. You type in a formula
and it makes a number. You can immediately *see* what is happening. In
traditional programming languages, you write a bunch of text, press go,
and maybe some kind of result is produced. It's far less visual, even if
you have a REPL to use. You don't literally *see* all the intermediate
calculation steps, unless you fire up a purpose-built debugger.
Which a spreadsheet, there is no separate coding and debugging phase.
You just have a grid of cells, which you can modify at will, at any
time, for any reason. This makes it easier to gradually construct a
sophisticated application from humble beginnings.
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 *only* way to repeat a calculation on more than one
input is to literally copy and paste it. (And most spreadsheets have at
least moderately sophisticated mechanisms for doing intelligent copy and
pasting.)
As a trained computer programmer, this makes me gasp in horror. It is a
clear violation of the DRY principle. But as an accountant, or a
quantity surveyor, you're not going to know or care about such things.
You're just trying to produce this month's figures (or whatever).
There *is* of course a facility in Excel to define functions. However,
this requires you to learn Visual Basic, a completely different
programming language, with radically different semantics. It's a bit
like the way C lets you write macros, but in a totally different
language (CPP), whereas Lisp lets you write macros in Lisp.
So, the main thrust of the paper is "what if you could write Excel
functions in Excel?"
The mechanism they chose is to let you define a special "function
worksheet". Certain cells are the function's input, and a certain cell
is the function's output. Every time you call that function, notionally
a new copy of this function worksheet is dynamically created with the
appropriate cells filled in, and whatever ends up in the output cell is
copied back to the formula that mentions the function.
That's the gist of the idea. Naturally, if you edit one instance of the
function worksheet, the change affects *all* instances, automatically.
And therein lies the payoff.
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.
Problem #2 is "how do we support recursion?" They chose the solution
"you don't". Which seems reasonable; how many people actually want to
use that feature? Do we really want to complicate the interface further
just to make those guys happy?
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.
In short, the solution is to UTTERLY BREAK THE SPREADSHEET MODEL! This
is the least convincing part of the paper for me. The cost of the user
learning this completely new mechanism seems too high compared to the
modest payoff of handling ranges of different sizes. I think a better
approach could be found.
So there we are. There's lots of screenshots, which gives the
*impression* that they had a working product. In 2003. But in the
conclusion they explain that these are simply mockups and simulations,
not a real, usable product. They make it clear that they are in
discussions with Microsoft about making this feature available in the
real commercial Excel product (Simon PJ works at Microsoft Research
Cambridge, after all). But, clearly, this never came to anything.
I think this could very easily be one of those features that nobody
knows about and nobody ever uses, unless you made it *extremely* simple
to comprehend and to use. For example, the vast majority of people who
use Word apparently have no idea what the hell a "mail merge" is, or
what Word has the capability to do such a thing. (Then again, most
people probably don't *need* it either...)
I think it would be very cool to define functions in Excel, but I doubt
Excel will be the one to pioneer this. I'm also not sure how much
anybody would use it...
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
Shame. Actually IME I find a lot of people are able to write VBA macros,
yet they purposely go to huge lengths to use only "native" Excel just to
avoid the dreaded "this sheet has macros" warning when opened. In fact
it turned into a bit of a competition at my old place; "I challenge you
to implement this in Excel without a macro". It's surprising how
versatile it is without VBA.
Also I just learned about the F4 key in Excel the other day (when used
while editing functions) - I've been wanting that functionality for
years but never bothered to look up if there was a keyboard shortcut...
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
>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.
>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.
>Problem #2 is "how do we support recursion?" They chose the solution
>"you don't". Which seems reasonable; how many people actually want to
>use that feature? Do we really want to complicate the interface further
>just to make those guys happy?
The function definition can use the standard Excel =IF(,,) function
to make a conditional result to stop the recursion.
The rest would be provided by the standard mechanism of function calls.
>
>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.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
|
|
