You wouldn't believe what sort of processes in very big banks/financial institutions are built using 10 year old VBA macros. In fact, VBA consulting for finance is a very juicy cottage industry at least in Europe to this very day.
On a serious note, I dread excel. If your PC is set to german, excel will translate the VBA keywords to german. But if you want to type them, you have to do that in english and then have excel translate them.
I don't want to accept that crap like this is the standard.
At least my spaghetti code goes into one direction only...
Excel has long had an option to display 'precedent' and 'dependent' cells.
You can actually see the spaghetti right there.
https://support.microsoft.com/en-au/office/display-the-relat...
Huh? At least in the versions I've used I've always needed to type the commands in German.
There's even an online German - English Excel dictionary...
It was an excel file created in pandas that threw an error on english commands but worked if the code included german ones.
I don't know how or why that happens, I just know I need to get away from it.
It's great for laying out things meant to print, and making invoices and stuff... But why didn't we have code files and proper fixed layout DB-style tables as "pages" that can go in a workbook?
Maybe keeping everything as 2D as possible is a necessary compromise for the spatial thinkers out there, and they just wouldn't want it if it was full of boring linear stuff.
I love the reactivity and the concept that anywhere you put a value, you can put an =expression. But the 2D stuff seems like it's for the people who always have a sense of where things are in space.
They've done a good job of convincing people that it's not programming and they can do it, I can't really complain, because if Excel didn't exist we might all still have to use paper on a regular basis, or completely unstructured text files.
You would think the ideal would be to allow everything Excel currently does, but also have some other model with more separation of code, data, and visuals, like node-based programming and separate cloud-synced pure tables.
Excel can almost cover a lot of "real programming" use cases, but not quite. The upgrade path seems to be Excel to Access to Custom app, rather than Excel to Excel but with more of the advanced features.
But I really like Excel for allowing me to just plop stuff down wherever when I'm brainstorming something, or just want to do a one-off calculation before I quit-without-saving.
Then it's pretty bad but still better than writing some custom software like people would probably do without it.
Because we have been doing it that way for almost 4,000 years!
https://en.wikipedia.org/wiki/Spreadsheet#History
Humans have organized data into tables, that is, grids of columns and rows, since ancient times. The Babylonians used clay tablets to store data as far back as 1800 BCE.[16] Other examples can be found in book-keeping ledgers and astronomical records.[17]
Since at least 1906 the term "spread sheet" has been used in accounting to mean a grid of columns and rows
What you want is the paradigm of Numbers from Apple. Their office suite is good, and it has the benefit of being opinionated. They're not trying to ape Microsoft, like all the open-source projects.
The main danger in Excel is that any area of a sheet can be treated as a pseudo table.... except it really isn't, and you can inadvertently sort some fields, while leaving the others alone, effectively scrambling your data.
A lot of people using Excel, even some of the more advanced stuff like if statements and logic, don't even realize that they're writing programs, and I think that's genuinely awesome: people are able to utilize the power of programming by accident.
I still use spreadsheets all the time for small number crunchy things, just due to how it's "reactive by default". I find it's actually really useful to immediately see everything update after changing a value. Admittedly, I mostly use Google Sheets nowadays simply because it's "good enough" and much easier to share with friends. Could I write a program to do all that number crunching that performs better? Obviously yes, I could start a new Julia project and mop the floor with Excel or Google Sheets in regards to saving cycles, but that would take me 20x longer and I'd lose all the nice features of a spreadsheet.
Granted, this is coming from a strictly Anglo-American perspective; I cannot speak to Excel's ability to use other languages.
Free, fast, accurate. Pick any three!
I find it's an overblown issue. Those of us that prefer functions in English can just set Excel to use it. When someone sends you a file it displays in your chosen language. And having the default being localized makes it usable for the majority of people who don't speak English.
I once had to update it to optimize (minimize) front-line staff working hours so the company didn't have to pay health insurance for those employees. A real nightmare of a task in more ways than one!
It was a chemical safety database. It was used to identify chemical risks and track the storage of the chemicals.
I pray near daily that someone else came along years later and rebuilt it in a modern language.
You can make Excel do some real wacky stuff. I have a spreadsheet that actually calls out to exec() to run a curl POST on commandline and consume REST API endpoints, parse the results, and update the spreadsheet -- why on earth?? because the API was ready but the web app was delayed. I was the fix. :D
VBA is kind of the result of people only - ONLY - wanting to use Excel for everything. I work with those people. They have mastered excel, but have little to zero interest in learning anything else, and would rather see the world be built around excel.
So you (like me) get tasked with building applications and forms in VBA.
I was STOKED when MS announced Python for excel, but alas, turned out to not be what I (and many other) wanted.
What's the medicine? Dunno, hire analysts that are more open to using other tools . Don't get me wrong, I love using excel for many tasks - but damnit, it's not the only tool.
I might be mistaken but wasn't VBA brought into Excel as a familiar element from the VB world?
It is true though that some of the more purer or more initial concepts of VB likely live on in the VBA subsets of Excel.
And Access.
It wasn't difficult, but at its core, I had to do it because the end users didn't feel like using some other interface - they really didn't want to leave their excel spreadsheet.
Currently working for a consulting company with a whole region's health system as the client. They use excel for lots, but it's all very basic stuff. I had one of my team members spend an hour whipping up an excel form for them that auto generates letters to different departments with all the necessary information. Even some basic standard work forms, let alone any sort of automation, would help them a lot as they rely on people to send certain information that gets missed every time. They described our excel sheet as a game changer for them.
Almost no-one has access to their ERP system which is safeguarded by a certain department which is ridiculous. I'm working on a spreadsheet for their HR team to calculate bonuses for certain employees based on a bunch of variables, then auto-generating letters to review and distribute. The data from their ERP software is such a mess, but I'm making up for it by cleaning up their reports in excel. I plan to get access to their ERP system to look at what kind of reporting I can do as HR only gets a report from the system once a month. I want to help them track real time stats for hiring, etc. And curious if I'm able to connect some spreadsheets to their ERP with an API or something (haven't done anything like that before).
Anyways, that professional development for excel book looks interesting. I see the second version is from 2009 and may not even be up to date with 2007 excel. I'm sure most of the concepts would stay the same though, so I'll definitely have to check it out.
I realize excel wouldn't be considered the most professional or robust way to build applications, but since microsoft 365 seems so standard and everyone uses excel, it makes sense to me why so many organizations use it. There seems to be a lot of potential to apply some excel automation in a lot of industries, especially ones that already rely on it as others have mentioned in this thread. I use it as a means to an end when helping clients, but I also see dollar signs as I find ways to build things that can be applied to so many industries.
Excel is perfect for building proof-of-concept apps and Microsoft has a cloud offering called PowerApps that use a somewhat similar "Excel concept." I have built a significant app in PowerApps...not recommended. If you don't have a development team Excel is good. Same for PowerApps. Very painful if they get big. Keep things simple.
Vernor Vinge figured this out 25 years ago. A Deepness in the Sky depicts a human society thousands of years in the future, in which pretty much all software has already been written; it's just a matter of finding it. So programmer-archaeologists search archives and run code on emulators in emulators in emulators as far back as needed. <https://garethrees.org/2013/06/12/archaeology/>
(Heck, just today I migrated a VM to its third hypervisor. It has been a VM for 15 years, and began as a physical machine more than two decades ago.)
http://www.pages.drexel.edu/~bdm25/gnumeric.pdf
Also lack of accuracy in calculating some complicated statistics that "nobody" uses is not really a calculation bug.
Also most of those come from the fact that Excel has a precision of 15 digits.
Let's see it. Fill the visible part of a sheet with it, conditional format the cells, red for negative etc.
hit f9 to recalc and see a big bunches of cells turn red. Fixed random function to return a random number between 0 and 1.
I haven't worked on gnumeric for a long time - examples came from gnumeric.org
Excel was really bad for everything beyond basic arithmetic, way beyond the important issues with floating poiny listed here https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.h...
Anyone seen anything about ms actually fixing the bugs? I haven't.
I am not sure what that means.
Excel keeps all the bugs for backwards compatibility. So the sheets made years ago still provide same results. In few cases the depreciated some functions -> the old ones still work, but are relatively hidden and the users are encouraged to use the new ones.
They probably should do the same with the statistical functions that supposedly have problems due to rounding. But that cannot be fixed - precision is up to 15 digits.
Also if you wanted an article that talks on Excel precision, you can start with the wikipedia page:
https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft...
Excel has many problems, but you linking to few website that supposedly prove that "Excel bad" - but at the same time - those exiles simply dont work, is somehow very funny.
>Excel keeps all the bugs for backwards compatibility
They /fixed/ rand(). Announcements. Fanfare. The fixed version returned numbers less than zero.
yet again excel's calculating issues go way beyond double precision floating point truncation.
I've been trying to get him to use Julia for that second step since I absolutely hate Matlab, but I honestly don't fault him for using Excel in the initial phase.
To that point, I imagine that there are a LOT of admin jobs (or, at least, a lot of tasks) that could be almost completely automated away. It's probably not even a capability issue, but one of job security on the employee side and a lack of *waves hands vaguely* on the employer side.
Afterwards, forget it. Maybe one.
If Excel stops working, financial institutions around the world would collapse.
Actually that's really awesome, I think.
My goal is basically to never touch Excel again. But, I have to admit - that 2D mixture of data and code called "a spreadsheet" is honestly a pretty cool programming paradigm for a lot of tasks. It can be horribly abused... but so can everything.