Excel elicits some equivalent of Stages of Grief in technical people. First, some version “it’s so dumb the world runs this way, I’ll just port a few of <use case> into a modern CRUD web app and people will prefer it since it will reduce errors and be in the cloud”. Then, “oh hmmm the flexibility Excel brings to the user is really hard to replicate in my CRUD app. But I’ll try.”. Then despair - “arrrg if only these dumb users would realize that the flexibility and power of Excel lead to lots of problems! Use my simple CRUD app, please!!!”. Then acceptance (and awe) - “Excel is UI, a DB, and business logic all wrapped in 1 thing that everyone already knows how to use.”
Not saying Excel is perfect. Lots of us have built or are building products that do a better job at specific workflows. But surely it is one of the best pieces of software we have, and if I could only use 1 piece of software forever, it’s probably Excel.
My problem with Excel isn't the principle of spreadsheet software, but that it's bad spreadsheet software. It has a bad expression language and a bad API and bad type-safety and bad automatic type coercions and bad range behavior and bad copy-paste functionality...
There are so many boneheaded things wrong with Excel that they can't fix because they must stay bug-compatible.
So I keep dealing with text getting mangled into dates and iso8601 dates getting mangled into God knows what and papers getting examined and people finding out "oops your data got screwed up by some counterintuitive behavior of excel and your findings are all wrong".
Actually, the researcher who works on this is a pioneer in the field of “example based programming”, which is pretty cool stuff. But it does occasionally produce garbage when given garbage (what did you expect February to map to?). In the space of simple programs that map Jan to Janet Jackson, that one doesn’t seem too bad!
So, from my previous career working with spreadsheets, I've noticed that most aberrant behaviors in Excel are easily circumvented through slightly-above average features the average user just isn't aware of.
It's been a while, but for example name-mangling can be circumvented by prefacing an entry with a single quote (analogous to how the r character raw string marker is used in Python). You can probably also change the automatic type coercion, buried somewhere in the settings, since adding that single quote prior to the string mangling might be difficult in Excel. (But data entry is often manually done anyways with these kinds of setups.)
Usually in cases like these, the problem is that the user isn't aware of the feature they need to solve the problem they're having. (Granted, Excel's whole appeal is easy onboarding for non-technical users.) Save for obvious limitations like data size or overly intricate business logic better suited for an actual language, Excel is well-suited for a broad range of business and academic use cases.
> You can probably also change the automatic type coercion
Maybe for manual entry, but not when importing a foreign format like one if the many CSV variants that we run into.
> buried somewhere in the settings
This is a huge problem, as where such settings exist they are by their very nature user-local. Back to our old frenemy CSV for one (sadly not hypothetical) example: too many times our clients shuffle data (exported from various places) around in text format and someone along the chain has the wrong locale set so when ISO8601 dates get silently converted it is to a format the next users version isn't expecting but silently assumes is correct because by some "miracle" the ambiguous day/month number combination is "valid" both ways around. The result is then imported into our system which is blamed for its answer not making sense...
Comically with their resources they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty, yet seem to have made no effort towards anything like that.
I still see people getting date and numeric fields mangled by excel on analysis processes that take literal days to solve because it ends up recursing over all the cells multiple times, and yet even showing them a simple Python or whatever program that does the same thing in minutes, and the result is still put in excel, the next time they need something similar ... Here's another excel abomination.
These aren't dumb people. They've been trained by Excel that the world is awful, slow, and error prone.
with their resources they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty
I think that's pretty much what they were going for with MS Access, it seems like Access hasn't matured nearly as much as its long life should have allowed. Possibly because it would eat too much into SQL Server. They have to keep its feature footprint too small to appeal to users who only barely need something like SQL Server.
As for Excel == slow, yeah-- I actually do like Excel, it's in my toolbox, but waiting 5 minutes for a vlookup against a few hundred thousand records to complete because I need one single column in another sheet is maddening when it would take nearly no time at all if I loaded the data into Python -> Pandas.
> but waiting 5 minutes for a vlookup against a few hundred thousand records to complete because I need one single column in another sheet is maddening when it would take nearly no time at all if I loaded the data into Python -> Pandas.
I replied elsewhere in the thread about me working on a new spreadsheet alternative that’s like a reactive Haskell. (Attempting to preserve community norms by not self-promoting more than once per submission.)
One thing that I’m planning is that for any table/array/tree etc. that exceeds a certain size, the data itself and any outputs of functions that work on that data will be offloaded into a real database as rows which can be indexed. Either via SQLite, using PostgreSQL, or even BigQuery (but I don’t trust Google much), this would let users transparently grow their data sets from 1,000 rows to 100,000 to 1,000,000 rows without having to suddenly switch languages or representations. (As an online product this is easier to do, but I think a desktop app equivalent could do it too.) Array functions would in the end be streaming functions rather than literally loading all data in memory.
In Inflex tables are literally arrays of records, but as the language is purely functional and statically typed, the compiler has a lot of freedom to rewrite code into more efficient representations (e.g. streaming), as done in Haskell or SQL.
Access hasn’t matured because the “easy to make database and app” concept has moved to a focus on PowerApps / Sharepoint. It wasn’t/isn’t a strategic platform and isn’t designed for data analysis.
For the sort of stuff Access does you should do it within Excel, which now has the ability to build a fully relational dataset using PowerQuery and PowerPivot.
But yeah, excel can be slow! It’s not designed for big datasets without PQ but inevitably gets used for it because it’s easy and available. I can still do a lookup in excel quicker than I can write one in pandas even if it takes 5 mins. As a hint, the new XLookup strictly dominates Vlookup and depending on how the data is structured/sorted can be many times faster according to the search parameters.
I suspect your example is only for context, but just in case it saves you time in future, if you can sort the data first then you can use something like: if(vlookup(value,range,1,TRUE)=value, vlookup(value,range,column,TRUE), error_marker). The TRUE returns next nearest match, the IF ensures you have an exact match, and it takes a few seconds to lookup over hundreds of thousands of rows.
> they could have put together a type-safe back end on sqllite with multiple available frontend APIs to gradually transfer users to Excel But Not Shitty
They have already done this (although not with SQLite), see “PowerQuery / Get and Transform” which is a full type safe data cleaning/transformation interface, MCode language, and then you can link this typesafe data into excel and then into PowerPivot + DAX / Measures.
All of the above is already built into excel as standard, just nobody seems to know about it. Open excel and click data, get and transform, open PowerQuery editor.
I get the impression that in recent years Microsoft's become more willing to make fundamental changes to Excel. Still early days but I'm cautiously optimistic that things are getting better.
I first read those thinking "cool, I'll have to let nerdy me have a play with all that". I actually often use Excel for various bits & bobs, and must (as a database guy and ex-Jack-of-all dev) shamefully admit to finding it useful and sometimes really liking it. But...
Then my mind moved on to "oh hell, what holes are our clients going to dig for themselves if they catch wind of some of this?".
Even when talking about existing functionality that you might want to use along with these new tools they say "It took us some time to understand the precise rules that drive this behavior". It took Microsoft's research people some time to understand the ins and outs of features in Microsoft Excel and they talk about this with apparent pride in a paper published by Microsoft.
I look forward to being witness to the minting of brand new expletives, when the reporting team are handed a gargantuan KPI calculations workbook that uses these tools, and asked to reproduce it in our environment (but make it work properly as "there are a few bits that occasionally give odd results and have to be adjusted manually sometimes" - not that they'll mention those oddities until we've banged or head against them a couple of times or in UAT they ask "but how do we tweak the financial sprocket count result for dept X only, when Y happens?"), which they assume we can do that in practically zero time...
I’m working on a (very beta) product that hopes to address a subset of your complaints about spreadsheets. https://inflex.io/ Based on your phrasing you might have already heard of it. In particular my two blog posts mirror what you said: https://inflex.io/blog
I haven’t added a date type yet, but it will be a distinct type (not just a string), with explicit import. Pretty much similar to the Haskell time API.
If you like the idea I would welcome any criticism. I have 50 things that I think the product needs, but your input would help.
It's one of those things where it's bad, but it's the best we have, especially given that in some of the flaws you mention, they're pretty much copied by alternatives who want to be compatible with Excel.
What’s wrong with its copy paste? It’s missing a few things but what’s there is great. The type situation is terrible I agree. I can’t even count the number of times I’ve screwed up a currency conversion or not known what currency a cell was. Why can’t it know that dollars are dollars and when I multiple my euros per dollar I get euros?
Copy/paste can be really weird. Take these three scenarios:
1) Copy some cells. Type something into another cell before pasting it. Your copy buffer is gone, you can't paste what you copied.
2) You copied some cells, pasted them. You arrow over a few cells to inspect a formula and then hit enter: You just pasted over the surrounding area with whatever you've copied previously.
3) You copied some cells, you pasted them somewhere. Now you want to insert a new column, but when you right click the option is missing, replaced with "insert copied cells". The clear the copy buffer you go to random empty cell, type a space to clear the buffer (see #1 above) and now you can insert a new column.
On the plus side though, copy/pasting formatting is pretty useful, so is transpose.
If you really want to hit your face on a desk, wait until you encounter a user who doesn't know how to tabulate or sum anything in Excel, and uses it as a big grid-based text editor, manually using a desk calculator to sum up columns, or perform various other formulas on some selection of cells.
also, prepare for the user to suffer some sort of existential crisis or breakdown, possible tantrum after you show them how to do these tasks automatically with a simple excel formula.
My first job had a coworker who didn't know how to sort the spreadsheet. She printed it out and cut it into strips, then sorted them while watching TV at home in the evening, then taped it back together. I was shocked and in the decade or so after that I have yet to run into anything like that again.
My introduction to Office/office horror was a colleague writing her message in PowerPoint, attaching it to an email in Outlook and sending it through.
It turned out to be a standard practice in the academic institution I was in.
Not quite the same, but in several community email lists, sending emails via a JPG or DOCX is the norm. Type it up in Word, and attach it to the email.
My institution's administration does this. Every message from them is typed in Word, then attached to an email whose body says "Please read the attached message". They do this for every message, no matter how trivial.
I asked them why they do this, but haven't received a reply, perhaps because my question was in an attached gzipped Postscript document.
I encountered exactly this when my brother went to film school. The students would rotate roles and it was his turn to be the producer. One day I came home from work and he was having problems with his Excel-based budget sheet. I noticed that his row of sums at the bottom of one sheet had hard-coded values rather than formulas. Thinking he had inadvertently pasted over them, I told him, "Look, you killed your formulas." "What are formulas?", he asked. Turns out that he had been tallying up the columns with a calculator and then typing those totals into Excel.
Also, if you think this type of user is going extinct as the new generation who grew up with computers will intuitively understand how to use the basic features of common software.....wait until you see a 24 year old with a bachelors degree doing stuff like this. Turns out watching youtube on an ipad from 2 years old to adulthood does not confer magical computer skills!
A loong time ago when I first encountered spreadsheets, I thought it would be a neat way of making the monthly reports, just adding up columns and rows of a 30x30 table. My boss was not happy about the use of "sum" and made me add up the rows and columns on a desk calculator to check that they were right.
Why the OS doesn't allow you to select some text and perform system-wide actions on it is a mystery. Calculate, Upper/Lower/Sentence case, Search, Dictionary, etc etc
Building on this, when purist engineers trash Excel, I point out that Excel cell formulas are the only case of Functional Programming going truly mainstream.
I found it interesting, fun and great.
Still, I would stick to using Excel for the personal analysis of data and not as the language/tool on which software is developed: data & programming logic intermixed, the default way to index cells, the difference between normal cells and array cells, and tables, etc., the fact that some things are just not possible to do fully programmatically without creating your own VB functions and therefore asking recipients of what looks like data to accept running arbitrary code in their machines, etc.
I use Excel in my data processing workflow. I'll grab a small subset of the data to play around with in excel to see what it is I actually need out of it. Then, I'll do all of the real processing in python along with matplotlib for any charts I need to produce. Excel could almost entirely handle my use cases but there is usually some bit manipulation I need to do because my data is usually telemetry encoded as ASCII. Also making if-else statement in Excel is exhausting.
I could also just use MATLAB but I'm more used to using python.
Excel is exceptionally good at telling you what the shape of your data is. I love being able to just turn something into a table and then see the distinct values as a filter.
Sure, SQL and most environments can tell you distinct values of a column and their counts, but they are much slower and more cumbersome to explore with. I often start out with broad filters then go through many more specific ones depending on what I see from the first filter.
>Also making if-else statement in Excel is exhausting.
About a year ago Excel added =SWITCH, which is effectively {Logic Test | Output} pairings executed until one is true. Many if/then/else nestings could be eliminated with it.
It looks to me datasette (https://datasette.io/) could fill the same need: load the data into SQLite and explore it with a host of tools and visualisations, all profiting from the power of SQL
If the functional programming language requires that each expression must be exactly one line with no comments, I'll pass thanks. Long Excel formulas make Regex look like poetry.
If you have a long expression, you can break it into multiple ones by storing some intermediate results in another cell. You can also hide such cells or put them in another sheet.
This might sound like a hack, but being able to see intermediate results makes debugging complex expressions easier too, so in a sense it is working as intended.
Formula -> Trace Dependencies/Precedents is also super handy! I have a short VBA loop that applies it to all selected cells. It makes it very easy to visually spot one cell reference that's wrong, because 20 cells connect to corresponding cells with parallel lines, then one isn't parallel.
Ctrl+Tilda toggles show/hide formulas, which helps identify input cells when you inherit a messy spreadsheet.
This is a big one, especially if your edition isn't English/US.
Also the number formats and locale settings can cause quite a bit of trouble, if its not English/US. Alot of data warehouse setups output files with U.S formats even in Europe.
On longer formulas, I do Alt+Enter while editing the formula to get a new line, then 4 spaces for an indentation. Just like that, you can keep track of start/end parenthesis.
It's not perfect, I wish it would automatically format that way, but it certainly makes maintenance easier.
Simon Peyton Jones, one of the creators of Haskell, has been on that point for awhile too (https://www.slideshare.net/kfrdbs/peyton-jones). I think it's his main area of research working at Microsoft actually.
I think a lot of the love for Excel by office workers comes down to empowerment. Workers like being empowered to solve their own problems. Excel does very well. You as a non-programmer office worker can quickly make a spreadsheet that solves your problem. You can update it on your schedule as your problem changes or to explore new possibilities. You give that up as soon as you go to a CRUD app. Need to change something, put in a a request to IT who might get around to it in a week if you are lucky. Wheat would have been a few minute feedback loop with Excel is a multi-week slog.
There is no other tool of which I am aware that enables the common person to sit down at a computer and make the computer do useful real world work work for them in a flexible way than Excel. I have used it for budgets, lists, form entry, and reports. In addition, it provides a very smooth on ramp into programming via the record macro button, which will write the VBA function that does what you are doing in the GUI. Most of my VBA functions started out as record macro, and then were edited to generalize. Truly an amazing piece of software.
> Need to change something, put in a a request to IT who might get around to it in a week if you are lucky.
If the change happens at all. The IT department might say "we don't have the time to do that," or "other users depend on the app being like this so we can't change it to be like that," or even "why do you need that, your justification isn't good enough."
Excel is the non-programmer or non-admin equivalent of "I'll just bang that out in [php | python | perl | powershell]." We've all done it, we will all continue to do it, and yes it will look like an absolute mess...that probably winds up powering a Fortune 50 company.
I've contracted in places where cmd is blocked, but Powershell is not. The mind boggles.
(For those cursed to work in Windows enterprise land, most of what is being talked about here is that Powershell is essentially .Net, e.g. '$DateTime = New-Object System.DateTime -ArgumentList 2015, 10, 10')
I have the experience of having worked in UNIX shops where $HOME was mounted noexec, everyone got development rights over access groups and we only got to use official IT tools.
Many are spoiled by having their own devenv nowadays.
Ironically cloud computing is going back to those days, where the cloud IDE and shell only allows for IT validated tools.
As a service owner of some fairly significant technology services, every time one of my colleagues “fixes” some business problem with an enterprise IT system, it’s measurably worse than a well-defined excel driven process.
My favorite was when our travel and expense system moved to some Oracle monstrosity, and we literally hired full time staff to data enter expenses that were submitted on paper because field service folks were spending 5-10 hours a week on expense submission.
Another great example is when almost anything is “improved” by some sort of quick and dirty interim servicenow task.
Well, I observed the opposite effect when a complex Excel spreadsheet was replaced by an Access DB that allowed a small company to discover that they had extra assets for like 200K USD. And the original Excel author realized how much more sense the data made once he saw visualization of data constraints and relations.
But then IT of bigger companies treats Access as not a real DB and often disregards it with the same contempt as Excel.
My beef is that tech folk have a tendency to make themselves the customer, or maximize convenience to IT over whatever the actual problem is. Internally, the politics of the company tend to dictate this stuff. In the marketplace, software is usually much better!
A point to note on Excel's empowerment I have discovered. The main reason for its ubiquity is because it's mostly already there and doesn't require the odious purchase order process to be navigated and then an odious IT onboarding process navigated. So fuck that, let's just cram another turd into Excel. That sort of overlaps with your IT request thing but at a higher level I think.
You could also say that it's IT externalising their costs. They don't want to be responsible for another system, so all the work and maintenance gets pushed on to end users.
" put in a a request to IT who might get around to it in a week if you are lucky."
A week would be brilliant. Months or years is more realistic. And every change has to go through five levels of management and business analysts before it goes to some clueless people at an outsourcing company and costs insane money.
I love excel, it's fantastic. I also hate excel, it causes me so many problems. Usually in the way of proliferating "shadow systems" of this sort:
Them: "We need you to do X with our data."
Me: "Okay, where do you see the data in the (enterprise ERP) system, I'll find the tables from there."
Them: "We use Excel because the system doesn't have a place for this data."
Me: "We upgraded 10 years ago. There's a place for it. How is office X getting the data they need from you?"
Them: "Oh we just share an excel file each week and they add their own stuff to it. Here's 300 weekly files for the last 5 years. We really need the analysis in a few days."
Me: ::tries to concat 300 excel files, discovers 50 variations on column arrangements, names, data types etc., tells department--:: "You aren't getting this in a few days. The good news is that you don't have to use Excel anymore because when I'm done, your data will be formatted & loaded into the ERP and all you'll have to do is put new stuff there too, and all analysis & reporting will be automated."
Them: "But it's the full-time job of one of our staff to handled all of this stuff in Excel."
Me: "Have you ever though that you needed more help in your department? Well, congratulations, you just got one 'new' full time employee."
People at least then generally walk away happy, except for me as I stitch together 50 variations on 300 different excel files. That's a slight exaggeration though: Most of these offline shadow processes aren't quite that large, and staff turnover is usually fast enough that such a process doesn't age 10 years past its point of obsolescence.
Now we have Airtable and Smartsheet, where you can have a proliferation of undocumented shadow IT systems core to your business processes AND pay by the user every month for the privilege! That's progress, right?
My general theory of shadow systems is that they're usually the product of either:
1) Poorly trained users that don't know how to use the system for their needs
2) A poorly chosen system (or system implementation) that doesn't fill the needs of the users.
2-b) This includes systems that technically do the thing users need it to do, but in such a bad/slow/sloppy way that no one wants to use it.
I'm not sure which is more prevalent... I've had to say "Your system will do that for you" plenty of times, but I've also been on the other side creating shadow systems myself. One such was an ad-hoc data integration using python's splinter library to automate data download from a system w/o an API to a very kludgy "select .... from dual union" series of SQL statements to populate some of the data needed for a daily analysis. The ETL folks were already in integration hell on over-scoped projects, and also not accustomed or tooled up to run arbitrary python code-- especially any code they didn't write-- to power a process.
Or 3 - Users have a new requirement outside of any existing solution and perceive building their own solution in Airtable as being quicker, easier and cheaper than dealing with the people in IT.
I’ve been in a position before where talking to IT to get something small made means absolutely tonnes of work producing business cases, requirements documents, asking for funding, only to be told the huge bill to produce something internally isn’t worth paying, for something that takes a few hours to build and implement yourself sitting next to the team that’s going to use it. Ok it’s not supportable, but it’s airtable!
In my current role as a ops/logistics consultant, when I talk about IT change to clients a lot of the time the question comes back as “can we find a way to do this without talking to our IT team? You can’t get anything done through them without it costing at least X”.
I've been involved in the selection & implementation of multiple systems like this, and there was a reasonable effort to include stakeholders from functional areas. After the RFP responses and selection of potential vendors, they each come in we had basically a week-long conference with sessions for functional areas, technical areas, implementation, etc before selecting the vendor. Even then, the problem wasn't lack of consideration for local needs: The problem was that pretty much every question asked of the vendor's demo team was "Sure we can do that, it's just a configuration!" or "That's a customization that a few of our customers have implemented, we can piggyback on their work to do the same for you!"
Then implementation comes along and what were claimed to be configurations are more like "You just rewrite this COBOL module, but updates will break it so you'll need to rewrite it for every update." And those shared customizations just never materialized. In one case the actual "built in" functionality shown via screenshot slides literally didn't exist in the actual product. ::ahem Oracle:: and resulted in a lawsuit for it, among other things like like deadlines they didn't meet and & refused to complete without being paid an additional exorbitant fee, even though it was a fixed-price contract.
Also yes, there are IT departments that take a request that's basically "Please create this database trigger" and run it through multiple layers of review & approval & business case justification, ROI, etc over the course of months for something that would take about 10 minutes to implement in a test environment, another hour to sufficiently test, and another hour to pass to UAT so they can confirm it's working as desired, and push to production.
So you're absolutely right: I missed a causal category for shadow systems. Honestly I don't even think shadow systems are inherently bad in some cases: I just think there should be formal documentation lodged with IT so there's institutional awareness of it, and the departure of a single employee who created it doesn't cripple a department. This crippling of a department is something I've actually seen.
> Them: "Oh we just share an excel file each week and they add their own stuff to it. Here's 300 weekly files for the last 5 years. We really need the analysis in a few days."
> Me: ::tries to concat 300 excel files, discovers 50 variations on column arrangements, names, data types etc., tells department
Had to laugh seeing as how familiar this is in my company.
I just realized something. Riffing on how Excel is UI/DB/biz logic in a huge unbounded zone, I think the reason it’s so popular is that people use it to build PRODUCTS. They don’t call them products, don’t sell them, but a well-built and complex spreadsheet is basically a mini app.
What else do we have that’s this powerful? And don’t say Airtable, although they clearly “get it” - it’s not about files, it’s about building mini products.
I knew an accountant that built an accounting software in Excel. He had different tables for different invoices, and Excel spreadsheet would take that data, do whatever accounting needs to be done and prepare all the reports that need to be sent to various institutions. If I remember correctly, he had a Excel file for each customer he had and just used Excel for all.
It wasn't a mini app, it was a complex application used in "production" for at least 5-10 years. It's likely it is used today as well.
As an example, there's a popular idle game called "ngu idle", full of complex overlapping and intertwined progression systems.
Someone made this spreadsheet to help you optimize your play. Some of the worksheets in this have very carefully considered UI and complex data flows. Its totally inscrutable if you've never played the game, but its more complex and well crafted than many apps on my phone:
Some people I work with have made very complex Excel "machines" that have been in use by many people for decades. One of the new engineers spent about six months porting one over to MATLAB and there were all kinds of magic numbers in the formulas that turns out were there to account for some bugs in some test equipment we bought 20 years ago. We had purchased a new system that would output data that wasn't compatible with his old model so the new engineer ported it over and now we get clean, consistent results. This is for antenna measurements so there's really been little advances in decades in the fields. The advances only really exist for antenna simulations.
The backbone of many statistical institutions such as the Office for National Statistics is Excel. I worked on porting a lot of complex Excel systems to Python that had been around for 5+ years.
There were teams of people whose job it was to produce one report over a timeframe and they had the process to a fine art.
The main problem came when members of said teams left. Macros locked behind passwords, magic numbers in formulas, the wheels could come off very fast.
If Excel had a more robust functionality for versioning and documentation it could be even more of a powerhouse than it already is.
@saberdancer: "When I saw this, I was blown away."
I've seen something like that, that took a whole weekend to produce a report. I suppose it does the job, as long as the accountant doesn't mistake 30 for 0.03 (30%) in a formula.
I’ve worked with a bunch of huge Fortune 1000 companies that do similar things. Model each contract in its own excel file then every month put in that month’s data and have the excel model spit out how much you owe in the form of a formatted invoice.
Long time ago I found myself as the sole point of contact between 200 managers+workers office & regional HR office. I had recently came across excel, so made a simple sheet with names & staff numbers. That sheet, over the time of 3 years, evolved into a mammoth of sheet. sheet with db_ prefix listed names, joining date, title, salary, timebase, country of origin, skills, driving license number, expiry & many such. Other sheets pull up some columns, & I manually add D for day shift in date column, or N for night, H for holiday, F for Friday. Another one pulls this data per week for D only or N only or both. Another one pulls data & prefills the leave request sheet for individual staff. At any time I change the dropdown week value, & I can print a summary of man hours, who worked, how much expenses on HR. No where in the sheet I used VBA because of that macro dialog box.
It's interesting that nobody's really leaned into Excel as a RAD tool. Once you have your spreadsheet functioning how you'd like, feed it into a "despreadsheeter". This could be, at a basic level, locking and hiding every possible cell, to running it in an embeddable Excel-like runtime without a lot of UI clutter and foot-guns, up to parsing the formulas, providing a form editor to build the inputs into attractive screens, and spitting up Python/C#/Java/a native binary.
I believe that generating database schema from excel would require an AI. In wordprocessor you can have heuristics like "user bolded whole paragraph and added newlines - consider that a heading" to guess the document structure but hardly so in spreadsheet.
Filemaker Pro is such a product but it looks like they just released the last version. Print companies use it like crazy but I've not seen it used anywhere else.
> if I could only use 1 piece of software forever, it’s probably Excel
“And thus we see that people with prolonged exposure to nonfree software need special help.” *Commits sin against the Church of Emacs with proprietary DuckDuckGo JavaScript*
That seems pretty irrelevant to the specific quote I was addressing, but in general it’s unfortunate there’s no tech-illiterate focused distro AFAIK, because it seems like CUA-mode would get you far enough to appeal to a lot of pretty mild power users (eg. there are stories of non-technical secretaries learning to program without realizing it by learning Emacs macros).
The specific quote exists as part of a broader context in this thread in which the accessibility of any proposed alternative to excel is relevant. Making it relevant to point out that while emacs might be a valid alternative for some, it is not for most users.
> The specific quote exists as part of a broader context in this thread in which the accessibility of any proposed alternative to excel is relevant.
Right, which is why I tried to address the next claim:
> while emacs might be a valid alternative for some, it is not for most users
I think a distro like Spacemacs but with cua-mode / UX targeting Office users instead of with evil-mode / UX targeting vi users could make it fairly viable for many or even most users, it seems that it already is for many users, like the secretaries I mentioned in my other comment, who considered themselves non-programmers if not non-power users (https://hn.algolia.com/?dateRange=all&page=0&prefix=false&qu...).
Well I guess we're splitting hairs at this point. No need to go another few rounds, I'll just thank you for pointing out Spacemacs, which looks pretty interesting.
The painful thing about Excel is that it's a self-fulfilling prophecy. It's built on extensive UX research that tries to support the existing corporate culture and workflow. In so being it reinforces that norm, which generally encodes the assumption: the people handling this data don't understand how or want to write code. It's extremely difficult to build a spreadsheet that gives those people as much power as Excel does.
This is frustrating for people working in an Excel shop who know how to code. Most of them realize their coworkers don't want to learn to code. They just wish their skills could be useful.
Excel is great until you need to fix/extend an app that is now integral to all company operations that was made 20 years ago by that guy in sales that was good at computers and has long left.
It does have something to do with it in the sense that it locks code in a proprietary format that does not allow code traceability and source co trol, and does not favor best practices like code comments and commit comments. That does make maintenance harder.
It just occurred to me that the situation is isomorphic to any runtime environment. I cannot imagine transplanting a shell script, C program, Java program (OK, maybe except for a Java program), Win32 app (OK, maybe that also), or a web-app from 20 years ago onto a modern version of the runtime and expect it to work without any major changes or fixes to account for the inevitable cases where the outside world simply decided that "we do not do things this way" any more. Cross-site scripting, password strength, political and timezone changes, many of those trip up applications too.
Shell and C should be fine. To be honest any pure computation should be fine. Your interactive HyperCard stack if you get the right emulators would be ok. MAME, sure. But anything depending on the persistence of networked resources will not work. Other than Phil Greenspun all the endpoints have changed. CGI with no external dependencies or Ajax should work except it got pwned along the line.
Except most likely it wasn't bash shell, or maybe it was, but it was written on an HP-UX and never ported to anything else.
Likewise the C code might eventually be written in K&R C still, make assumptions of byte ordering on the MIPS and memory access semantics of workstation it was originally targeted for, and with luck use SGIPro extensions.
Then you realize it's pretty amazing that an app that was built 20 years ago by a non-programmer without any assigned budget fulfilled the needs for so long.
This is literally what I spent my last two weekends doing. Not as something I wanted to sell, but for one of my own personal spreadsheet use cases. Thought I could build a web app to suit my needs better, but after some 30+ hours working on it, I'd rather just go back to the spreadsheet.
Airtable is basically MS Access for the current age and it's absolutely amazing.
When dealing with it, I realized what makes Airtable so wonderful and different from excel: It's typed! Each column has a type which allows airtable to build this easy to use featureset.
I think a crud app is great for "directing" the user through the common changes they will make to the data, like approving a document by clicking a button for example. But a simple tabular view of your data is really great too if you can get away with it. Sometimes it's not that useful, if for example the data is hundreds or thousands of columns wide.
I think that's why something like airtable is great. It gives you the best of both worlds. All the data is backed by a simple spreadsheet, and it has an API so you can create the more custom UIs to help direct users with common tasks.
Spreadsheets are one of the original "bicycles for the mind". They empower users to do what users want to do, more efficiently.
They have their downsides. But they allow a non-programmer express some logic and arithmetic in an interactive, iteratively built, visual reactive program.
This is nearly unparalleled in the modern software landscape.
I have built many personal finance and analytical tools for my own use with Excel and Google Sheets in few mins and have extended their features as needed from there.
I have been using some of these sheets for years and are better to my needs than buying an app or subscribing to a SaaS web product.
They did not kill anything, they brought them into VB.NET, and I can tell many VBA users are quite happy to transition into VB.NET, as I experienced in a couple of life sciences corporations.
Having used VB back in the day, the only thing missing are a couple of people still not getting that VB.NET with Forms is just the same deal, even Me is available.
Except that all the little controls and plugins DIDN'T transition to VB.NET--and they're what made VB6 useful to common people.
And the hot code reload in VB.NET is nowhere near as useful as it was in VB6--and that was how common people debugged code.
I can go on and on.
VB.NET was not a useful replacement for VB6. I still know tons of businesses running VB6 code and, when they finally have to migrate, it won't be to VB.NET.
Never display data in a tabular format without sortable columns, and a link to the raw data as CSV/json, preferably CSV, and if possible a link to the SoR for the data source.
SharePoint / Office is overlooked by the developers you write about here.
Office/Excel clientside => SharePoint "Office Server" serverside => then write apps integrating with SharePoint.
SharePoint is an application platform with Excel services on top of .Net. (so that multiple people at the same time can work on an Excel sheet via a web front-end)
It is ment to make the step from local application to server application.
It has auto import / workflows / api / taxonomy / search / etc ...
> ...if I could only use 1 piece of software forever, it’s probably Excel.
Google Sheets gives Excel a real run for its money. It's less powerful in many ways (example: getting data is more complicated) but makes up for it with the ability to collaborate and run multi-platform. Sheets is one of the more underrated products in the Google Apps portfolio.
Disclaimer: I reached the awe stage on Excel many years ago.
My main problem with Excel is it does many things but it's never the best tool for any job.
If I could only use one piece of software forever it would be emacs. I find it sad that Excel would be considered in the same light. It makes me think of caged animals at zoos.
For most people in most companies, the choice is between hacking something together in Excel over the weekend that will mostly work (with a few bugs); or to give your project to the IT department and wait at least six months for them to fail anyway.
Excel is an amazing piece of software if you are disciplined enough to use it well. Hell, I've even solved some Advent of Code problems on it: it's really powerful when you enable iterative calculation
As I always like commenting when I see these kinds of comments: many thanks for (in a general sense) providing consulting/freelancing/general small business work well into my future.
I second that. While it has plenty of flaws and is getting very dated, Excel has been a phenomenal tool in the land of the analysts and others who actually need to get work done and move on.
I had a big OS migration project, where we moved all the RHEL machines to Ubuntu. We had a big set of Python scripts that would go out and query a bunch of databases and logging system to get the current state of the migration each day, but we published the results as a spreadsheet that was emailed rather than some website. It worked very well to get people data they needed and to put it in a form where they could ask their own questions of the data without talking to anyone else. It worked better than I expected, having not used excel for decades.
Excel ate my data four times in the past week. I prefer LibreOffice; I think it is much easier to control. Also I can tell LibreOffice not to eat my data and it listens to me.
Yes, but Google Sheets or shared Excel files in OneDrive. Otherwise the magic that is Excel gets lost in various versions in people's email attachments. Collaboration + Spreadsheet though truly is a versatile tool.
The most common use case is via seed document. People grab the seed doc, modify for their specific case, input data, get results, stuff into Powerpoint, done, next.
Excel files in onedrive don’t work very well. Some features are disabled (even merging cells), so very quickly you now have various versions plus a version on onedrive. You may think there is one master file but there probably isn’t.
Google sheets are better (and plotting and pivot tables are much easier to use) but there’s always someone who downloads as excel and makes a new version. I almost think google drive with much more friction to export would be better.
Google Sheets is for the many companies that use Google. It’s definitely not ubiquitous, but then Excel isn’t either (my company only has a few Excel licenses for the people who are customer facing, everyone else gets Google Sheets).
I love VBA, it’s so easy and powerful. My first job was being a developer/consultant at a big 4 firm, developing a disclosure management software with VB6. It even had a ORM to map objects from SQL queries. It was lightning fast, Office integration so superb. Later it was ported to .NET with a proper client/server architecture, still it never reached the speed and simplicity of the VB6 version.
People are downvoting you but are overlooking the fact that it's not always about making things in the newest tech but more often than not about making things work. Most software will only be seen by industry specific users that don't care that the program is React Native and runs on AWS. A simple VBA script is sometimes completely sufficient to input the data into a spreadsheet. Scott Hanselman called devs doing this "Dark Matter Developers" [1].
32bit VB6 programs are still able to run on modern Windows 10 machines, even on x64. The hours that would've been lost on porting everything to the newest macOS can be spent on new features or customer wishes. With the .NET Framework it's even possible to seamlessly use VB and .NET in the same program which combines old and modern technology. It's officially unsupported but it works and even gets bugfixes sometimes.
That’s a little far, old doesn’t mean good. The bleeding edge often gets abandoned, but tech has generally been improving over time. IMO, the sweet spot starts when something was a fad ~5 years ago and is still reasonably popular.
A good reason to wait a few years is precisely so that we have a clearer picture of what strengths and deficiencies a piece of tech brings to the table, especially out in the real world.
We have a good picture of how Angular and React works in production now. We cannot say the same for something like Svelte.
De jure deprecation, correct - but reading between the lines in Facebook's own blog article that introduced Hooks ( https://reactjs.org/docs/hooks-intro.html ), which put a bit too much emphasis on "There are no plans to remove classes from React.", which to me means they're definitely going to be deprecating class-based controls in the future.
The MVC and ORM revolutions are the counter-argument to this.
Rails/Django, then ASP.Net MVC/Laravel/whatever Java was was a massive step forward. If you didn't use it, you were hamstringing yourself. People switched to Rails in droves.
As were using ORMs. You'll still get people quibble about this, but never having to go through the tedium of updating 101+ SQL statements when you add a column in a pain you young'uns will never know...
JQuery was actually another example. Cross browser Ajax statements were annoying, plus just manually adding individual HTML nodes in HTML was laborious.
> If nothing stable, decades old, and well documented can solve the problem then reach for hot new-ness
I think it's a little more nuanced than that. Usually the older technology is more stable and better documented. But not always. Sometimes the new hotness is the new hotness precisely because it's better in these kind of categories.
It's 100% true, but only in broad strokes. My personal ladder is
- paper
- text files / spreadsheets
- desktop apps / local db
- SSR web apps / single database
- SPA or mobile app / cloud storage
The only thing that challenges that is the reality that around a decade ago smartphones with tiny screens and no filesystem access eclipsed desktop computer, and we're still reeling over how much more expensive that makes development.
Are SSR/SPA solutions really that much more expensive than local apps to develop?
The start of my ladder looks much the same as yours except I jump from local files straight to single database SSR/SPA. Maybe slightly more complicated than a local app because you need auth, but when prototyping something I just use basic auth to start.
Chuck it up on a $5 DO box and you instantly get:
- Multidevice access, desktop and mobile (write mobile-first css and the tiny screens aren’t a problem)
- a UI that’s trivial to hack on (HTML and css are easy)
- likewise, a technical base that’s easy to extend. Drop in react, build out the api side and use it as a source for other projects, etc
All this assuming you have reasonably reliable network access, and even then you can build it as a clever PWA falling back to local storage if you need to (I think, prototyping that is still on my todo list)
Heck, save yourself $5/month and stick it on a raspberry pi at home.
It’s truly a golden sweet-spot for personal software tools. The only external dependency that irks me is needing a domain name.
All of the constituent parts are easy. It's when you tie it all together that it starts to get complex. There is no doubt there are many more moving parts in a modern web app compared to desktop. I did a few VB projects and I needed know two things, VB6 and a database.
Of course we can't go back, enterprises are rightly presenting their systems directly to the customer, you can't do that with a desktop app. None the less it does feel more complex than it needs to be.
> I did a few VB projects and I needed know two things, VB6 and a database.
Desktop apps are relatively simple if you only need to target one platform. Both windows and macOS have good options accessible from managed languages. But if you want to do cross platform then the complexity rockets.
Yes they are more expensive to develop. With a winforms or wpf gui you can iterate much faster than with a web one while supporting rich interactions, thinner tech stack, faster feedback loop.
My checklist would be (I'm eager to know other people correct me) suitable (does it some my needs) , stable, available (including documentation, live q&a resources like stackoverflow, blog posts explaining how- not new ones but existing and up to date regarding the version available), and simple.
I try to adapt my checklist to the problem at hand. Sometimes I'm making something as a hobby project, or to solve a problem right now. Sometimes I want to write code that will outlive me, and sometimes I'll just throw some code at the wall and call it done.
Each situation necessarily results in different priorities and tools. I wouldn't TDD a gamejam project, and I wouldn't start a multi-year project in zig - at least, not yet.
For infrastructure projects I want well written deps which are simple, easy to use and have good documentation. When I'm evaluating something I often read bits of its source code (eg to figure out how to do something not listed in the examples). You get a sense of where to put things that way - actix (the actor library, not the web library) is very carefully designed, but seems to go a bit overboard inventing new concepts (+ associated traits). Tide feels pragmatic - its a bit sloppy with allocations, but it doesn't seem to really care. It wants to be fast enough and good enough while being simple to use.
For hobby projects I like to follow my nose and pick whatever seems shiny. Over the last few years I've learned svelte, typescript, snowpack, rust (and some rust libraries), zig, wasm and other stuff. I like to make some risky bets and then just play the hand out and see what happens. And I use that as fuel for when I make longer term projects. I'm making a little database at the moment and I'm using rust - which is much slower for me to write (compared to nodejs) but it matches the values of the project I'm working on to a tee.
20 years later it seems I spend months fighting overwrought frameworks like Webpack and React to get the equivalent productivity I could get in one day with VBA.
My frustration with VBA is that it didn’t seem very well documented.
If I could record a macro that was “close” and examine it, the ecosystem was very productive. Once I wanted to go beyond that, it seemed like there was an undocumented chasm to cross.
It wasn’t the VB part of VBA that wasn’t well documented, but rather the “for A” part that was spotty. The application automation model wasn’t as thoroughly documented as most of Microsoft’s stuff. This was well after the Internet was seriously prevalent.
Things like destroying [iff present] and recreating Excel charts from scratch, including generating all the series labels, shapes, etc., updating elements in a word doc from an excel sheet, generating on-slide progress indicators in PowerPoint.
VBA is great for many things but it has its flaws.
The biggest problem with it is really that the tooling around it is decades old so it hurts your productivity. Maintaining VBA code bases is a painful experience, the IDE sucks and the language has enough quirks and shortcomings that it forces you to take long detours to accomplish what would be very simple tasks in more modern languages
The loving part of VBA is its interoperability across the Office suite, but there's no reason why that couldn't be done in, say, Python
Oh, I almost forgot. VBA classes are absolute misery.
The loving part of VBA is its interoperability across the Office suite, but there's no reason why that couldn't be done in, say, Python
I agree with you, but Python and pretty much every code environment is missing a few things that create a pretty high barrier to entry:
For a whole lot of cases, VBA is not even needed. People put data into cells, operate on it with formulas in other cells that drop output into still other cells which are then used to get output.
Input can be almost anything these days.
Formulas have a well defined, easy to understand syntax that work across a wide variety of operators. Simple copy / paste operations make sense, often with the intended data mapped right in. (given people are a little organized)
Output can be almost anything these days too.
And it's live. Make a change, see it happen.
That's real power! People don't have to know much to make it all work either.
I have been using Excel to transform business data for years, model business and a lot of other things, and as a rapid prototype system. I can write code too. Often I do, but the more specific and or variable the task is, like a one off need to solve yesterday, the more attractive just banging it out in Excel becomes.
Should one get super crazy, have one of those outputs from Excel be a working program. No joke. A script file is one of my favorite outputs. Mash the data up in Excel, and once the plan of attack is clear, execute the script and watch it run on the real system.
It's a perfectly usable, and I would suggest one of the easiest, assemblers I've ever seen! I ran it on my mobile. Crazy.
I just used it to knock out a little routine for a retro-game project I'm working on and was kind of stunned at how lean, accessible, functional this really is.
For Clarity: Replacing VBA with something else costs more than the value add at present, and it's because Excel is the gateway drug into VBA. By the time people reach for VBA, they already are familiar with a lot of it.
VBA is very stupid, and I facepalmed a lot while learning it, but after you get used to it you can write in a very functional, clear, low/no-side effect style, with reasonable polymorphism using interfaces. It's still bad, but it's available. And I can make the UI out of spreadsheets and output still more spreadsheets, so business people aren't scared of it at all.
edit: I'd certainly rather be writing VBA than js, which is what the seem to be replacing that type of automation with, not python.
VBA and VBE being replaced with Python would require so much work... and there is a crazy amount of code out there doing an equally crazy amount of work too.
All the points I put here are why doing that replacement work doesn't really add much value.
I understood you. My reply above was in relation to your:
> For a whole lot of cases, VBA is not even needed. People put data into cells, operate on it with formulas in other cells that drop output into still other cells which are then used to get output.
* > Input can be almost anything these days. *
Nobody is arguing VBA is always needed and inputs being almost anything has no bearing on VBA's shortcomings.
* > Formulas have a well defined, easy to understand syntax that work across a wide variety of operators. Simple copy / paste operations make sense, often with the intended data mapped right in. (given people are a little organized)
Formulas are great, no argument there. Still irrelevant to the "VBA has plenty of shortcomings" discussion.
> Output can be almost anything these days too.
> And it's live. Make a change, see it happen.
> That's real power! People don't have to know much to make it all work either.
You're describing spreadsheets. I love spreadsheets. I should, as I often spend 100 hours in a single week working with them.
> (...)
> For Clarity: Replacing VBA with something else costs more than the value add at present, and it's because Excel is the gateway drug into VBA. By the time people reach for VBA, they already are familiar with a lot of it.
You haven't really proved that point at all. You talked about spreadsheets and then concluded something about VBA, which doesn't follow.
————————————————————
As for your reply above
> VBA and VBE being replaced with Python would require so much work... and there is a crazy amount of code out there doing an equally crazy amount of work too.
The best time to plant a tree was 20 years ago. The second best time is now.
The same was true about Excel 4.0 macros and yet we did it. Python or [insert your favorite language] doesn't need to replace VBA overnight. It can be available alongside it, just like VBA was available alongside Excel 4.0 macros for decades when introduced. Unsurprisingly, XLM felt out of fashion and VBA took over as the superior choice.
> All the points I put here are why doing that replacement work doesn't really add much value.
Sorry, but you really haven't made those points.
> Which is why VBA is still a thing.
VBA is still a thing because Excel has no real competition as an Enterprise spreadsheet app. But its popularity and prevalence don't speak to its quality.
>the language has enough quirks and shortcomings that it forces you to take long detours to accomplish what would be very simple tasks in more modern languages
I've already mentioned VBA classes. If you want to read , just read through Chip Pearsons' great Arrays library. 30 functions just to do what you should be able to do natively in a high-level scripting language such as VBA
Imagine how painful your experience would be writing any decently complex reusable code in VBA without knowing about all of the edge cases covered by Chip in that module
Oh, I was mainly interested in your view to kick off a conversation. I wasn't sure what you meant by long detours. I don't program in VBA, so I was interested. From the outside, it seems like a powerful language for non-programmers to implement business logic.
Sorry, didn't mean to sound rude at all. I guess I was a bit on the defensive given all the pushback I was getting elsethread
It's hard to really pin it down to a couple of things, but after spending some time in it, you quickly feel the ergonomics aren't really great. It's just a lot of typing to get basic things done like inserting an element into a 1-d array.
For such a high-level language and one directed at non-programmers as you mentioned, you'd think that sort of tooling would be available natively
What surprised me the most is that most developers can't actually use Excel properly. Most developers struggle even with really basic Excel operations while a lot of non-programmers can do advanced and useful things with it.
For exampe: We used it once as a bug tracking tool and all business people could easily work with it while developers didn't understand that they should use the drop-downs in the cells to define the status and not type something in the cell. We had to continously fix the sheet and explain the developers. While they were complaining about Excel,it was obvious that the lack of basic knowledge of how to use it properly was just lacking.
The same is true even to a greater extent for Word processors. Only a small part of the users can properly format a document with a correct usage of headings and sections.
True. The problem is people share the bug tracking spreadsheet over email, and you have to find out the latest version from inbox. I still don't know what the best solution could be for both business people and IT people.
For word documents, I write in markdown and check in to git which allows me to compare the difference, etc. And I use pandoc when I distribute it as a proper document.
People do this in my company even despite us having SharePoint which has decent enough support for collaborative capabilities and should in theory get rid of any need for different document versions, but that escapes many people.
This seems like a conversation from 2010. I'm not saying that to insult, but it's literally the kind of conversation I was having in a corporate environment 10+ years ago.
Now, why can't you just share the link to the Excel spreadsheet and work collaboratively on the same document? If it's stored in SharePoint or OneDrive, this is trivial. There's a "Share" button in the top right corner, and you can do it from Windows Explorer as well. There's little value in emailing versions around anymore, particularly internally.
Your example is a bad one in regards to users, be it devs or otherwise, but is a good example of how half-backed software is then given as example. In your case, if the goal was to only get values from dropdown, then make those cells behave properly. Constraints are easy to implement to allow only get values from dropdown and no typing allowed.
So I'd blame the original developer of said Excel form instead of its dev users.
What you say about word processors is pretty accurate. I'm pretty sure I can use word well enough, but I always just end up writing markdown because it's pretty much impossible to screw up the formatting with that.
I think the biggest thing a lot of developers miss with the bashing of Excel and VBA is just the simplicity to the end user.
As a consultant, a lot of time the solution has to be able to be given to anyone, on computers with no admin rights or installation privileges, and just work with no other dependencies, installation or special skills required.
Very, very few other methods meet that requirement as well as a plain old Excel file.
I've been stuck in an Excel only situation and it was extremely unproductive, and I knew with certainty that going past a very low complexity threshold would make my code 'write only', subject to bitrot when it would eventually be thrown away to be replaced with yet another spreadsheet.
Although spreadsheets are not great computational models for many tasks, they can be made to get the job done. The problem is easy introduction of errors and maintenance pain -- enhanced by the fact that 'anyone can do it's means no one owns solving the problem and staffing it someone with coding skills. It's like leaving basic toolboxes around the office and saying 'no we don't use electricians/plumbers/carpenters, too much overhead, just DIY'.
So yes, consultants often get the job done with Excel+VBA, a few Access DBs etc. Sometimes it is self-maintainable. The fact that Excel can get the job done (and comes with inbuilt software updates and budget) makes it easy for IT. But it is doing business critical work, using it is only papering over capability gaps.
I think such situations are really something that needs to be felt out -- I'm not really a programmer, but I know how to code, and in my line of work, it's a benefit for sure. We don't develop projects for external or internal clients, but there are times when being able to punch things out in lowest-common-denominator programs/languages is really handy, and the idea of long-term maintenance isn't really a factor in such decisions.
It's all about the problem-set you're working with; the problems I come across are either:
1. Time-consuming but rare tasks that require a pretty intimate understanding our product's code; one-off solutions in various lowest-common-denominator languages are fine, even if they need to be updated eventually because typically the problem is not so common.
2. Simple, static, non-changing tasks that can have one well developed script from the beginning and the only "maintenance" is QOL maintenance.
When these are the problem sets you're working with, the burden of knowledge typically is not so heavy to transfer. They aren't urgent "our company/workflow dies if this sheet/script fails", so there is time for someone to explore and learn.
I get a lot of interns that have never touched code in their life that cut their teeth on little projects like this, and in the event that we don't have someone interested in such things, even then someone still picks it up, just not on an ideal timeline.
I do get what you're saying on the 'anyone can do it' problem basically just becoming a warped version of a prisoner's dilemma where the end result is no one does anything, but at the same time taking control and mastering such workflows and lowest common denominator languages helps inspire and grow people. Powershell is great for this (and I really don't like powershell), and Excel does similar things with the mind especially once you hit the limits of what is built into the UI and start looking at scripting as a solution.
Excel, Powershell, and other such things are burdensome and have many rough edges to cut on; but they are extremely empowering for basically every user, and often a gateway to showing some people a skillset they never realized they had.
Yes that's a fair statement, but I think you can get a lot further with 3 day python course and a locally hosted Jupyter instance (and from an endpoint security perspective, no need to worry about VBA or PowerShell on the clients either).
> First of all we have to say that you can interact with Outlook directly from other Microsoft Office apps via VBA using the object Outlook.Application.
I haven't seen any corporate deployments that permit jacking around directly with Outlook in years.
In particular, the transition to .*x file extensions some years ago marked the a heavy lockdown of all my favorite VBA stylings.
To give a customer an .xltm with the capability to import .json files with tidy formatting, it was necessary to package everything as a .docx and then construct the target on his machine.
I thought this was a great examination of unintended consequences and a problematic (or unevolved) security model. I am sure it was useful to allow Excel to manipulate other Office programs, but in a networked world the surprise applications of this are dangerous.
I mean, being able to search for "password" in emails from Excel? What could go wrong?
As with other Office applications, if internet originated document contains VBA code, Excel opens it in read-only mode with disabled code execution and displays warning.
> I am sure it was useful to allow Excel to manipulate other Office programs
Your understanding is backwards. Other Office programs are not special, Excel can manipulate absolutely anything as VBA has a complete access to full Win32 API, that Outlook.Application is a COM object that can be accessed by any other Windows application including VBScript and PowerShell in about the same way.
Worth adding Excel’s default .xlsx format won’t even save macros.
And as Raymond Chen would say, if you have macro execution you’re already on the other side of the airlock. Being able to P/Invoke from VBA is really handy too!
This is unreal... I thought these kinds of things would've been fixed in 2000 with the ILOVEYOU virus [1]. But apparently Excel VBA allows you to manipulate the registry, access Windows APIs, drop files onto the system, and manipulate/access Outlook to run macros across all of your mail and more. Does Microsoft really expect the average office worker to not fall for an urgent-looking, highly-realistic spreadsheet asking him to enable everything? If this is really what I think this is it's an obvious security disaster for the whole Windows ecosystem.
It's a tool, and as any tool can be use for good or bad things. I don't hear you whining about Chrome/Firefox/whatever extensions written in C++/JavaScript/whatever that can be keyloggers too.
Like many incumbent thing with network effect in the world, excel is both terrible and useful. But that is the beauty of software industry. You don't have to be perfect to win. You need to be faster, better and cheaper than current solution.
For the moment, the faster and better solution than Excel might not be cheaper. So we are stuck with Excel, until someday someone really comes up with a faster, better and cheaper solution.
I love excel but it feels like it's UI based abilities have been left in the dust. It's like development of excel and VBA stopped and even reversed to make things function worse. A few new features, forms, buttons, etc. would be a god send for more complex systems.
A key stakeholder of our product is constantly pushing us to make our ui more excel like.
As a product we are happy to oblige, but the cynic in me just wonders why they don't just use excel and close the loop on their csv import / export workflow
Do not give them ideas, on the contrary, give'em more rope. And when the time comes and they come with the idea themselves present yourself as the unique person that can do the migration to Excel, since meanwhile the codebase would be in millions of lines of code and you're the only one left to understand it. And that's your moment to ask for the big bucks as a contractor.
Always remember the golden rule of life: stupidity and intelligence must be paid (their stupidity and hopefully your intelligence).
Both over web apps, locked to work on Edge on Windows 10 by a corporate policy. Screw you for violating mental health of generations of IT people, Microsoft.
Not saying Excel is perfect. Lots of us have built or are building products that do a better job at specific workflows. But surely it is one of the best pieces of software we have, and if I could only use 1 piece of software forever, it’s probably Excel.