8 ms·

Does VBA for Excel count? Because if it does then VBA for Excel has reached the"nuclear resistant cockroach" level in finance. You wouldn't believe what sort o

Does VBA for Excel count? Because if it does then VBA for Excel has reached the"nuclear resistant cockroach" level in finance. 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.

datavirtue 3y ago
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.
- jandrese 3y ago
  It's no more crazy than trying to build an OS out of a web browser.
- Flowzone 3y ago
  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.
  - datavirtue 3y ago
    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.
nottheengineer 3y ago
Excel is literally 2D programming. Us mortal developers who can only put lines below one another are incapable of comprehending it, so we only get to ask the wise finance people how their enigma works. 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.
- wiz21c 3y ago
  > Us mortal developers who can only put lines below one another At least my spaghetti code goes into one direction only...
  - eastern 3y ago
    Yes, but I bet your code cannot reveal its own spaghetti:-) 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-relationships-between-formulas-and-cells-a59bef2b-3701-46bf-8ff1-d3518771d507 https://support.microsoft.com/en-au/office/display-the-relat...
    - quickthrower2 3y ago
      Now do that for the VBA!
- rzzzt 3y ago
  With side-effect free functions: https://youtu.be/0yKf8TrLUOw https://youtu.be/0yKf8TrLUOw
- netsharc 3y ago
  > But if you want to type them, you have to do that in english and then have excel translate them. 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...
davidmurdoch 3y ago
I know of a restaurant franchisee with 170+ locations that uses a home grown ERP system built in VBA on top of Access by an accountant about 20 years ago. 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!
- matwood 3y ago
  My first programming job in the 90s while I was still in college was building systems exactly like you describe (and they were as poorly built as you think hah). I worked for a small IT programming/consulting shop. We did small jobs like this in town in addition to installing networks, IVRs, etc... while working on larger software to sell (which is an entirely different/crazy story that involved burning CD demos and using a hand 'stomper' to label and then mail them out).
- aj7 3y ago
  This post has more information than you realize.
  - davidmurdoch 3y ago
    What do you mean? Are you suggesting you can figure out who the company is?
    - mattgreenrocks 3y ago
      I have a guess, but don’t want to offend the big cheese there so I’ll just see how this plays out.
- protocolture 3y ago
  In high school I got a gig rebuilding a pre 2000 Access DB + VBA front end in office 2003ish due to some kind of incompatibility that was preventing them from upgrading their PC's. 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.
dauertewigkeit 3y ago
My brother is a mechanical engineer and apparently, there's lots of Excel VBA in traditional industries as well.
- bmj 3y ago
  I can confirm this: I know several mechanical engineers that do mission critical-type systems (think "power plants"), and they routinely use Excel VBA for calculations.
  - pillefitz 3y ago
    And it's often the best tool for the job
    - harry8 3y ago
      Other than the known calculation bugs in excel that MS refused to fix for at least a decade and probably still 20 years later. http://www.pages.drexel.edu/~bdm25/gnumeric.pdf http://www.pages.drexel.edu/~bdm25/gnumeric.pdf http://www.phusewiki.org/docs/2009%20PAPERS/SP06.pdf http://www.phusewiki.org/docs/2009%20PAPERS/SP06.pdf http://www.tandfonline.com/doi/abs/10.1198/tas.2011.09076 http://www.tandfonline.com/doi/abs/10.1198/tas.2011.09076
      - pillefitz 3y ago
        Only last link works
      - rvba 3y ago
        It is funny how you talk tjat Excel is bad and the examples just dont work. 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.
        harry8 3y ago
vb-8448 3y ago
So true!!! :D :D :D If Excel stops working, financial institutions around the world would collapse.
- daydreamtrip 3y ago
  Python was just added to Excel so there's not much chance of that
  - JohnBooty 3y ago
    Really?? Oh, wow: https://techcommunity.microsoft.com/t5/excel-blog/announcing-python-in-excel-combining-the-power-of-python-and-the/ba-p/3893439 https://techcommunity.microsoft.com/t5/excel-blog/announcing... 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.
schmookeeg 3y ago
Healthcare and insurance too! I transform into some glorious magical elf when I volunteer to do the VBA tasks nobody else understands or can lower themselves to do. 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
- eli 3y ago
  Pretty sure you can access a REST API from VBA without resorting to exec()
  - Shorel 3y ago
    It only works with toy examples and then stops working. For reasons unknown, as it should work.
    - pillefitz 3y ago
      Never had issues with the standard VBA HTTP Interfaces
  - schmookeeg 3y ago
    We support both mac and pc excel -- the mac VBA is seriously gimped.
