6 ms·

Nah... why should it? It works and it does what it needs to do and more if you decide to hook into the Windows API or Mac’s API. Want to parse 500mb structure

by kevas 7y ago

Nah... why should it? It works and it does what it needs to do and more if you decide to hook into the Windows API or Mac’s API. Want to parse 500mb structured XML file? Okay. Takes 3 seconds or so. I had a lot of fun creating a full featured & modern look & feel application using Excel’s VBA runtime as my platform. Sure... I had to create everything from scratch, but learned so much while doing it. Kind of miss it at times since I now work with Java. By the time I moved jobs, the codebase was +30k lines and even built an auto-updater, auto-installer, diagnostic, and AD type of authentication for the app, but most importantly saved tens of thousands of hours by automating reporting, analysis, detect errors, and querying that analysts, accounting, and some BI’s would do as part of their normal work. VBA is great for analysts to work in Excel all day & every day who want to get into programming a little more, but want to have it apply directly to their daily work. Now.... I wish Access does die...

CinchWrench 7y ago
This is so cool. VBA is a gateway drug, for sure. It was my first foray into programming, specifically to solve a problem I knew I could formulate as a shortest path problem. That's led me on quite a journey. The only thing I wish they'd do is update the editor. I like that it feels responsive, I just don't like the lack of line numbers and themes.
- markus_zhang 7y ago
  Try the rubber duck extension: http://rubberduckvba.com/ http://rubberduckvba.com/
- animalnewbie 7y ago
  I actually cut paste to and from gvim
