Excel might be the world's most widely used programming language; Microsoft is on a journey to turn it into a better and more powerful programming language, without losing what makes it Excel.
Image: iStock/muchomor
Spreadsheets do more than just calculations; they help people analyse data -- and often, they codify that analysis into a decision model. Code those same kind of decisions in JavaScript or PHP and it's clear that you're doing programming.
Excel is often referred to as the original low-code tool. When Enron's company documents became public, it offered a rare chance to see how enterprises use spreadsheets for these kind of business decision models, prompting Felienne Hermans, associate professor at Leiden University, to interview typical business users about why they were effectively building applications in Excel.
"[We asked] 'Why did you build this yourself? Why didn't you go to IT and ask for a system [written] in a programming language?'' And they said 'Yeah, we did that and they said it was going to be two million euros and six months, and then it was 10 million euros and nine months, and it had half of the features of this spreadsheet I built in an afternoon'."
A few years ago, Hermans built a Turing machine in Excel. Now, the new LAMBDA feature that turns Excel formulae into reusable custom functions makes it Turing-complete. Effectively, you can build rules for manipulating data inside Excel that aren't the rules that come built into Excel, with as much power as any other general-purpose programming language. But you don't have to go and learn a programming language like VBA or JavaScript, the way you do with existing options for custom functions.
Improving Excel as a programming platform
Millions more people know how to do what's effectively programming in Excel than in any programming language. But until recently, Excel has lacked some key features for programming, including built-in custom functions, variables and custom data types.
To add those, the Excel product team has a long-term partnership with programming language researchers at Microsoft Research Cambridge -- including Simon Peyton-Jones, the Microsoft researcher behind the functional programming language Haskell.
The goal is to make sure Excel doesn't limit what a business or scientific professional can do in a spreadsheet, and to make it easier to ensure they're correct as well as powerful.
"Excel is, if you think about it, the most widely used programming language," Brian Jones, group program manager for Excel, told TechRepublic. "If you write Excel formulas, you lay them out on the grid; it's functional programming. But in our work with Simon [Peyton-Jones], he helped us think through what are the core primitives that are missing that would make it more robust and less error prone. People will be able to build the things that they've already built before, but in a more trustworthy, robust and less error-prone, more efficient way. You'll be able to get things done faster, and it'll be easier for other people to understand what you are building."
Making Excel more of a platform takes it beyond the text and numbers people have always been able to manipulate, to cover richer data. "Data types mean that rather than a cell being just a string or a number, it could actually be an object -- a rich set of information like a stock or geography. We have a partnership so that all the Wolfram data can come in as data types, and enterprises can go and build out their own data types. So I could define a custom customer data type, and have that show up in the cell."
Data types can be based on your business data in Power BI, for example. Dynamic arrays built on data types. "The idea [is] that a function could receive an array, rather than just a single value, and it could return an array," Jones explained. One new function in Excel builds on that to return not just the current price of a stock, but its history. "You can pass in a data type that's a stock and the result of that will be an array that is the stock history for that stock."
"We've also been improving the actual formula syntax itself," Jones added. "Sometimes there are these really mega formulas that would get too complex for people to read and understand what was going on." Using the LET function to declare variables made formulae more comprehensible; but, it turned out, variables also make formulae execute faster: "The way it's written can make a formula much more efficient," Jones said.
Recursion allows a set of logic to repeat at a dynamically defined interval. In this example, a LAMBDA function called REPLACECHARS repeatedly calls itself until every unwanted character has been removed.
Images: Microsoft
Custom functions without extra code
LAMBDA builds on those features, making Excel into more of a complete programming language by letting users create custom functions in Excel's own formula language, and having one function call another (known as recursion).
"More advanced folks could always write custom scripts as a function. We had the old user-defined functions that were essentially like COM add-ins, so you could write your own imperative logic to go and build a custom function, which was very much a developer scenario. We now have the ability to do that with JavaScript, but it's still a developer scenario," Jones said.
Some developers are using the Excel JavaScript functionality in advanced ways, like calling Python scripts that run Azure functions, Jones added. "They write Python, they write a JavaScript function that behaves like a function in Excel and takes the values that you reference, and then passes that to Python executing in the cloud as a way of augmenting Excel's formula language."
"Now you can create your own custom functions, but you just do it using the Excel formula language. There's just orders of magnitudes more people that know how to write formulas than know how to write JavaScript."
SEE: Windows 10 Start menu hacks (TechRepublic Premium)
Put '=LAMBDA' at the beginning of an existing formula and it becomes a function you can reuse elsewhere in the worksheet without having to worry about references changing when you copy and paste the formula. You can make things clearer, especially for someone else trying to understand the spreadsheet -- or for yourself in the future if you have to fix errors -- by using LET to declare variables.
"Basically the team thinks of LET and LAMBDA almost like a combined investment. Once you get into building LAMBDAs, you'll start to make more and more use of LET," Jones suggested.
"If I reference cell B9 a bunch of different times, if I want to go and reference a different cell instead, I have to do a whole bunch of updates." Excel tries to make that painless, but LET means fewer places to make the change. "I can create a variable and assign that to the value of B9 and now everywhere else I just reference that variable. And so now if I want to go and change from B9 to B10, I can just edit it in this one place," Jones explained.
"LAMBDA is the next step, where I can take the same logic that was in that formula and I can wrap it up in a LAMBDA function. Now, if I go and start typing in a formula, Excel autocomplete will automatically see that LAMBDA as a new function that's available to me. I can reference the cell I want, hit return, and it goes and gives me the results."
If you later find a mistake in the original formula, you only need to correct it once, in the LAMBDA, instead of searching for every time you used the formula and making the same change. "From a debugging perspective, it's less error prone, and it's easier for other people to go and figure out what was the intention of what you're trying to do," Jones said.
You can also build more complex functions by calling a sequence of simple functions -- a very common programming technique. "That's something you never had the ability to do before in Excel; to have a set of information that you iterate over," Jones added.
Currently a LAMBDA is only available in the worksheet you create it in, and you can work with it in the Excel Name Manager. But a function that's useful in one spreadsheet will often be useful elsewhere, and people will want to share functions with colleagues. That would come in time, Jones said.
"This is clearly just the first step. The Name Manager itself isn't the greatest in terms of an authoring experience, so that's clearly a thing that we'll want to go and improve on. We'll look over time at what are the right models around how you could potentially take these LAMBDAs and share them with other people."
Similarly, JavaScript scripts for automating Excel are currently only available within a single worksheet, and the Excel team is already talking to customers about the best way to share those, so it may well be a similar model for sharing.
"Everyone has that formula you wrote that you want to go and use in other places, and everybody has their own way of how they go and store that for reuse," said Jones.
SEE: 60 Excel tips every user should master
Once functions are easier to share, and they'll also be easier to track for governance within the organisation and update with fixes. "If you see all the places this is being used, if you realise there's an error, you know all the workbooks you need to go look at that may be prone to that error. This is about having more security and robustness in the solutions that people are building, because there are hundreds of millions of people that write Excel formulas -- essentially, spreadsheets are apps and people are going and building these apps."
LAMBDA functions encapsulate formulas that could be useful very broadly, because Excel users are often experts in their domain, Jones said.
"You can imagine ways that we can look at not just sharing the LAMBDA in other spreadsheets, but so that it becomes a thing that's callable even outside of Excel. We'll be exploring that because there's a lot more people who can write this very valuable logic with Excel."
"The people that use Excel are the ones that understand the business problems. They're the ones that understand what really needs to be built, as opposed to having a developer come in, who's trying to interpret what the need is and code it up. That's the beauty of Excel: the person that has the need is the one that builds the solution."
The combination of the new programming features could end up with organisations customising Excel for their own field. "You can create your own custom data types and then you can create some custom functions that know how to act on those data types, and then you get to where you're building Excel for physics or Excel for chemistry with a set of data types may be coming from Wolfram. Now people can start to make decisions and build out that logic around anything in the world that matters to them," Jones said.
"A lot of people, when they think about Excel and modelling, think about modelling financial data. What this is really saying is 'no, you can now use Excel to go and model out and make decisions on anything going on in the world'. We expect to see an ecosystem over time of data types around all of the types of objects that matter to people, that they want to go and make decisions or track or use, and then use our formula language in the grid."
Improving the Excel interface for programming
The grid is what defines Excel, and it offers a form of visual programming. "The beauty of Excel, the soul of Excel, is the grid and functions," as Jones put it. "The formula language on its own is not super powerful; neither is the grid on its own. But the combination of the two is this magic that you get."
"Because you can quickly start to lay out a set of logic, because of each cell being a step along the way and the function you're building on makes it really easy for you to follow what's going on, and learn how to build out the logic that you want. You might be doing that without even thinking that that's what you're doing. Even when people present reports, often there's a lot of numbers being presented but really there's just a couple of cells that are what you look at, but here are all the interim steps along the way, if you're curious about how we arrived at this result."
That means the grid is already a debugging tool, Jones suggested. "You can break your logic across the cells, so rather than writing the one big formula you break it down into several steps where one formula gets a result and you then click on the next result. The grid almost gives you this natural debugger -- it's like each cell has its own little watch window."
"LAMBDA changes that a bit, especially right now with the implementation where you're writing the full thing and putting it into the Name Manager. So we'll look at whether the patterns folks use are ones that they break it out first 'til they get to where they know the logic works, and then they combine it. Or if we need to have a better way of interrogating the different parts of the formula to see what's the result of this piece versus the result of that piece, so I can understand why I'm not getting the result I was expecting."
Excel won't get what programmers might recognise as a debugger to help with that, but there will be more help. "We'll definitely look at the key places where people get a little bit stuck in terms of understanding their results and how to go and fix those results," Jones said.
But adding more formal programming language constructs isn't about turning Excel into a tool for experienced developers.
"As much as this is adding more power, we want to make it so that Excel feels approachable to more and more people. We're definitely not trying to go down the path of making it into even more of a high-end tool. Where we think Excel will do a great job is trying to take some of these patterns that you see in programming languages and bringing them to non-programmers."
Jones promised improvements to the Name Manager, which was a logical place to put the new LAMBDA feature so people could start to use it but is currently somewhat primitive. "We don't ever want to be a full-blown IDE, but if you're familiar with the Name Manager, it's just a single-line text string that you're editing. In fact, our help articles for getting started will suggest that people write the LAMBDA out in the grid first, and then copy it into the Name Manager. There are definitely a lot of things that we could do to go and improve that, and that's an area we will be iterating quite a bit!"
Excel will also try to start attempting to predict what formula you're typing and autocomplete it, rather than having users type it out in full. "We're starting to look more and more for ways we can use intelligence to help people, so we can be a little bit more predictive as they're writing out formulas, to try and help people build out the right type of formula and make the right selection."
As usual, the new LAMBDA functionality is rolling out to builds in the Office Insider beta channel gradually, starting with around half of Insiders, so not everyone Insider will see it yet. It's not restricted to specific Office SKUs and will be in all Office 365 and Microsoft 365 subscriptions that include Excel.
Microsoft Weekly Newsletter
Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and Wednesdays
Sign up todayAlso see
"complete" - Google News
December 11, 2020 at 05:36PM
https://ift.tt/3qMP6zj
Microsoft: Turning Excel into a Turing-complete programming language - TechRepublic
"complete" - Google News
https://ift.tt/2Fvz4Dj
https://ift.tt/2YsogAP
Bagikan Berita Ini
0 Response to "Microsoft: Turning Excel into a Turing-complete programming language - TechRepublic"
Post a Comment