It is great that Microsoft is investing in Office again. I just got upgraded at work from office 2007 to 2016 (we like to take our time, I was on WinXP not so long ago), and I can barely see the difference. They changed some colors, there are menus that open in a panel instead of a modal box. I don’t think a single user cares about these changes (and they seem to have carefully preserved the bugs). And the problematic UI (for instance the tiny non resizable modal when you click the fx button) have not been updated.
But there are still massive pain points or improvements that could be added. For instance:
- linking between excel and powerpoint. Almost anyone who will show an analysis, whether in a bank, accounting firm, consultant, etc will prepare some table and charts in excel and will want to use them in powerpoint. The link between the two is unstable, screws the formatting, or requires lots of manual steps.
- Microsoft should really look into Apple’s take on excel, with a sheet being a canvas on which you can add tables, charts, the tables have scroll bars if they overflow. Instead of the rigid single grid per tab, where if you show two tables one over the other you need to align the columns even when they have different data types.
- VBA hasn’t been updated in 20 years and it clearly shows. There needs to be a more modern scripting language. I have heard rumors about javascript. Not convinced it’s a great choice, but that would still be better than VBA.
This is a headache for me too: with a sheet being a canvas on which you can add tables, charts, the tables have scroll bars if they overflow. Instead of the rigid single grid per tab, where if you show two tables one over the other you need to align the columns even when they have different data types
Another thing I'd like to see is to be able to treat cells as objects rather than single scalar variables. For example, to represent data that has more than two dimensions.
VSTO isn't great. There was something called VSTA earlier, basically a little visual studio embedded in Office, you could do with any .net language what you could do with VBA. Unfortunately they canned it.
Especially with ExcelDna, it's hard to make an argument for using VSTO. And even with ExcelDna (i.e., an xll written in F#\C#), I find there are still niche areas where vba is effective, particularly when working with the Excel object model. The split I like is to have vba orchestrate interactions with the object model, Sheets, Books, Ranges, Range.Find(), etc. So for example, have your F#\xll code parse "60d" into the last 60 trading days, and then use vba to select those days in Excel's autofilter.
Possibly stating the obvious, but you know that you can manipulate the excel object model from ExcelDNA in the same way that you can with VBA (thanks to VSTO!).
Add a reference to Microsoft.Office.Interop.Excel, and get a handle on the Excel Application object using ExcelDna.Integration.ExcelDnaUtil.Application.
Alternatively you can use the excellent NetOffice.Excel (instead of Office.Interop.Excel), which will have less functionalities but with better version support.
Where has NetOffice.Excel been hiding?! Thank you for that.
What I've found with interop is there's a little more debuggability/edit-and-continue friction that can accumulate into a large loss of time during development. Maybe that loss is offset, or more than offset, by doing the interop with a vastly superior language. I'm not sure. Any thoughts about this trade-off?
I don't know. I find VBA to lack so many modern features (type inference, linq, generics, etc) that I favor using a more modern language over edit&continue. VBA and interop have nearly the same syntax so you can always toy in VBA and then implement it in .net. NetOffice sometimes has a different syntax so is a bit more iterative.
Banks, insurers, accountants; I know a company with 100M euro rev per year that runs entirely on Excel with VBA. Their office car park gate is opened, closed and managed with Excel.
It sounds crazy but the CTO is a cofounder and he found it is much cheaper to just do everthing that way. They have been running like that for over 20 years.
I'm a dev on XL at Microsoft and took a trip a few years back to meet some of our advanced users. I was blown away by their 'accidental' CS education through Excel. They built sheets (debuggers) to debug other sheets (programs) on their OS (excel), and did this not even using VBA.
In my own time, I've visited manufacturing plants where I've seen customers send the factory a spreadsheet containing nothing but a VBA project to exercise a (hardware) test jig for quality control.
This just reminded me that when I was young, I couldn't afford VB5/6, so I would program in VBA in the office products.
I used to work in the aerospace industry. The engineers had spreadsheets that consisted of three visual elements: Cells for the input and output filenames, and a "Run" button.
It had once been company policy that engineers didn't have compilers or other programming tools on their computers (because "we have programmers for that"). But engineers are a resourceful lot, and they did have Excel with VBA.
I still find it useful to use Excel as a GUI. I created all sorts of models that takes lots of configuration tables as inputs. There is no faster UI for someone to fill and modify a table than Excel, so all configuration files are excel spreadsheets.
On the “we have programmers for that”, I see another culprit appearing: IT security, who decided unilaterally that everone else in the 100k people organisation only needs Office and are trying to impose security solutions based on that assumption. That includes application whitelisting, or automatically encrypting all office documents (as a result non office programs can’t consume spreadsheets anymore), etc.
One possibility is this - the people who mess with Excel VBA are generally very smart folk who just never went too techy, but then got really good at Excel and just learned vba as the next logical step. That means the code might not be kosher but it will be thoughtfully written and encompassing all the practical use cases.
Contrast that with a 20something can grad who while competent isn't as smart as that non tech guy, and has indoctrinated the cs way of doing things w.r.t. test coverage and unit tests but often will lack the insight of what the program is actually trying to do. Frankly I would also like to choose the former than the latter.
CS indoctrination is most definitely not a failure of education. Mediocre kids thinking they are writing good code because they've written unit tests is.
EDIT: Let me explain my thinking a bit more - we can try to categorize software development into two categories:
1. People write messy, unorganized code that is often in the head of just one or a few folks, who are smart but unorganized and often without formal CS training. The code often has almost no tests and doesn't follow any of the standard best practices of software engineering. The code is often impossible to pass down to new people, often ending up forcing the new people to redo a large fraction of the work.
2. People write clean, modular, testable code with good unit and integration tests, a robust build framework, etc. The code is written in a manner thats super easily transferable, most devs don't even have to understand the entire codebase to start meaningfully contributing.
Obviously, the second category is the preferred category. A good SDE with a CS bg should follow (and often do follow) the second method. However, category 2 could, at least from my experience, be split into two sub-categories:
2A. The framework for both the code and the dev ecosystem was laid down by (often just one) really good engineers who think through what the problem really is and make sure that the fundamental structure and architecture of the codebase works towards solving that real world problem. This kind of code is absolute pleasure to work with and extend.
2B. The framework and the majority of the code are written by average engineers; often the first few eng hires in the company put the groundwork and make poor design choices and the engineering team that follows never wants to change anything fundamental because that's "tech debt" which the company can never afford to take a step back and look at. The average engineers have a good heart, but often their test cases never test real-world edge cases, they often don't even remember the architecture of the code they themselves wrote a few months back, and the code breaks all the time. Furthermore, the engineers would generally balk at adding any new feature because the codebase is fundamentally evolved into something that just cannot be extended without significant rework, and often they cant even see how they can rework it to add the required feature. In the end, good SDE practices and testing doesn't do shit if the person who wrote it didn't think hard enough.
Here again, 2A is the preferred method of doing dev, but unfortunately, the thoughtful smart SDEs aren't that many, and would often be found in a well-paid job in a big company. Most regular devs can't step up to that level and the end result is 2B happens.
Now the question is, which is the better of the two evils, if they are the only choices? 1 or 2B? I'd choose 1. My experience has been that while the scrappy code is unmaintenable by anyone new, at the least the guys who wrote it (assuming you can keep them long enough) will at least own up to it and make sure it keeps running, and they can at least try (and practically, generally succeed) to ship a new feature as opposed to the 2B case where often the categorical answer would be no.
http://steve-yegge.blogspot.com/2008/02/portrait-of-n00b.htm...
This point reminds me of "Portait of a Noob".
For people who understand what the codebase is doing, it's more effective to have as much code on screen as possible.
For "noobs", since you don't know what the code does, less code on screen is better.
Additionally, these 'noobs' like more meta-program stuff like type declarations (or unit tests) which don't actually contribute to the program itself, but provide a safety barrier to prevent things from going wrong.
(Whereas, if you know what the code is doing and it goes wrong, you can just fix it).
It's fair to say "a program not covered by unit tests can still be good enough to get the job done" (& to suggest that this is under-appreciated), but not having sufficient test coverage significantly raises the barrier to entry for those not familiar with the code ("noobs") from contributing.
Is that really true though? Comprehensive test coverage will definitely give the new programmers confidence, but is it justified? Are there really that many software projects where lots of tests instills confidence that changes won't break things?
At the end of the day, unit tests or not - you have to understand the code you are working in. This means new programmers will not deliver features as fast as those who have been working on the project for a while. They are going to need to learn the code base regardless of whether it has tests or not.
There was a post on HN about Oracle's DB2 code being completely in C and fully unitelligible. Only an insane suite of semi integration tests allows them to maintain it, albeit in the most inefficient way.
A good recent example of that. We were asking a dev team to build a dashboard (we are a bank) showing capital ratios. The team came back saying voila! We looked at it, and the headline number in bold at the centre of the dashboard was showing a cet1 ratio of 1300% (a cet1 ratio is typically in the 5%-20% range). I am sure they used modern programming techniques, but they left an error that would have shocked even the least sophisticated user.
I have many similar annecdotes. Having a good understanding of the underlying domain and what numbers to expect is critical to writing correct models and too often I see dev teams in banks that have no understanding or curiosity of what they work on.
This is such a common pathology it does cast doubt on the entire field of software development but I would like to note it's been pretty commonly spread and now many years old best practice to have real users in constant communication and perhaps colocated with developers.
This is a very predictable outcome when a requirements doc is tossed over the wall followed by patient waiting for the output.
Again, it happens all the time so maybe this advice isn't enough or practical or something, but there is a well recognized solution and I would wonder whether it was done in this project.
On the other hand, a dashboard is probably one of the most boring stuff to work on when you have a CS degree. Typically, a dashboard is asked as a deliverable to keep the dev team busy while also making sure other people's jobs in the bank arent rendered obsolete if the dev team were used to its full potential.
The people saying Devs dont do enough effort to understand the domain are the same people who try to keep the "domain" overly complicated to justify their jobs/positions
Still, when you have a team of very smart folk writing complicated bussiness critical software in Excell, it is a good indication that you should invest in training those people in more proper programming languages.
I know about it because my friend (son of the ceo) provided the electronics for the gate and he showed me all the software in 1999 because we were pitching a rewrite in ‘web tech’ (my go to was Perl in those times). The gate software wrote to the parallel port via the win32 api to open and close the gate after the day/night watch entered the numberplate and name into the sheet.
I've done some pretty mindbending things with VBA on Excel.
They work but they are not pretty and not easy to maintain. VBA is technically a "complete" language (I want to say Turing-complete but that is not a meaningful trait), so it is possible to do a lot with it, but one ends up having to re-implement (sometimes badly) stuff found in other languages in order to write the main parts of the code.
Part of what makes VBA deceptively easy is the control over the interactive elements of Excel (a lot of stuff is done with the Range object), but unfortunately that also introduces state that you can't always control, which entails write extra checks to make sure the state is correct before proceeding. This is especially true if your users are on different versions of Excel (I once wrote something in 2010 that doesn't work in 2016).
There are now other options like QueryStorm [1], which lets you write C# in Excel and connect to SQL databases.
There are also a bunch of Python-Excel solutions that are based on manipulating COM objects, but I've learned that when dealing with a Microsoft stack, there are advantages to using Microsoft-native languages like C#.
Coming back to the article, it mentions adding arrays, vectors, and records to Excel itself; this will make Excel much more powerful because it has traditionally been a cell-based computation system, which has disadvantages that higher-level abstractions overcome (like vectors and tables).
It also mentions writing Excel functions in a first class manner instead of relying on a separate procedural language like VBA. Operations on arbitrary sized arrays will also help it transcend Excel's issue of operating on fixed size arrays -- this will clean up a lot of very messy formulas.
These developments will be interesting to watch, because it brings Excel much closer to a true functional computing system, and gets closer to Quantrix [2] territory.
You can add custom functions to Excel (e.g., the ribbon) and other Office apps using Visual Studio Tools for Office (VSTO). There’s almost nothing you can’t do once you have C# or other .NET languages in the mix (I like to use F#).
One of my collaborators (Ben Zorn) is one of the people featured in the linked article. We’ve been working on visual debuggers (and other things) for non programmers in Excel for a few years now.
There are many little things that just aren't available in Python-Excel libraries, that are available in VSTO.
There are also odd little things that Python database libraries don't implement, like SqlClient.SqlBulkCopy. Native bulk inserting is generally unsupported on Python, so I either have to spit out a CSV and use bcp.exe to bulk insert, or I have to use TurbODBC, a Python library (which has certain limitations, like not supporting certain datatypes like VARCHAR(MAX) or GUIDs).
I think software is often more complicated then it needs to be. I used to write a lot of vbScript, but now I use Node/JavaScript whenever I need to glue something together. And there are many modules in Node.JS that you can glue together in order to do something useful. I find it even easier then vbScript/Excel/VBA!
I am (as a matter of principle) only working on projects which need to run for 10+ years or cannot be upgraded (firmware for very constrained devices) so yes, software is often far more complicated than it needs to be, but writing it in VBA or JS is not helping imho. Carefully designing, typing and formally proving (parts) is what makes me have applications running on cheap servers with basically 10+ years uptime (besides OS security updates) that give me income without work. I run software (SAAS) more complicated and with more clients on one 15 euro per month server than many run on complex aws setups with node/js/mongo. Also with higher uptime; although aws etc can give you 100% uptime, the quality of most software is so bad that it goes down. Ofcourse maybe you are different and take care to make things robust; that is not the common vba/nodejs hacker though in my experience.
Managing complexity is not so much about the language, it's more about software development process. But I think it helps to use a safe and simple language. Engineers often forget about their own cost.
I'm fascinated. What sort of software needs to run with ten years uptime? How do you avoid downtime for operating system updates on those cheap servers?
A lot of embedded hardware, financial transaction systems, factory critical ERP systems (I built one mid 90s in Clipper which is still running in that place; actually I built a few which are still mission critical at this point in time).
But, less hardcore, also my own projects; picturepush.com and flexlists.com for instance have been running for > 10 years making me money. Flexlists was hacked / down once because of a stupid fix gone wrong (just winged it before taking off in a plane). I have many of those; downtime etc will cost me literally money and clients. But running it multi-region AWS etc will cut massively in my margins and, as I see in the many companies I do c-level interim/consultancy for, it really actually results in more downtime at most companies because more moving parts.
OS updates I carefully study and only do them if they expose an exploitable issue. Which is almost never the case as most exploits actually require server access. The mentioned hack was my own fault and showed me that I must use the demands I have for the systems I write for clients also for myself. I am redoing my applications (some of them) in C#, F#, TLA+ and Coq at the moment.
My goal as an engineer is to only need to update the frontend to current standards and let the rest be untouched. I have managed so far.
I've always read that proving correctness and using theorem provers was impractical for real projects. Could you elaborate on how you use those tools and for which situations? I'm actually really curious
Crazy stuff indeed. I saw a type of "click-once", auto-update deployment for vba code at an IBank in 2000 that worked wonderfully. Excel models built by trading desks and investment banking groups themselves are often constructed under a lot of time pressure and one thing about Excel is you can do it wrong and it may still work reasonably well. I have seen some specialty Excel software teams in these environments that refactored those models and made them remarkably robust and coherent. Much of it is just having the time to study alternatives: what part of the model should be on the sheet in formulas, what part in vba, what part in another language via an xll? What questions are the users going to ask and what calculations do they need to be able to see and understand? Where does the flexibility need to be? If a user moves a sheet to another workbook, will the button on it still work? How do you deploy vba code so that if you fix a bug all the models that use that vba code, and all their 'save as' descendants, run the fixed code.
There are clever ways to answer these questions but if you condescend to Excel as a development platform, and believe you can't build anything great with it, you may never brainstorm enough to uncover them.
This sounds like a joke, but I actually met a guy in the early 90's who wrote a Quicken/Microsoft Money competitor as a series of VB Excel macros, who then joined in on the Microsoft anti-trust suit, when Microsoft made moves to try and squash him.
This is really interesting. I wonder if some people that did this have migrated towards hybrid models? Particularly I'm thinking of something like Jupyter notebooks where charts, tables, and code can live side by side and be iteratively developed in a really tight REPL feedback loop as well.
It's interesting to note that one of the people behind this work is Simon Peyton Jones, known (among other things) for creating haskell and being the lead developer of the GHC.
It's why MSR has employed him for almost as long as he's been working on GHC. Not specifically to work on Excel, but to have him help integrate ideas from GHC into Microsoft products.
And I've got to say, they made a great choice. He's one of the most effective communicators I've ever seen when it comes to teaching new ideas.
Alternative mix-up: maybe you're thinking of Andrei Alexandrescu, who worked on D while at Facebook? Names don't match but it's [programming language guy] at Facebook.
This is awesome (let expressions in formulas!!), but Mesh Spreadsheets has offered most of this for a couple of years, and in a free, open-source form.
Specifically:
- arrays and objects living in a cell
- mix of spreadsheet and textual views
- functions in spreadsheet cells, and running spreadsheets as a messaging service (as a lambda function / API)
- dynamic array sizes
We deliver this by offering a grid view of (structured) JavaScript code.
I had never heard of Mesh before. It looks very cool. I wonder if the concept would work well on mobile devices (unfortunately the editor itself doesn’t, as it stands now).
Thanks! Yeah, marketing has been limited as I want to improve the error experience first.
To be honest, mobile is not a focus as I think it's hard to be productive without a proper keyboard. I'd like to make the iPad + kb experience better in the long-term though.
The problem is that your software does not run on top of Excel. Nowadays it is not as much of a problem, but in the past, Excel was often the only software you could use to do something like this - enterprise computers used to be incredibly locked down.
If we're talking about Spreadsheet programming, the work of Felienne Hermans [1] [2] (former mentor of mine) is definitively worth a read. She and her group have done a lot of research into how spreadsheets are used as software in companies and how software engineering research can be applied to them.
I rarely use Excel nowadays, but if I could choose one thing to improve it would be to let me use the English names for functions. Excel is localized to my language, and that includes function names. That probably made sense when targeting non technical people 30 years ago, but it makes it harder to search online and is non intuitive when you're used to other programming languages.
The ideal spreadsheet should be a mix of Excel, Geogebra, reactive Jupyter Notebook (like ObservableHQ or Vue.js computed properties), programmable in Python, C#, JavaScript and/or other languages with the ability to embed third-party widgets like maps or custom charts. And decent data connectors with SQL-like query syntax (including JOINs).
Hi — that’s what we’re working on! Retool (https://tryretool.com) is similar to Excel, except the cells are themselves React components. And you can refer to other components inside the props for each component. You can pull in data either via SQL, APIs, or any other data source we support. Everything inside Retool is also JavaScript, so it’s pretty hackable.
If you’ve got any feedback, please let me know! I’m david@. Thanks!
Thanks for your feedback! I’ll look more into what we can do. We tried purchasing the .com for around 6 figures, but the buyer wouldn’t sell. I’ll try sending him an email again.
An acquaintance of mine was working on this really cool spreadsheets startup that mashes up R, Python's SciStack, and Excel into a web-based tool. Definitely worth checking out: https://www.alphasheets.com/
It's kind of telling that all the sibling posts suggest SaaS options.
The ironic thing about MS Office these days is that, even if it's still proprietary, you at least get to run it locally as a native application and keep control of your data.
As a programmer, excel would be so much more useful if it allowed you to do things like filtering and selects using SQL or some other code so you didn't have to rely on the UI so much to do filtering.
As well as that you can roll your own functions which can be used in cells just like the native ones and call out to other languages and the shell. It really is rather powerful for minimal effort.
However, with great power comes great responsibility ...
This works for one offs. But performance is non-existent when you try to mock up google sheets as a database. I'm in the process of switching a company from google sheets + google sheets query to postgres. Loading one of their google sheets takes around 60 seconds. If they move around one of their 'id' columns it takes 5-10 minutes for everything to recalculate (also keep in mind that google offloads the computations onto the user's CPU...)
This is amazing and much-needed progress for Excel, which I think is arguably one of the greatest software products ever (except perhaps the web browser).
Unfortunately for Excel, it’s essential to Microsoft’s product strategy for keeping people on Windows, which means that Excel on other platforms feels almost as if it were sabotaged compared to the “native” experience in Windows. I feel like Excel could really thrive if only it could separate itself from the mothership and focus on its own interests.
Absolutely, and although the end user was the primary focus of this article and perhaps of the effort as a whole, this initiative can't help but have a positive impact on the libraries used by those heretofore nameless "third parties" such as myself who use code to transfer data to/from Excel.
Types and units would be super useful. I recently lost a few days of work after realizing that a part analysis spreadsheet I was working on for evaluating low noise op-amps was producing incorrect results because I accidentally divided by 1000 instead of a million in a unit conversion somewhere.
I knew the starting and ending units, but Google calculator and Wolfram alpha have made me sloppy.
Having Excel save and load readable and editable source file would be the best improvement for me... Version control, easy editing with vim etc while interoperable for people who like Excel. No programming language research needed for that; just normal programming best practices.
Excel is probably the most successful programming environment in the history of computers. It is great that instead just trying to tell all those people that they need to migrate to something else, and path forward is being provided.
This is pretty neat. Tangentially, does anyone have a good way of version controlling excel files? I deal with people in the finance domain all the time, and for some files that come across my desk, it's terrifying to see multi-million dollar decisions being made in a file where anyone could change anything and not know any better.
Excel seems like the ideal frontend for CRUD apps. It already has all the controls one needs, reusing these would be better than reimplementing them again and again in custom webapps.
Has anyone integrated Excel with a db like PostgreSQL?
A simple scripting language, like LabView’s C subset, that could take arguments out of cells, and write to cells, would be welcome. VBA has too much gobbledegook for a user like me. Python could also be embedded.
I think LibreOffice uses Python as the scripting language, but you would have to fact check me.
There is a company called DataNitro that sells a Python add-on for Excell which allows you to use Python instead of VBA. It works pretty well, but I no longer use it.
I was on a spreadsheet yesterday and I had range of cells that I really just wanted to "map" into another range of cells. I wish scala notebooks could be viewed spreadsheet style, something like that. Just to be able to write a function (formula) in the language of my choice, and then reference it from a cell.
We’re starting to use Airtable in a pretty sophisticated way. It allows you to organize yourselves and be pretty dynamic as you learn and adapt your process. We can then have our engineers come in once the data model is robust but frankly I see our team using the Airtable UI for a long time.
But there are still massive pain points or improvements that could be added. For instance:
- linking between excel and powerpoint. Almost anyone who will show an analysis, whether in a bank, accounting firm, consultant, etc will prepare some table and charts in excel and will want to use them in powerpoint. The link between the two is unstable, screws the formatting, or requires lots of manual steps.
- Microsoft should really look into Apple’s take on excel, with a sheet being a canvas on which you can add tables, charts, the tables have scroll bars if they overflow. Instead of the rigid single grid per tab, where if you show two tables one over the other you need to align the columns even when they have different data types.
- VBA hasn’t been updated in 20 years and it clearly shows. There needs to be a more modern scripting language. I have heard rumors about javascript. Not convinced it’s a great choice, but that would still be better than VBA.