aj7 3y ago
Before VBA for Excel, I wrote numerous macros, including ATAN2(x,y) before there was an ATAN2. Afterwards, forget it. Maybe one.
TrackerFF 3y ago
Pre-face: I write a lot of VBA 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.
- wavemode 3y ago
  The company I worked for in college was in the "VBA for everything" world. It was like the Renaissance when they started experimenting with Power Query for some use cases. (Which, honestly, was still a terrible language. But slightly more maintainable than VBA.)
- j45 3y ago
  VB is not VBA, VBA was a relatively small subset. 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.
  - TrackerFF 3y ago
    VBA works fine if you want to create macros and automate various things for your spreadsheets, but the problem is that the end user want / demand full-blown applications layered over their spreadsheets. The last thing I worked on was a CRUD -interface where the user could read and write data from their spreadsheet, to a azure DB. 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.
underlipton 3y ago
I used it as a coding layman in 15-buck-an-hour "admin" (clerk/secretary) roles to automate some processes that my predecessors had done by hand. Mostly just copying entries from a spreadsheet to company Excel/Powerpoint templates and printing them without killing myself copy/pasting or going into the save/print dialog 50 times. It did its work as something any old schmuck could harness to save themselves from carpal tunnel. 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.
Spinnaker_ 3y ago
And not just old school financial institutions. Jane Street used to run out of an excel sheet. Many hedge funds still do.
harry8 3y ago
10 year old you say.... office 97.
system2 3y ago
I can't imagine Excel without VBA. I tried Google Spreadsheets with js and it sucked big time.
anonzzzies 3y ago
I keep saying this to all the depressed new and old programmers who believe you cannot make money anymore and it’s all over; there are billions of lines of legacy shite running the biggest companies in the world. Not only Cobol and Fortran, but excel/vba, access, fox pro, and, indeed, old php. Companies have to choose, every year, to rewrite or maintain. I do many projects focusing on maintain; example; my last client had a quite to rewrite his systems in some modern crap (next/react) for a little over 8 million usd (that’s the estimate; not a fixed price and of course no guarantees by the consultancy corp quoting it) or, we will keep their ancient php and Java running for 25-50k/mo (few hours work per year to do that with Docker). I have a very low tolerance for rewrites so we go in hard against companies offering them: we make far far more profit than any of them; we run well over 50% with far less risk. Once I discovered containers (chroots in the late 90s and now docker etc), I knew rewriting things is never needed.
- TMWNN 3y ago
  >I have a very low tolerance for rewrites so we go in hard against companies offering them: we make far far more profit than any of them; we run well over 50% with far less risk. Once I discovered containers (chroots in the late 90s and now docker etc), I knew rewriting things is never needed. 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/ 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.)

datavirtue 3y ago

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.

jandrese 3y ago
It's no more crazy than trying to build an OS out of a web browser.
Flowzone 3y ago
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.
- datavirtue 3y ago
  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.

nottheengineer 3y ago

Excel is literally 2D programming. Us mortal developers who can only put lines below one another are incapable of comprehending it, so we only get to ask the wise finance people how their enigma works. 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.

wiz21c 3y ago
> Us mortal developers who can only put lines below one another At least my spaghetti code goes into one direction only...
- eastern 3y ago
  Yes, but I bet your code cannot reveal its own spaghetti:-) 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-relationships-between-formulas-and-cells-a59bef2b-3701-46bf-8ff1-d3518771d507 https://support.microsoft.com/en-au/office/display-the-relat...
  - quickthrower2 3y ago
    Now do that for the VBA!
rzzzt 3y ago
With side-effect free functions: https://youtu.be/0yKf8TrLUOw https://youtu.be/0yKf8TrLUOw
netsharc 3y ago
> But if you want to type them, you have to do that in english and then have excel translate them. 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...

davidmurdoch 3y ago

I know of a restaurant franchisee with 170+ locations that uses a home grown ERP system built in VBA on top of Access by an accountant about 20 years ago. 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!

matwood 3y ago
My first programming job in the 90s while I was still in college was building systems exactly like you describe (and they were as poorly built as you think hah). I worked for a small IT programming/consulting shop. We did small jobs like this in town in addition to installing networks, IVRs, etc... while working on larger software to sell (which is an entirely different/crazy story that involved burning CD demos and using a hand 'stomper' to label and then mail them out).
aj7 3y ago
This post has more information than you realize.
- davidmurdoch 3y ago
  What do you mean? Are you suggesting you can figure out who the company is?
  - mattgreenrocks 3y ago
    I have a guess, but don’t want to offend the big cheese there so I’ll just see how this plays out.
protocolture 3y ago
In high school I got a gig rebuilding a pre 2000 Access DB + VBA front end in office 2003ish due to some kind of incompatibility that was preventing them from upgrading their PC's. 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.

dauertewigkeit 3y ago

My brother is a mechanical engineer and apparently, there's lots of Excel VBA in traditional industries as well.