- remarkEon 7y ago
  I had to write VBA one summer for an internship, as I was handed this massive spreadsheet that a small to mid-sized business used to run their entire supply chain operation. It was a complete and total mess, with circular references everywhere and random excel formulas that, as far as I could tell, just made everything slower. They were too small at the time to invest in any kind of off-the-shelf solution (though they don't appear to be anymore). Took me about 2 weeks to figure out what the file did, and another 5 or so to learn enough VBA and leverage it to recreate a much lighter and cleaner version. Basically hung out for the last 3 weeks of the internship. It was cool to use, and after a little bit of a learning curve started making a lot of sense to me. Beyond that though ... I don't see myself ever using it again. Thankful for the opportunity (they took a chance and hired me with zero legitimate experience in that sort of thing), and, yeah, it did make me pick up some other stuff after I was done.
markus_zhang 7y ago
However, it would take a lot of effort to maintain that 30K+ lines of VBA application. But I think that's because the infrastructure at that time was archaic. If it's today you would probably ditch Access for SQL Server and build reports with something more modern.
- Ididntdothis 7y ago
  Maintaining VBA code is probably easier than maintaining C code. You have all the tools to structure things nicely. You even have classes. One problem is that you can’t version control Excel or Access code. I think that’s the biggest weakness.
  - fbender 7y ago
    I found this gem: https://www.xltrail.com/ https://www.xltrail.com/ – I have not used it yet but from the looks it‘s a great solution to our vicious circle of (no) maintenance at work :D
  - lmm 7y ago
    C is a pretty low bar. And yeah, not being able to do version control, diffs, releases, dependency management... it all adds up.
    - kevas 7y ago
      I had version control and everything
      - lmm 7y ago
        What did your build/release pipeline look like? I'm imagining a lot of that would have had to be custom, just because I've never heard of this being done with VBA.
  - pessimizer 7y ago
    I used to use a weird excel extension that I found somewhere that would export all the modules as text and push them to a subversion repo.
yokohummer7 7y ago
But it doesn't have to be VBA. Any language with a decent OLE Automation library would suffice. I've been using Python to automate my Excel files, and am really happy about that. I tried to learn VBA, but could not adapt to its archaic syntax and the clunky VBA editor that comes with Excel.
sebazzz 7y ago
Problem is that VBA in its current shape doesn't work for webbased Excel. Microsoft started with attempting to support Javascript based add-ins [0]. However, they those are of course light years behind in terms of API support. Never even mind that on the desktop Excel the javascript runs in the Internet Explorer engine (of all things, not even the EdgeHTML engine). [0] https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-core-concepts https://docs.microsoft.com/en-us/office/dev/add-ins/excel/ex...
- merb 7y ago
  the last thing of your sentence isn't true anymore: https://docs.microsoft.com/de-de/office/dev/add-ins/concepts/browsers-used-by-office-web-add-ins https://docs.microsoft.com/de-de/office/dev/add-ins/concepts... it really depends on your platform/version. Keep in mind that they try to use chromium in 2020.
- ed_elliott_asc 7y ago
  It isnt the end of the world having different support on different platforms.
- srcmap 7y ago
  In theory, MS can compile the VBA engine to web assembly and run VBA code directly on webpage?
jmnicolas 7y ago
A +30K LOC VBA app to maintain is exactly the punishment I expect to get when I'll eventually end up in Hell ;)
- TeMPOraL 7y ago
  Well, of course. While the Devil promotes web SaaS solutions to entrap mortals in sin, Hell itself runs on Excel and VBA because it works.
  - n0rbwah 7y ago
    And it's a hellish punishment for all the developers who end up in hell and have to maintain it.
clausok 7y ago
Your Excel+vba application’s features remind me of when I joined an investment bank in 2000. I had come from an insurance company where I was considered the Excel\vba wizard, and I was impressed, in the extreme, by my new colleagues’ approach to Excel development. Even during the interview process I had realized that, when it came to vba, I was but a babe in the woods. They had auto-updating code (when you “published” code, all client workbooks downloaded the latest version), code-generated collection classes, interface inheritance, tests, error detection, higher-level functions via Application.Run(), self-contained worksheets with embedded vba code that would operate even if moved to a new workbook. Inevitably, new hires would be unhappy that they did, in fact, have to write vba code all day and would argue for switching to a better language. Our team manager would say, "Vba gives us a superpower no other language does: we can deploy whatever we want, whenever we want, to whomever we want. In any other language, getting 'Hello World' in front of a user is a six month project."
- AdamM12 7y ago
  What would the role names be for positions like this at an IB? Been interested in this type of work.
  - richardcrossley 7y ago
    Something called "Desk Developer", "Deskdev", "Rapid Application Development" or "RAD". I was in the Deskdev team in an investment bank for 4 years, great fun and I learned a lot about Excel.
    - AdamM12 7y ago
      Ok cool. Thank you!
  - clausok 7y ago
    The desk-aligned dev spot is a tough one to land. If you’re not hailing from CalTech, MIT, et al, I suggest the highest-probability strategy is an indirect one: get a client-facing job at hedge fund administrator, get great at Excel+vba; over-deliver for the fund’s CFO and/or traders in all their post-trade questions (that’s why Excel+vba expertise is important: it’s easy to deploy to them as just an Excel file); get recruited. The right spot at a fund administrator is much less competitive, and you get an opportunity to impress the same people you would if you were hired directly onto a desk. Excel+vba is an odd beast because, nowadays, it's rarely seen as a differentiating skill -- it's not taken seriously -- and yet on almost any trading desk there is an infinite series of todo's where Excel+vba is just the right tool for the job.
dgudkov 7y ago
I know many non-technical people love VBA because it empowers them to do cool things, especially in Excel. However, there is a no smaller number of people who view VBA as a necessary evil, because they are forced to do programming and they are not programmers. It's also well-known that VBA macros written by non-professional programmers tend to become a cumbersome, unreadable, unmaintainable mess. My favorite case is when VBA is used to insert complex multi-line formulas in a spreadsheet, run calculations, then do it again with another set of formulas. I believe, VBA will remain as a general purpose language for long time, but it will be partially replaced by tools that are purpose-built for tasks such as data preparation. We've seen it with EasyMorph (https://easymorph.com https://easymorph.com), a visual data preparation tool we've created exactly for heavy Excel users without a technical background. It works very well for them and we get a lot of praise for it. If this approach works for data preparation, probably it will work for other types of automation too.
ourlordcaffeine 7y ago
The company I work for just moved all automated bookkeeping and analysis into R scripts (running automatically), completley ditching office and Microsoft.
raxxorrax 7y ago
We still have Access/Filemaker in use. Just as a frontend, but still. There could be something better, but often there just isn't. Some turned their Access apps up to eleven, with maps integration for navigation, address completion, automatic correspondance functions, automatic dashboarding for different departments... some are even more advanced than the latest stock CRM/ERP solutions from known developers. There certainly would be a better solutions, but reimplementing some of those apps would take months or even years of development time. Completely crazy.

by kevas 7y ago

CinchWrench 7y ago

This is so cool. VBA is a gateway drug, for sure. It was my first foray into programming, specifically to solve a problem I knew I could formulate as a shortest path problem. That's led me on quite a journey. The only thing I wish they'd do is update the editor. I like that it feels responsive, I just don't like the lack of line numbers and themes.

markus_zhang 7y ago
Try the rubber duck extension: http://rubberduckvba.com/ http://rubberduckvba.com/
animalnewbie 7y ago
I actually cut paste to and from gvim
remarkEon 7y ago
I had to write VBA one summer for an internship, as I was handed this massive spreadsheet that a small to mid-sized business used to run their entire supply chain operation. It was a complete and total mess, with circular references everywhere and random excel formulas that, as far as I could tell, just made everything slower. They were too small at the time to invest in any kind of off-the-shelf solution (though they don't appear to be anymore). Took me about 2 weeks to figure out what the file did, and another 5 or so to learn enough VBA and leverage it to recreate a much lighter and cleaner version. Basically hung out for the last 3 weeks of the internship. It was cool to use, and after a little bit of a learning curve started making a lot of sense to me. Beyond that though ... I don't see myself ever using it again. Thankful for the opportunity (they took a chance and hired me with zero legitimate experience in that sort of thing), and, yeah, it did make me pick up some other stuff after I was done.

markus_zhang 7y ago

However, it would take a lot of effort to maintain that 30K+ lines of VBA application. But I think that's because the infrastructure at that time was archaic. If it's today you would probably ditch Access for SQL Server and build reports with something more modern.

Ididntdothis 7y ago
Maintaining VBA code is probably easier than maintaining C code. You have all the tools to structure things nicely. You even have classes. One problem is that you can’t version control Excel or Access code. I think that’s the biggest weakness.
- fbender 7y ago
  I found this gem: https://www.xltrail.com/ https://www.xltrail.com/ – I have not used it yet but from the looks it‘s a great solution to our vicious circle of (no) maintenance at work :D
- lmm 7y ago
  C is a pretty low bar. And yeah, not being able to do version control, diffs, releases, dependency management... it all adds up.
  - kevas 7y ago
    I had version control and everything
    - lmm 7y ago
      What did your build/release pipeline look like? I'm imagining a lot of that would have had to be custom, just because I've never heard of this being done with VBA.
- pessimizer 7y ago
  I used to use a weird excel extension that I found somewhere that would export all the modules as text and push them to a subversion repo.

yokohummer7 7y ago

But it doesn't have to be VBA. Any language with a decent OLE Automation library would suffice. I've been using Python to automate my Excel files, and am really happy about that. I tried to learn VBA, but could not adapt to its archaic syntax and the clunky VBA editor that comes with Excel.

sebazzz 7y ago

Problem is that VBA in its current shape doesn't work for webbased Excel. Microsoft started with attempting to support Javascript based add-ins [0]. However, they those are of course light years behind in terms of API support. Never even mind that on the desktop Excel the javascript runs in the Internet Explorer engine (of all things, not even the EdgeHTML engine). [0] https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-core-concepts https://docs.microsoft.com/en-us/office/dev/add-ins/excel/ex...

merb 7y ago
the last thing of your sentence isn't true anymore: https://docs.microsoft.com/de-de/office/dev/add-ins/concepts/browsers-used-by-office-web-add-ins https://docs.microsoft.com/de-de/office/dev/add-ins/concepts... it really depends on your platform/version. Keep in mind that they try to use chromium in 2020.
ed_elliott_asc 7y ago
It isnt the end of the world having different support on different platforms.
srcmap 7y ago
In theory, MS can compile the VBA engine to web assembly and run VBA code directly on webpage?

jmnicolas 7y ago

A +30K LOC VBA app to maintain is exactly the punishment I expect to get when I'll eventually end up in Hell ;)

TeMPOraL 7y ago
Well, of course. While the Devil promotes web SaaS solutions to entrap mortals in sin, Hell itself runs on Excel and VBA because it works.
- n0rbwah 7y ago
  And it's a hellish punishment for all the developers who end up in hell and have to maintain it.

clausok 7y ago

Your Excel+vba application’s features remind me of when I joined an investment bank in 2000. I had come from an insurance company where I was considered the Excel\vba wizard, and I was impressed, in the extreme, by my new colleagues’ approach to Excel development. Even during the interview process I had realized that, when it came to vba, I was but a babe in the woods. They had auto-updating code (when you “published” code, all client workbooks downloaded the latest version), code-generated collection classes, interface inheritance, tests, error detection, higher-level functions via Application.Run(), self-contained worksheets with embedded vba code that would operate even if moved to a new workbook. Inevitably, new hires would be unhappy that they did, in fact, have to write vba code all day and would argue for switching to a better language. Our team manager would say, "Vba gives us a superpower no other language does: we can deploy whatever we want, whenever we want, to whomever we want. In any other language, getting 'Hello World' in front of a user is a six month project."

AdamM12 7y ago
What would the role names be for positions like this at an IB? Been interested in this type of work.
- richardcrossley 7y ago
  Something called "Desk Developer", "Deskdev", "Rapid Application Development" or "RAD". I was in the Deskdev team in an investment bank for 4 years, great fun and I learned a lot about Excel.
  - AdamM12 7y ago
    Ok cool. Thank you!
- clausok 7y ago
  The desk-aligned dev spot is a tough one to land. If you’re not hailing from CalTech, MIT, et al, I suggest the highest-probability strategy is an indirect one: get a client-facing job at hedge fund administrator, get great at Excel+vba; over-deliver for the fund’s CFO and/or traders in all their post-trade questions (that’s why Excel+vba expertise is important: it’s easy to deploy to them as just an Excel file); get recruited. The right spot at a fund administrator is much less competitive, and you get an opportunity to impress the same people you would if you were hired directly onto a desk. Excel+vba is an odd beast because, nowadays, it's rarely seen as a differentiating skill -- it's not taken seriously -- and yet on almost any trading desk there is an infinite series of todo's where Excel+vba is just the right tool for the job.

dgudkov 7y ago

I know many non-technical people love VBA because it empowers them to do cool things, especially in Excel. However, there is a no smaller number of people who view VBA as a necessary evil, because they are forced to do programming and they are not programmers. It's also well-known that VBA macros written by non-professional programmers tend to become a cumbersome, unreadable, unmaintainable mess. My favorite case is when VBA is used to insert complex multi-line formulas in a spreadsheet, run calculations, then do it again with another set of formulas. I believe, VBA will remain as a general purpose language for long time, but it will be partially replaced by tools that are purpose-built for tasks such as data preparation. We've seen it with EasyMorph (https://easymorph.com https://easymorph.com), a visual data preparation tool we've created exactly for heavy Excel users without a technical background. It works very well for them and we get a lot of praise for it. If this approach works for data preparation, probably it will work for other types of automation too.

ourlordcaffeine 7y ago

The company I work for just moved all automated bookkeeping and analysis into R scripts (running automatically), completley ditching office and Microsoft.

raxxorrax 7y ago

We still have Access/Filemaker in use. Just as a frontend, but still. There could be something better, but often there just isn't. Some turned their Access apps up to eleven, with maps integration for navigation, address completion, automatic correspondance functions, automatic dashboarding for different departments... some are even more advanced than the latest stock CRM/ERP solutions from known developers. There certainly would be a better solutions, but reimplementing some of those apps would take months or even years of development time. Completely crazy.