The worst of the two worlds: Excel meets Outlook
adepts.of0x.cc
adepts.of0x.cc
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.
:D
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.
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.
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!
Tech folks: That's a business use case
Business users: This is something I do 500x a day, this I do 10x a day, this I do once a month
PMs are supposed to be the ones to do this work, but they tend to be technically clueless and blinded by project timeline spreadsheets.
BAs usually don't take the time to actually ask the user "Why are you doing this?" and get all the nuances.
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 get that most of us live in a land of "Of course I have an IDE and compilers / REPL" at work.
You know what IDE and compiler IT can't take away? And the last option that's always available at any company?
Excel.
(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')
https://mcpmag.com/articles/2015/11/04/net-members-in-powers...
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.
IT says no to or unnecessarily complicates acquiring a needed product? Fine. In Excel it goes.
It never matters how un-auditable the mess is. That never enters the calculus.
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.
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.
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.
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.
When I saw this, I was blown away.
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:
https://docs.google.com/spreadsheets/d/1S1JXe3kZeqzxBOVXMo2-...
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.
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.
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 could also just use MATLAB but I'm more used to using python.
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.
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.
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.
Ctrl+Tilda toggles show/hide formulas, which helps identify input cells when you inherit a messy spreadsheet.
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.
It's not perfect, I wish it would automatically format that way, but it certainly makes maintenance easier.
https://insider.office.com/en-us/blog/let-names-in-formulas-...
And it's a hack, but you can put text comments inside an N() function and add them into your formulas.
https://support.microsoft.com/en-us/office/n-function-a624ca...
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.
I can’t speak to what gets maintained in Excel, but compared to some Win95 program, it’s in much better shape.
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.
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.
I asked them why they do this, but haven't received a reply, perhaps because my question was in an attached gzipped Postscript document.
They shouldn't, but it's amazing.
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".
They likely aren’t bugs. Some may have been. But they’re now expected behaviour. Moreover, by and large, they’re how non-programmers see the world.
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.
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.
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 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.
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.
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.
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.
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...
Use Get and Transform Data -> CSV
You can control the typing per column when you import, split columns, etc.
Like new+custom data types: https://www.microsoft.com/en-us/microsoft-365/blog/2020/10/2...
And LAMBDA for custom functions: https://www.microsoft.com/en-us/research/blog/lambda-the-ult...
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 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.
“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*
Emacs has ~10 spreadsheet modes, including one for free-form calculation and one for reading Excel files (https://www.emacswiki.org/emacs/SpreadSheet).
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...).
We had a programming language that matched that description ...
Visual Basic 6 -- and Microsoft killed it because it wasn't making them enough money.
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.
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.
Interactive .NET sessions have been working for several Visual Studio releases, at least all the way back to 2010.
The only issue is the anti-VB.NET atittude from some VB circles.
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 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.
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.
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.
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”.
Local parts of the MNC's end up using the centrally required system, as well as their own local shadow system.
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.
> 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.
If it didn't cost a fortune to create and modify CRUD apps, Excel wouldn't be so popular.
Why does Airtable, which is Excel in the cloud with a few trinkets cost so much and do so little, despite 100+ million having been poured into it?
Because developing software is a nightmare every step of the way.
Until that's addressed, we are stuck.
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 ...
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 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.
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.
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.
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.
1: https://www.hanselman.com/blog/dark-matter-developers-the-un...
But new tech should be the last resort.
If nothing stable, decades old, and well documented can solve the problem then reach for hot new-ness
That would mean it's okay to use Angular today, but dev trends (and Google's support "policy") would advise against that.
We have a good picture of how Angular and React works in production now. We cannot say the same for something like Svelte.
If one wants stability, then PHP has a way better track record.
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.
> There are no plans to remove classes from React.
They're totally going to remove classes from React.
> We are going to remove classes from React.
They're obviously going to remove classes from React.
> No comment.
They're totally going to remove classes from React.
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.
In descending order of importance.
When choosing between any technology choose the one that is most stable.
If multiple options are on a stable release, choose the one that is best documented.
If multiple stable releases have excellent documentation, choose whichever is oldest.
I don't think this framework is always (or even often) right. But it works for me
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.
- 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.
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.
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.
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.
Apparently they are all now learning Elixir after a short migration wave over to Clojure.
C++, Java and ASP.NET over here for the last 20 years.
But my private NVBC (NecroVisualBasiCon) repo has some great stylings that I tap into at least once a year.
Nevertheless, when are they going to relase Office with Python bindings included?
Somewhat clunky and slow but it does work.
There are basically three VB ecosystems.
Visual Basic which many refer to as VB6 and it represents the last version every released.
VBA which is Visual Basic for Application and lives on to this day as an embedded language for Microsoft products like, Word, Excel, Outlook etc.
VB.Net which is an implementation of the Visual Basic language running on the .Net Framework.
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.
Almost everything in VBA can be found with a simple Google search, and Microsoft's technical documentation online has gotten very good.
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.
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.
Check this thing out:
https://github.com/tilleul/apple2/tree/master/tools/6502_ass...
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 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.
Which is why VBA is still a thing.
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.
I also agree on quality.
What I do not see is the value added to improve quality.
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.
Any examples that you've come across?
http://www.cpearson.com/Excel/VBAArrays.htm
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
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
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.
So I'd blame the original developer of said Excel form instead of its dev users.
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.
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.
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.
https://docs.microsoft.com/en-us/office/dev/add-ins/develop/...
Still, I’m disappointed MS didn’t implement a typesafe lang such as C# or F#.
On Error Resume Next
On Error Goto HellI 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.
It's one way to do security, I suppose
I mean, being able to search for "password" in emails from Excel? What could go wrong?
> 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.
This I believe will be a short-lived hack in a properly setup enterprise environment with proper GPOs in place.
Saying that my eyes have seen a lot and especially if you leave it up to the users, they'll click their way through any warning messages so...
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
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.
Excel is an good example;