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."
JavaScript alone should prove that the "better" language does not win on language design alone (These days it is at least pretty ok)
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#.
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.
I was in the Deskdev team in an investment bank for 4 years, great fun and I learned a lot about Excel.
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.
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.
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...
it really depends on your platform/version. Keep in mind that they try to use chromium in 2020.
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.
.
And how did you design the async parts, given that VBA doesn't have first-class functions? Classes and interfaces? Or nested event loops?
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.
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 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.
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...