bmj 3y ago
I can confirm this: I know several mechanical engineers that do mission critical-type systems (think "power plants"), and they routinely use Excel VBA for calculations.
- pillefitz 3y ago
  And it's often the best tool for the job
  - harry8 3y ago
    Other than the known calculation bugs in excel that MS refused to fix for at least a decade and probably still 20 years later. http://www.pages.drexel.edu/~bdm25/gnumeric.pdf http://www.pages.drexel.edu/~bdm25/gnumeric.pdf http://www.phusewiki.org/docs/2009%20PAPERS/SP06.pdf http://www.phusewiki.org/docs/2009%20PAPERS/SP06.pdf http://www.tandfonline.com/doi/abs/10.1198/tas.2011.09076 http://www.tandfonline.com/doi/abs/10.1198/tas.2011.09076
    - pillefitz 3y ago
      Only last link works
    - rvba 3y ago
      It is funny how you talk tjat Excel is bad and the examples just dont work. 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.
      - harry8 3y ago

vb-8448 3y ago

So true!!! :D :D :D If Excel stops working, financial institutions around the world would collapse.

daydreamtrip 3y ago
Python was just added to Excel so there's not much chance of that
- JohnBooty 3y ago
  Really?? Oh, wow: https://techcommunity.microsoft.com/t5/excel-blog/announcing-python-in-excel-combining-the-power-of-python-and-the/ba-p/3893439 https://techcommunity.microsoft.com/t5/excel-blog/announcing... 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.

schmookeeg 3y ago

Healthcare and insurance too! I transform into some glorious magical elf when I volunteer to do the VBA tasks nobody else understands or can lower themselves to do. 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

eli 3y ago
Pretty sure you can access a REST API from VBA without resorting to exec()
- Shorel 3y ago
  It only works with toy examples and then stops working. For reasons unknown, as it should work.
  - pillefitz 3y ago
    Never had issues with the standard VBA HTTP Interfaces
- schmookeeg 3y ago
  We support both mac and pc excel -- the mac VBA is seriously gimped.

aj7 3y ago

Before VBA for Excel, I wrote numerous macros, including ATAN2(x,y) before there was an ATAN2. Afterwards, forget it. Maybe one.

TrackerFF 3y ago

Pre-face: I write a lot of VBA 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.

wavemode 3y ago
The company I worked for in college was in the "VBA for everything" world. It was like the Renaissance when they started experimenting with Power Query for some use cases. (Which, honestly, was still a terrible language. But slightly more maintainable than VBA.)
j45 3y ago
VB is not VBA, VBA was a relatively small subset. 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.
- TrackerFF 3y ago
  VBA works fine if you want to create macros and automate various things for your spreadsheets, but the problem is that the end user want / demand full-blown applications layered over their spreadsheets. The last thing I worked on was a CRUD -interface where the user could read and write data from their spreadsheet, to a azure DB. 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.

underlipton 3y ago

I used it as a coding layman in 15-buck-an-hour "admin" (clerk/secretary) roles to automate some processes that my predecessors had done by hand. Mostly just copying entries from a spreadsheet to company Excel/Powerpoint templates and printing them without killing myself copy/pasting or going into the save/print dialog 50 times. It did its work as something any old schmuck could harness to save themselves from carpal tunnel. 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.

Spinnaker_ 3y ago

And not just old school financial institutions. Jane Street used to run out of an excel sheet. Many hedge funds still do.

harry8 3y ago

10 year old you say.... office 97.

system2 3y ago

I can't imagine Excel without VBA. I tried Google Spreadsheets with js and it sucked big time.

anonzzzies 3y ago

I keep saying this to all the depressed new and old programmers who believe you cannot make money anymore and it’s all over; there are billions of lines of legacy shite running the biggest companies in the world. Not only Cobol and Fortran, but excel/vba, access, fox pro, and, indeed, old php. Companies have to choose, every year, to rewrite or maintain. I do many projects focusing on maintain; example; my last client had a quite to rewrite his systems in some modern crap (next/react) for a little over 8 million usd (that’s the estimate; not a fixed price and of course no guarantees by the consultancy corp quoting it) or, we will keep their ancient php and Java running for 25-50k/mo (few hours work per year to do that with Docker). I have a very low tolerance for rewrites so we go in hard against companies offering them: we make far far more profit than any of them; we run well over 50% with far less risk. Once I discovered containers (chroots in the late 90s and now docker etc), I knew rewriting things is never needed.

TMWNN 3y ago
>I have a very low tolerance for rewrites so we go in hard against companies offering them: we make far far more profit than any of them; we run well over 50% with far less risk. Once I discovered containers (chroots in the late 90s and now docker etc), I knew rewriting things is never needed. 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/ 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.)