Hacker News Reader

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."

What would the role names be for positions like this at an IB? Been interested in this type of work.

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.

Ok cool. Thank you!

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.

>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

JavaScript alone should prove that the "better" language does not win on language design alone (These days it is at least pretty ok)

Back then there were a few book that talked about the "Internals" of VBA and taught a lot of tricks like manipulating the pointer, etc.

But I do believe that people should turn to better tools. I think at least they could use VB, which is a proper language and has support for version control and other stuffs. It's also very easy to use VB to manipulate Excel, much easier than C#.

Not to mention circumventing the usual corporate app deployment BS...

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.

Try the rubber duck extension:

http://rubberduckvba.com/

I actually cut paste to and from gvim

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.

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/ex...

the last thing of your sentence isn't true anymore: 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.

It isnt the end of the world having different support on different platforms.

In theory, MS can compile the VBA engine to web assembly and run VBA code directly on webpage?

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.

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.

I found this gem: 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

C is a pretty low bar. And yeah, not being able to do version control, diffs, releases, dependency management... it all adds up.

I had version control and everything

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.

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.

I wrote one for Access a long time ago. Worked surprisingly well but it didn’t export queries and table definitions. Should have done that too.

It was extremely easy. For awhile, I used naming conventions to keep everything organized, but then found RubberDuckVBA and there... I found enlightenment.

The codebase was clean and extensible. I built out a standard lib (I/O sync/async, networking, array methods, dictionary class, XML utilities (DOM/SAX). Then built out database functionality that included auto exporting to various formats, many tabs and with optional conditional formatting. Built the UI from the ground up to be async by building all UI components to be ‘reactive’ and have standardized interfaces. Started with just the frame & label and made my version of the modern web design in VBA’s user form. All components were reusable—from buttons, checkboxes, custom filterable drop-downs, tables, and the ability to display a subset of markdown (which allowed end users write their own documentation for the workflows they’d end up owning)

It had standardized parser interface that allowed for quick buildout of a new parser class; same for full pages. The navigation was completely dynamic and handled by the routing/navigation module. The permissions for all users were in a hosted database and the ui would only build out what the user had permission for and nothing more. SQLServer would handle user authentication as it would get the users name from the connection metadata.

I handed over the project a few months back to a former colleague, one who has only been programming for about a year and one who only knew how to do macro recordings. Since leaving, the application has thrived and is used all across the country, supporting hundreds...

Just before I moved on, I started working on a VBA native toy browser that supported basic HTML, but ended up leaving and not finishing it.

One pain in the ass was custom :hover functionality over ui components. Tried a lot... even tried hooking into window messages, but that messed up some async events...

I think I went off topic, but it wasn’t really a problem.

.

Did you build some kind of component tree diffing for the reactive UI, like React? Or data binding?

And how did you design the async parts, given that VBA doesn't have first-class functions? Classes and interfaces? Or nested event loops?

Vba has classes, enumerated, interfaces, events, collections, stacks, queues, array lists, dictionary, and more.

I didn't mean to imply that it doesn't, and I know that it does - but I was just curious about how you built the reactive UI and the async features.

I did some async stuff in VBA recently (with events) and it was a bit of a mess, which I'd like to clean up if possible. VBA has classes but not anonymous classes, and not anonymous functions either, so you can't easily do callbacks. And the error handling mechanism is not great.

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

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.

And it's a hellish punishment for all the developers who end up in hell and have to maintain it.

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.

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.

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), 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.

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