There is a book called: Professional Excel Development. If you want to get into it. You could probably use that book to build an OS in Excel. I'm not joking.
This sounds like it would be right up my alley. I'm not a professional developer of any kind but have done hobby coding on/off for years, and I'm better at excel than the average user.
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.
Make sure you build subs (functions/methods) for everything....I mean everything. Break all of your code into the smallest subs possible with clear names. Otherwise you will not be able to make heads or tails of your own code in a few months. I built a fairly sophisticated VBA project and left it for a few months, came back and was pulling my hair out. I had to refactor before I could move on, and it was very painful. After the refactor I could make changes and modify it without issue. You have to be, what seems like, over-granular. Its just "clean code" principles: the name of the sub should describe exactly what it does. If the name is too big...break stuff out into different subs.
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.
