Uh-oh: a 'partnership with the Excel team to transform spreadsheet formulas into a full-fledged programming language'
LAMBDA: The ultimate Excel worksheet function
"allow cells to contain first-class records, including entities linked to external data"
"dynamic arrays allow ordinary formulas to compute whole arrays that spill into adjacent cells"
With a wee debate (obliquely related to recent threads here) as to whether Excel's VBA is Turing complete.
One of the authors, Simon P-J is 'Mr. Haskell'. He's long had a side-interest in breaking Excel free of its chains.
Quote from AntC on February 20, 2021, 11:07 amLAMBDA: The ultimate Excel worksheet function
"allow cells to contain first-class records, including entities linked to external data"
"dynamic arrays allow ordinary formulas to compute whole arrays that spill into adjacent cells"
With a wee debate (obliquely related to recent threads here) as to whether Excel's VBA is Turing complete.
One of the authors, Simon P-J is 'Mr. Haskell'. He's long had a side-interest in breaking Excel free of its chains.
Excel's VBA is certainly Turing complete. It's just standard VBA with the Excel DOM made visible.
I'm all for research and whatnot, but unless I missed something -- I did skim it very quickly -- it seems unaware that VBA has been integrated into Excel for the last few decades, and gives you full Excel programmability. I kind of appreciate the intent behind turning the Excel expression language into a full programming language, I guess, but...
I think it's at heart a great idea.
Yes, VBA is T-C, but it's mostly about manipulating spreadsheets and other objects, not defining formulae.And it brings security issues.
I would very much like to define a formula to replace an expression, rather than copy and dupe an expression, and do it safely. As always, the devil is in the details, and this is a devil we do not know. But it sounds like we will.
I know nothing about Google apps so I ask the question: does their spreadsheet do anything like this?
Quote from dandl on February 21, 2021, 5:06 amYes, VBA is T-C, but it's mostly about manipulating spreadsheets and other objects, not defining formulae.
I've mainly used it to define formulae, but it's handy for both defining formulae and general spreadsheet manipulation.
And it brings security issues.
Making the Excel expression language Turing Complete doesn't?
If it's restricted in capability, then VBA will still be needed. If it's as powerful as VBA, then it will have the same security issues.
There may be a halfway point that makes it more programmatically powerful than it is now but not so powerful that it's a security risk, but that may be the same point where it's more programmatically powerful than it is now but not so powerful that it's worth using. It'll be interesting to see what it turns into.
Quote from dandl on February 21, 2021, 5:06 amI would very much like to define a formula to replace an expression, rather than copy and dupe an expression, and do it safely. As always, the devil is in the details, and this is a devil we do not know. But it sounds like we will.
I know nothing about Google apps so I ask the question: does their spreadsheet do anything like this?
I looked at Google spreadsheet quite a few years ago, it seemed pretty much a clone of the frequently-used bits of Excel. In particular, it didn't seem to support 'Array formulas' -- which seems to be what this new gizmo is exploiting.
'Array formulas' (I looked at them a long time ago) seemed to be a really good idea that got half-developed then abandoned. I used them to (for example) reverse a string -- which the blurb claims you couldn't previously do. But 'Array formulas' didn't support the 'spillover' idea, which meant you had to know the sizes of all your arrays in advance. (Or in the case of reversing a string, allow them to be much bigger than you 'could possibly ever need' (ha!), then mentally ignore trailing spaces to pad the string -- which turned into leading spaces when reversed.)
I asked 'power users' around me about 'Array formulas'. They didn't like/didn't understand them -- mostly they were Accountants, I guess they didn't like the idea of treating a block of cells as an entity, with corresponding offsetting within blocks. Instead they used all sorts of cross-match totals to catch the formula in one cell getting its relative references out of synch with those it was supposed to be the same as.
Quote from Dave Voorhis on February 21, 2021, 9:38 amQuote from dandl on February 21, 2021, 5:06 amYes, VBA is T-C, but it's mostly about manipulating spreadsheets and other objects, not defining formulae.
I've mainly used it to define formulae, but it's handy for both defining formulae and general spreadsheet manipulation.
And it brings security issues.
Making the Excel expression language Turing Complete doesn't?
If it's restricted in capability, then VBA will still be needed. If it's as powerful as VBA, then it will have the same security issues.
The blurb for this new gizmo explicitly excludes VBA. That is, when they say formulas are not Turing-complete, that means without resorting to VBA. But then their grounds seem pretty specious: Excel doesn't have infinite-sized spreadsheets. True, but no actual computer has infinite-sized memory.
There may be a halfway point that makes it more programmatically powerful than it is now but not so powerful that it's a security risk, but that may be the same point where it's more programmatically powerful than it is now but not so powerful that it's worth using. It'll be interesting to see what it turns into.
The 'New functions' here look suspiciously like they're trying to implement relational operators. (You could already do simple joins with LOOKUP() and friends.)
In which case the 'spillover' business is going to be really useful: each sheet represents a relation (real or virtual).
Quote from AntC on February 21, 2021, 10:05 amQuote from Dave Voorhis on February 21, 2021, 9:38 amQuote from dandl on February 21, 2021, 5:06 amYes, VBA is T-C, but it's mostly about manipulating spreadsheets and other objects, not defining formulae.
I've mainly used it to define formulae, but it's handy for both defining formulae and general spreadsheet manipulation.
And it brings security issues.
Making the Excel expression language Turing Complete doesn't?
If it's restricted in capability, then VBA will still be needed. If it's as powerful as VBA, then it will have the same security issues.
The blurb for this new gizmo explicitly excludes VBA. That is, when they say formulas are not Turing-complete, that means without resorting to VBA. But then their grounds seem pretty specious: Excel doesn't have infinite-sized spreadsheets. True, but no actual computer has infinite-sized memory.
There may be a halfway point that makes it more programmatically powerful than it is now but not so powerful that it's a security risk, but that may be the same point where it's more programmatically powerful than it is now but not so powerful that it's worth using. It'll be interesting to see what it turns into.
The 'New functions' here look suspiciously like they're trying to implement relational operators. (You could already do simple joins with
LOOKUP()and friends.)
Aside: Demonstrating to a spreadsheet fan that LOOKUP() is an awkward, badly-implemented, clunky form of relational (or SQL) JOIN almost invariably leads to the spreadsheet fan insisting that relational (or SQL) JOIN is an awkward, badly-implemented, clunky form of LOOKUP().