Will VBA Die? (2019) | Hacker News Reader

Will VBA Die? (2019)

thespreadsheetguru.com

146 pointsUkiahSmith162 comments

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

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.

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.

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.

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?

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.

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

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.

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.

It'd honestly be a shame if it did (but I understand the stresses that might cause it to happen).

Flash back to ~1986, and Bill Gates wrote an article for a Byte magazine special edition in which he described a unified version of BASIC implemented across a suite of GUI productivity applications.

Keep in mind, this is before Windows 3.0 and the Microsoft hegomony, before Word for Windows, (and Access, Project, Visio, etc.), before OLE/COM/ActiveX/IDispatch, and all of which are arguably necessary to complete the vision he outlined in the article. Ten years later, the vision of the article was realized, and thirty-five years later, it not only still exists, it's still useful across a huge cross section of computer users. (Despite the radical changes in the industry over that time.)

Microsoft has gotten a lot of flack over the years, and a lot of it has been earned, but the ability to identity a useful target state ten years in the future and rally an organization to achieve that goal is an amazing accomplishment.

I'm working on an excell addin right now. It doesn't use VBA (it uses microsoft interop libs for .NET) And I have zero interest in using VBA, but I will admit, it would have been much easier to do this project in VBA instead of C#. Debugging would have been easier. I could debug inside the VBA code-behind instead of Attaching to the excel process in Visual Studio. Deployment would have been easier. I could make an .xlam file and just give it to the users instead of A separate visual studio project that creates an installer on each build. Excel interop would have been easier too(probably, I'm not 100% sure.) But using the Microsoft.Office.Interop.Excel libs are kinda hard. I don't have any good reference material for this lib, but for VBA, there are tutorials all over the internet for doing common things. I mostly avoided VBA because I prefer the syntax of C#. I would imagine most people that only know VBA would have no interest in using C# to write office interop code, and I don't blame them. As for javascript, I doubt anyone that knows VBA well would have a hard time learning javascript, and if the codebehind for excel was slowly migrated to that, most devs would welcome the change.

There is so much of this I can relate to, I have worked with legacy systems too and have been fortunate in a sense to have to drill into someone else's VBA and thankfully there were people there to run me through those strangeness of it.

While I don't think it needs to continue, I believe it's been replaced in all aspects to date.. I do think that there is a requirement for a knowledgeable person to at the very least lead the re-write.

> As for javascript, I doubt anyone that knows VBA well would have a hard time learning javascript, and if the codebehind for excel was slowly migrated to that, most devs would welcome the change.

One of the main points of contact for my VBA adventure picked up vanilla javascript super fast, and within a few weeks he was already explaining how it could be made semi strongly typed, he introduced us then to a newly formed TypeScript subset and we never looked back.

It's not a language issue, its an understanding issue, give a good engineer/developer/hacker any language and they will figure it out and know how to make it work.

That said, if I ever see VB again, I will still shed some salty tears.

> I would imagine most people that only know VBA would have no interest in using C# to write office interop code, and I don't blame them.

I'd be happy to use C# but it's so god damn hard to learn those libraries.

The biggest disadvantage in using VBA is speed – not to mention the idiosyncrasies of the language itself, which can make complex code rather painful.

Writing a .xlam addin is easy, except there's no tooling for creating "builds".

On a separate but related note, since writing XML for the ribbon by hand is so annoying (and you also have to write callback wrappers for each Sub you want to expose) I have actually written a python tool that parses annotations from comments in .bas files in a given folder and spits out a .xlam file. Haven't gotten around to publishing it but if folks are interested I can give it a little oomph and finish in the next few weeks, so lmk

I’m the other way. Started a large project in VBA once, jumped over to C# interop and never looked back. I found the libraries to map fairly well to the same named functions in VBA when it came to worksheet manipulation. My biggest issue was that the interop has quite a bit of latency. You really want to accomplish as much in one call as you can (i.e. read whole ranges into an array instead of looping through cell by cell).

Edit: If this is internal, you can also set up one click deployment to some folder on the network that will auto update the add on for end users.

The latency might be because the C# is running out-of-process and has to serialize all the data and make calls by (internally) sending window messages. If you can create the COM component in-process (in the Excel process) it should be a lot faster.

I was working on a small program that originally used the C# interop and found the latency pretty bad as well. For a small document, it was taking like 4 or 5 seconds to do what we wanted (dont remember exactly what that was atm). Eventually, I used some OpenXML library Microsft offered that was basically LINQ to XML with some types / extensions and found something interesting:

On smaller documents, the OpenXML manipulation blew the COM interop out of the water in terms of speed, but with larger documents (hundreds of thousands of rows), I think the COM interop was either faster or at least just as fast.

The key to using C# for manipulating Excel for me was ExcelDNA. The development workflow and binary packing was pretty good, I thought.

Before ending up on ExcelDNA, I had started with a VSTO template from Visual Studio, and the development workflow was hell because of how buggy the interactions with Excel were. And I thought the ClickOnce deployment was annoying, if you can instead merely copy an excel file as well as its add-in file(s).

You don't really need to sign your add-in, though I guess that's not good.

I'm curious, what's the upside of not using VBA? Aside from being cool, obviously :)

VBA is very annoying, and I wouldn't love it if I hadn't been forced to spend so much time in it creating custom functions before attempting larger scale applications.

Once you get used to classes/interfaces, however, the feeling is comparable to realizing the guitar you learned how to play on was terrible and had absurdly high action, and now that you're holding a mediocre to moderately good guitar, you can play a lot better than you thought.

I also think you have to accumulate a personal library of utility functions to make it tolerable (or sometimes even fun.) I've never noticed a real community with standards and norms around VBA, so you're on your own a lot.

One thing to consider, which I've found powerful, is a hybrid approach where you use vba for what it's best at -- interacting with the Excel object model -- and have the vba load and call a C# xll that leverages the .Net libraries and does all the work that would be tedious to do in vba. For your users, they would simply open a "host" .xlam file, which is the only one they would be aware of, and this file would load additional .xlam or xll files and typically also check for updates to these files. No installer necessary.

For example, your vba code would pick up a range of index (or ETF) tickers from a sheet, use Application.Run("lib.constituents") to call a C# function that pulls the stock tickers and weights that represent each indices' constituents, your vba code would receive back an array of arrays and use Excel's row grouping feature to group the members & weights under the parent index and perhaps add some formatting and\or formulas.

The advantage of this division of labor is that you get faster dev & debug feedback loops within your Excel-centric code, while minimizing the amount of time you spend working in a dev environment that has stood still for 20+ years as others have raced far past.

Microsoft should rather provide a better path toward office automation rather than just frustrate users. As I have seen it, javascript isn’t even remotely close to the sort of integration that made the success of VBA. Like how can I save a javascript macro as a user? Javascript user defined function?

VSTA was a good attempt in its time, a mini visual studio integrated in office with VB.net and C# instead of VB6. That would have been cool.

Agreed. JavaScript has no real upside here besides being fashionable. whereas built-in C# with access to the .Net framework would be really powerful.

JavaScript has a huge upside because a lot of people already know JavaScript. The more tools and platforms switch to JavaScript the more of an office superhero users are when they know something about the language. The productivity of employees will jump off the charts. When you send someone to train for one thing that utilizes JavaScript you get the benefit that those skills may also happen to work with something else as well.

At some point, I hope we can get past this argument. Learning a new language for rudimentary tasks is not particularly hard. I'm not sure why people decided that JavaScript of all things was what we had to all latch onto.

You're right, we should automate everything with Rust \s

People both over and underestimate the ability of users to learn a language for quick scripting/automation. Languages like Bash, Python, JS, VBA... they're extremely accessible for quick and dirty work, which is why people latch on to them compared to others.

> You're right, we should automate everything with Rust \s

I did chuckle a bit.

Accessibility is key, so is documentation, so is flexibility, but again I re-itterate, the right tool for the job matters most.

Because it’s the new Franca lingua of programming. Everyone knows it whether they like it or not. There’s value in not have to switch context while programming. Also there’s typescript which makes JavaScript usable. I don’t like JavaScript either but typescript isn’t terrible and the ecosystem is much bigger than any Microsoft programming ecosystem which results in less reinventions of the wheel. MS has also been treating JavaScript as a 1st class citizen on Windows

I suspect that we are reaching peak javascript, that wasm will open the browser to all languages and that javascript will compete based on its own merits rather than its historical monopoly.

> MS has also been treating JavaScript as a 1st class citizen on Windows

Eh what now?

The ActiveSctipt host for JScript (for shell and Classic ASP scripting) hasn’t been updated since Windows 2000, and for shell scripting in-general Microsoft has been (“was” at this point?) pushing PowerShell since its demo at PDC 2003.

WinJS for Windows 8 was a huge waste of money for the company that people always seem to forget.

And their Chakra JS engine is effectively now retired given Chromium+V8 replaced EdgeHTML earlier this year.

The only places Microsoft has been using JS are for their current set of Electron-based applications (VS Code, Skype, MS Teams, Azure Storage Explorer, etc). TypeScript is nice and all (and I really love it and I hope it brings algebraic typing and structural typing to more languages) but it’s only popular because Google, of all people, adopted it for Angular.

It kind of won in a lot of ways... biggest package ecosystem. The most developers using it. Highly optimized runtime. Supports the use of OO, Functional and Procedural paradigms. Able to run in the browser for online versions. Cross platform and nearly ubiquitous.

>biggest package ecosystem.

I don't understand how you can be proud of having the most unusable package ecosystem. Sure the numbers are large but can you actually safely use those packages? No, you can't. Just add a single library and you will include a huge amount of transitive dependencies from random package maintainers over which you have no control.

Other languages like Java or Rust have the same problem but this is a problem with exponential impact and NPM is the leader in tree depth. Having a 10 layer deep dependency tree is far worse than a 7 layer deep dependency tree. When I look at the dependency tree of my own projects more than 50% of the libraries are first party and from a vendor with a good reputation. (spring, apache commons, tomcat). The rest are less trust worthy but each project has a small opensource community that consists of more than just a random guy that may randomly throw emotional fits like in the leftpad scenario.

That comes down to due diligence... I look at my bundle outputs and overall package size... I also review the packages I use, and tend to avoid anything that isn't open source. I've also forked packages that have issues I need resolved.

You have all the control in the world, and just because someone is lazy doesn't mean it doesn't work. Beyond this, you probably don't review every single line of code that goes into your applications dependencies regardless of language. It's about impedance vs productivity for the most part.

I tend to, at least with front end projects focus on koa for the server tethered to the UI, React and material-ui ... nearly everything else is one-off building from there. Unless you think the likes Facebook and Google are just one random guy.

I have a friend that wanted to learn programming. I suggested Python, but they wanted to go directly into making web apps.

I don't think they finished the class. Javascript just has too many weird concepts (what's the difference between null and undefined and false and 0 and "") that distract from the mechanics of learning what a computer program is, and I also think that the desire to make something others can use too early on leads to going too quickly through the basics ("yeah yeah, add numbers, who needs to do that") and are then overwhelmed when things like objects and promises show up. You have to crawl before you walk.

I think if I were going to introduce someone to programming today, I'd probably pick CircuitPython. Python is simple and makes sense almost immediately. Doing it on a microcontroller lets you make something "neat" almost immediately, and you're spared a lot of the complicated externalities of the real world. (HTML, CSS, dev servers, npm modules, etc.) Some day you can learn all that stuff, but you need to know about if statements and loops and variables first.

I also miss the days of BASIC and Logo. I think those would be fun for the younger generation... but now everyone just wants to make a mobile app and those don't get you there. Ahh, for simpler times.

Since we are in a VBA thread, I'd argue that VBA is a great introduction itself into programming; especially with its macro recorder.

Progamming by clicking (in the spreadsheets) and seeing how the code changes instantly is arguably better for beginners than what normal programming languages usually offer.

Most potential users of VBA don’t know JavaScript or any other language. And VB is way more accessible than JavaScript.

My guess is that they will retire from having to program one day.

On that I agree... Python would probably be a better option if there weren't a need for cross platform support including in-browser. Also, I'm not sure if there's a good path from Python to JS or Wasm for browser usage.

I love JS, but it's not that accessible and a poor first language for people. I just enjoy the shear flexibility of it.

Sure, but it's not as big as an ecosystem. Knowing JavaScript at this point is way more valuable than learning VBA. I'm not saying VBA is bad, I think visual basic is great, I have fond memories of it, but reality is JavaScript is going to be what companies are going to want their employees to know.

Can JavaScript use COM libraries and windows APIs? The JavaScript ecosystem is big but I doubt it covers much of what a typical VBA application needs. I think C# or another .Net language would be more suitable.

If there isn't now, I imagine it will come. There's:

https://docs.microsoft.com/en-us/microsoft-edge/windows-runt...

I'm sure Microsoft is probably at least considering making JavaScript work well with existing Windows APIs.

I think that's deprecated. They dropped support for UWP apps with JavaScript in Visual Studio 2019:

https://social.msdn.microsoft.com/Forums/en-US/ebb5e04b-d00a...

Comparing javascript to C# is really not appropriate here, for so many reasons. I do think you know the main differences between interperated, transpiled and compiled for starters, but the "right tool for the job" is far more important.

Being fashionable is also not exactly fair, it was very flakey before jQuery created some entry level standard, because of its popularity it grew to what it is today, if thats 'fashionable' then I support it.

I am old. I have no idea what interperated is :)

:) Not sure if you're joking or not, so I'll be more rude and ask if you've ever written a compiler for the fun of it.. That will gauge your real age ;)

I'm not sure, but that person may have been attempting to make a joke about your misspelling of "interpreted".

ah.. Well colour me silly! ;) Well played!

On a more serious note: an excel macro language like VBA pretty much needs to be interperated or the compiler needs to be hidden away like VBA does. As user you shouldn’t have to deal with a compiler step. You can do this with C# pretty well as you can with other languages.

Personally I think it would be really cool if they used TypeScript instead of JavaScript.

> Comparing [JavaScript] to C# is really not appropriate here, for so many reasons. I do think you know the main differences between [interpreted], transpiled and compiled for starters...

Are you saying there is a fundamental difference between JavaScript and C# in this regard? I don't think there is.

Each language has a compiler that compiles source code to bytecode. Each has an interpreter that can directly execute that bytecode, and a Just In Time compiler that can compile the bytecode into native machine code.

These languages, along with Java, are pretty much identical with regard to these distinctions.

Transpiled and transpilation are ugly and unnecessary words. They are just a fancy way of saying compiled and compilation.

Some make a distinction between a transpiler and a compiler; however this is a distinction without a difference. A compiler transforms source code into object code. The source code or the object code may be a "low level" language like bytecode or machine code. Or either may be a "high level" language.

For example, TypeScript has a compiler that compiles TypeScript code into JavaScript. [1]

The original implementation of C++ was Cfront, a compiler that compiled C++ code into C code. [2]

For that matter, "machine code" on modern CPUs is a high level language of its own, which bears little resemblance to the low level operations that happen within the CPU.

[1] "The command-line TypeScript compiler can be installed as a Node.js package." https://www.typescriptlang.org/

[2] Personal discussion with Bjarne Stroustrup on BIX around 1985 when I called Cfront a "preprocessor" and he chewed me out and told me it was a compiler just like any other compiler. Also: "Cfront was the original compiler for C++... which converted C++ to C" https://en.wikipedia.org/wiki/Cfront

Blockpad (http://www.blockpad.net), while not directly exposing C# is written in it so there is certainly potential in the future for such interop.

Especially with Microsoft's creation of Blazor (leveraging WASM) and the .NET Jupyter notebooks, it seems like they'd be positioned to make .NET/C# accessible in the mobile and web platforms too.

VSTO to the best of my knowledge is still a thing. And you could indeed do some pretty cool stuff with it!

But it's not as easy to use as VBA, nor was it incredibly easy to work with from a development standpoint when I last tried to use it (Maybe I was doing it wrong, but I had to do a full VS install with the add-in module.)

VSTA, not VSTO. It was a .net IDE embedded in an application the same way VBA works.

Ahh. I always assmued that VSTA worked the same way as VSTO...

That makes me ask why VSTO -didn't- work more like VSTA, then. It may have improved adoption substantially...

I think there's a different focus with JS vs VBA or C#. JS is the future because of Office365 ie. Cloud/online apps are the future.

I don't think C# integrates with the o365 codebase, but I'd consider both JS and C# as attempt to move people to good dev practices, C# with IDE/versioning/debug etc and JS with the approach of funneling data in and out of excel eg. Cloud compute, and then using excel for basic functions that don't involve using macros etc.

As long as Excel is used by businesses, VBA will be relevant. Excel will probably continue to exist - and be supported and updated to ever newer versions - forever, so it stands to reason that VBA will probably also exist forever. Unless Microsoft goes bankrupt and Excel isn't picked up by another company. Which will also probably never happen.

Microsoft is slowly transitioning Excel macros to Javascript / Typescript. It will happen eventually.

The Excel object model is really a COM/IDispatch object model... it's been accessible from non-VBA applications essentially since the beginning.

Not Excel, but one of my first consulting projects was at a company that developed some custom libraries in Java and for a company that used ASP for the front end. The architecture we used ran the Java code in the Microsoft JVM, which then made Java accessible via IDispatch (and therefore by ASP's scripting engine).

IIRC, we were doing stuff similar to this:

    let javaObject = CreateObject("java:com.company.library.ApiClass")
    
    print javaObject.version

This is essentially a part of the 'embrace and extend' functionality that MS added to Java and got sued by Sun over. (There were also mechanisms for relatively easily calling into Windows from the MSJVM too... it felt an awful lot like the Proto-CLR that it was.)

At the moment the implementation is to basically inject code, but I imagine eventually Microsoft would like to move away from that.

Issues like DCOM and legacy systems would need some interface from Microsoft of course to handle IDispatch, GC etc, but thats what Microsoft do.. And to behonest, as much as I like to rip on them, they do it well.

Edit: I think the Java example is a little bit different, but i don't know anything about Proto-CLR!

Have a read about Ext-VOS here.

https://blogs.msdn.microsoft.com/dsyme/2012/07/05/more-c-net...

"Project 7 and .NET Generics"

https://fsharp.org/history/hopl-draft-1.pdf

WinRT, as it was originally designed, traces its ideas back to that Ext-VOS project, but going back to COM as they were thinking about it back then.

https://docs.microsoft.com/en-us/uwp/winrt-cref/winrt-type-s...

Quote: "What Will Replace VBA? Short answer: JavaScript. "; and author follows with some logic about JS vs VBA using cross-platform.

Except this will never happen:

1 - M$ loves backward compatibility. It's what keeps their software being sold all these times. Worse case JS will have bigger user share and that's it, but M$ will never cut VBA out.

2 - Also majority of business is done on Windows. Cross-platform means absolutely nothing to corporations. The day that Windows dies that's the day VBA will die as well.

3 - JS as golden boy vs VBA? pleease. Best case scenario you're switching from one ugly boy to another ugly boy. Both VBA and JS are horrors. Don't believe me? Go read'em horror stories about JS cross browsers implementations. JS solved the problem of cross-platform only to open the problem of cross browsers. Good luck having Apple implement the same JS in Safari as their mortal enemy from Google in Chrome.

1) They are paid to support their own tech, when they drop one (Silverlight for example) it can be a serious hit for a buisiness.

2) Yes, but it is changing thanks in part to dotNet core understanding this too. So we should see some benefits across the board for everyone.

3) Well, like PHP back when it was the golden boy against Perl, it's up to the developers to decide the direction of the language itself. Browsers and Node are big players to influence that too, but M$'s interest bringing TypeScript for example is evidence they might believe that.. Also as for standards, I might be wrong but I think they now decide their own standards, with all the browsers as members ?

> They are paid to support their own tech, when they drop one (Silverlight for example) it can be a serious hit for a buisiness.

...but you can still download and run Silverlight.

Fun fact: if you load up Netflix in a browser that's too old to support the html5 player, they'll prompt you to install the Silverlight plugin. https://help.netflix.com/en/node/23742

There's really two camps of VBA, the "application builder" and the "spreadsheet functionality" camps.

Application builders are trying to build interfaces for things that will run in a non-spreadsheet (i.e. non-reactive, not always-recalculated-to-be-consistent) mode. That's bound to be brittle, because that's not what Excel is for.

Spreadsheet functionality extending people OTOH write almost 100% what in ordinary programming is known as "pure functional style". Too often to write complex formulas in Excel (even something as simple as the Black-Scholes equation) people have to use multiple cells to keep things tidy and debuggable. You can use VBA functions for that. You can also write short loops to "solve for zero" with the bisection or Newton method etc. as long as they don't run for long. None of that interferes with spreadsheet semantics.

My first apps ever were extremely customized VBA Access databases. I already knew QBasic so doing VBA was easy. It affords a ton of power. I was able to create an entire interface and workflow easily and have everything contained within one Access file. The only downside was when multiple users wanted to use it (I think one had to save/close to let another in). For small things (a workflow tracking a university's scheduling changes) it can be great. Filemaker is a similar tool and both tools allow rapid development and prototyping. It might not be that useful to seasoned programmers, but to people who just want to get something done quickly, and especially for people who don't program much, it's an amazing tool.

Some points:

* I guess with Office365, JavaScript makes some sense, but why not just go VBA to WebAssembly?

* What about the Python rumors a couple years ago?

* I should probably turn my NecroVisualBasiCon library into A book. If you use the Access.Application library, right-click and toggle the hidden members, that COM object has a few extras like loading/saving objects from the VBProject that make e.g. git integration feasible.

* VBA does an outstanding job of providing 80% of what you want and no more.

Excel is one of the reasons I want to leave a career of Business Analyst behind.

Regardless of the process, the last step is always to dump the data into Excel and spend tons of time to create good-looking charts and tables. It usually takes me a full day to do that plus write wiki pages in Confluence (another pain point).

I just want to stay away from spreadsheet -> which means I need to get further from business and get a more technical position.

I think you may find R an excellent tool/ecosystem for that kind of work.

I'm actually trying to break into BI or DE, so Python is my first choice. I also have some experience with C++ but it is not that useful for corporate development nowadays.

I don’t think it will. Microsoft might add another macro language but everyone is obsessed with backwards compatibility at MS and I highly doubt they would remove something as widely used as this. I used to be really annoyed by this as an engineer, but in contrast to Google’s culture where products are shutdown all the time I’ve started to come around that users should come first to avoid getting this reputation, even if it does become a combinatorial hell of different versions. I work at MS so I’m biased probably

Ha - good timing! I've been using VBA today and yesterday actually!

Often only a few times a year I use it now but when I do it I feel productive.

In case your wondering I has a previous Excel VBA Macro that I wrote for updating a SQL Server Database based on data in Excel. The macro first has to run checks against data in an IBM AS/400 Database.

I ended up having to pull out the macro to do a bulk update in an ERP. It was faster to do this then re-write in an modern language (especially since I work with the data in Excel first).

As far as I'm concerned, JavaScript (or at least JScript, MicroSoft's proprietary reimagining) replaced VBA 13 years ago. I haven't had to do anything programming with Excel for rather a long while, but way back in 2007 I was already writing JScript programs to automate spreadsheet tasks.

I hate VBA with a passion, but on the other hand it saves me many many hours every month through my ability to automate tasks. I would love for almost any language to replace it, but there has to be some kind of replacement if it goes away.

Between the spread of excel across businesses and MS focus on compatibility it seems rather unlikely. It is kinda ridiculous how much depends on VBA macros.

Weirder things have happened, but whatever would replace it, would create an industry overnight.

I don't own a Windows computer, but I've seen some incredibly interesting stuff come out of the VBA for Excel, which I sadly never have had a chance to learn.

Does anyone here have a side-by-side comparison of the LibreOffice BASIC vs the VBA?

The main difference will be the object models for the various applications. LibreOffice's object model was designed around the expected scripting/automation language - Java. That means that the object model, especially the properties and methods of those objects, is written in a very Java-accented way that would normally feel like foreign vocabulary mixed into a Visual Basic-based dialect. If you're used to that, then the way objects work in VBA (or the very similar Lotusscript) might feel a little bit weird at first, then make more sense than the LibreOffice objects/properties/methods. (Similarly, writing Java against a COM- or OLE-oriented API object model feels awkward, even if you can do all of the same things that you could do with VBA.)

> one of the easiest coding languages to learn if you don’t have a computer science background

Really? A language that has values passed by value, values passed by reference, references passed by value and references passed by reference? (And both late and early binding, and auto-boxing with all kinds of mysterious type-conversions, etc. and so on. And default properties.)

What makes it easy for beginners is the integrated environment, which is also what makes it difficult for more experienced programmers as it doesn't integrate with their version control system and other common tools.

If traders/quants and financial analysts in banks say that they don't need VBA anymore, sure it will die. Otherwise, not a chance. Trust me.

Not sure why they wouldn't go something C#/.NET based. If you're going to go the JavaScript route, at least pick TypeScript.

Whatever your position on the static typing debate surely one of the sweet spots for dynamic languages is casual use by non-professionals?

C# has a dynamic runtime. In that sense it's very similar to VB with can also be strongly typed or dynamic.

It's weird to me that we haven't settled on a common, stable, purpose-built high-level language specifically for business logic. Stuff that doesn't change much, that doesn't need to concern itself with the platform, only the business. That code should be portable to whatever shiny new underlying system gets invented; why does it keep having to be re-expressed every few years?

The situation is much better (though still far from ideal) when it comes to data. Everything knows how to use CSVs. Relational databases, from a schematic perspective, really haven't changed terribly much in decades. NoSQL came around but that was really just an alternative option; you don't see everyone scrambling to migrate their SQL data to Mongo. SQL isn't quite a standard, but it would be dramatically easier to migrate an ancient MS SQL database to Postgres than an ancient COBOL codebase to Java.

The great thing about standards is that there are so many to pick from.

What is the difference between what you are imagining and Java or python?

I guess I'm talking about a domain-specific language. Something where you only articulate business processes and formulas, and nothing more. Java and Python are programming languages. You use them to tell a computer what to do. Whereas this would be a representation language. You'd be able to carry that representation around with you and drop it into different programming contexts.

You should look at Sqlite, not just as a portable business environment, but as a data format and deployment format.

Yes, and with an English-like syntax for ease of use by non-sw professionals. It could be called something like Common Business Oriented Language...

Him... We do have a standard language for business data and rules. It's called SQL.

But current programs are 99% incidental complexity that it won't help solving, so programmers tend to avoid the language.

Well judging from the fact that my first computer my father bought back in 1988 Amstrad CPC 6128 is still alive and kicking with very active community and more development tools than it ever had, I think its safe to assume that software never dies. Only thing it takes is a small dedicated community and it can last for centuries. Partly because father and mothers infect their sons and daughters with their passion for the technology and the the loop never ends. What else never dies is necrophilia in software , apparently people are addicted to declaring software dead prematurely. Oh and of course clickbait because some people are desperate for views. It started with Java back in to 2000s and still going strong. I am not fan of Java but I am also not that delusional to declare Java dead. So no I think its pretty safe to assume VBA is not going anywhere.

JavaScript, for better or worse, is probably the most widely used language out there, by nature of being the language in the browser. The push for cross platform, mobile and web with frameworks that leverage it have leaned in on and expanded JS greatly. The engines are highly optimized (more than any other scripting language as feature rich), cross platform and nearly ubiquitous.

VBA just isn't. Anything not JS is just about a non-starter for the web versions of these applications, and that's where things are headed. If you're on Linux it's probably the only option in the space for a while.

JS is pretty decent, the ecosystem is massive, and you have the option of TypeScript if you want something more formal. Guessing everything MS offers will actually be written in TS, just consumable in plain JS or TS.

Reading the replies to this post, the title is most certainly click-bait and not encouraging deep discussions.

I still think that original Excel macro language was better than VBA. It essentially excel's formula language extended to be imperative language and represented as sheets. At the same time it was user friendly and had kind of lisp feeling.

If there's one thing I've learned in 15 years of IT work, it's that once business users have discovered a thing it will never die.

No matter how outdated, shitty, useless, etc. it may be, some jackass has built their entire workflow around it and has more power than you to prevent that from ever changing.

I have a soft spot for VBA. Programming macros in Excel was my introduction to programming; it made me realise how simple it can be to automate a repetitive process and save yourself literally hours a day with just a small investment of time.

VBA was my entree into professional programming and I'm not ashamed to admit it. I enjoyed using BASIC to solve my own problems (games) as a kid, but VBA showed me how fun it was to solve other people's problems.

VBA will "die" (in the same way fortran is dead) when people can record macros in another language, and then keep those macros baked into xlsm files.

Hopefully not. It's withstood the test of time, why replace it with something trendy?

Edit: Yes, Python has also withstood the test of time... as a scientific and web language. There's no reason to assume that it could replace VBA as a spreadsheet automation language used by relatively non-technical business folks.

Python is widely regarded as a very good first language for non-programmers.

With ExcelDna, which is open source .NET library, has very fast C API and rich COM API, I don't know why one would use VBA for new dev. One commenter here said debugging of interop libs is hard, but my experience is quite opposite - it works nice from Visual Studio with normal F5.

Microsoft will probably port VBA to WASM. With the mass of all MS Office dll involved, this can grow huge.

VBA was the first environment/language that I professionally used at the beginning of my career. It was so efficient to navigate in an environment that had 1000s of Excel files. I did not realise how fast I could develop the things needed to be done.

Short answer? No. Just like COBOL and fortran. With us until the heat death of the universe.

One of things that at least the old Microsoft was really good at was backward comparability.

Did they break that recently ?

They keep compatibility but abandon a lot of stuff. If they switch to JavaScript VBA development will slow to almost nothing and it will take years for the JavaScript stuff to catch up, if ever.

Something similar happened with visual studio. 2008 was really nice, very customizable and fast. Then they redid everything in 2010 and the result was slower with way less capability. And even now there are still a lot of features that 2008 had not implemented.

I also love vs2008 over vs2010 and still use it for some projects just because it feels so nice and snappy.

But which features exactly are missing in vs2010 that were in vs2008 (except the snappy feeling)? I have not actually noticed anything missing and would like to know more

2008 had an excellent macro recorder which was gone on 2010. 2008 had an easy way to customize toolbars and menus by drag and drop. With 2010 setting up a toolbar is much harder. There were a few other things but I don’t remember right now.

Joel Spolsky was making the claim that they had, fifteen years ago. (And I really tend to agree with him, both then and now.)

https://www.joelonsoftware.com/2004/06/13/how-microsoft-lost...

(Particularly relevant to this article, because Spolsky was the original 'designer' for the Excel/VBA integration: https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev... )

If they killed it today, then if VB6 is anything to go by, it will still be chugging along unconcernedly in 2050. VBA was a huge component of VB6, so it would be relatively easy ( I think) for MS to produce a 64 bit VB7.

Depends on what you're using VBA for, it is for Excel data processing or analytics. IMO it will likely die out, there are much better data solutions on the market for data analytics that use python.

I can relate to every single point he makes about how great VBA is compared to other languages and how annoying it is that MS has basically abandoned it in favor of JS.

As any single provider language, VBA will die on the moment Microsoft decides to kill it.

They don't seem to be in that mood, so no, not today.

this will definitely will be a huge relief for many sys-admins in windows environments... I think javascript isn't a bad choice since ms isn't as ignorant as some years ago, when it comes to new standards.

Hey, hey, my, my, VBA will never die.

The real question is whether VB6 will ever die.

TL;DR: No, or at least not anytime soon (author guesses 2035.)

    10 PRINT "Nope"
    20 GOTO 10

No, it will not.

Betteridge's law of headlines rings true.

Will VBA live?

Betteridge’s Law says no.

COBOL's Law also says no.

I wish Microsoft would just make a CSA already, c# for apps. This would be a huge win on so many fronts that I am shocked they havent done it already.

Increase C# mindshare.

Better Lang means a better ecosystem.

It's a free win.

It's also an ad for office. I started appreciating Outlook and Excel once I had vba filters and maps

Very surprised after all these years (decades?) they have not done something like this. Basic has been a low priority at MS for a long time now.

It could have made for an interesting alternative to PowerShell as well.

Not a PS1 expert by any means but you have to spend some time with it to appreciate the design decisions. It's a very "wholesomely" designed language.

Try eg. The for each parallel construct which made me realize why the output in PS1 behaves how it does.

That's always been the problem for me, I don't use it often enough to become fluent with it.

You're missing out. As a full time Linux user (since a year), PowerShell is by far the best shell out there (once it starts after 5 secs)

I'm not going to start using it for the sake of it. Some tools I just dip in and out of when I need to. Build software is the same, I haven't modified my build scripts for a year.

Yeah, and considering that the .NET 5 runtime will run in WebAssembly this is quite interesting. Maybe they are porting VBA libs to .NET. Then C# and VB.NET can pick up.

Or they do the real deal: Port VBA to .NET ... that however, would be a little bit difficult to explain with VB.NET in place as well ;)

It's just too much effort for nothing. People using VBA for automation pretty much live in the ecosystem of MS Office so it's not a big win for them. Plus MS has to spend huge sum of money to incorporate C# into Office.

And we already have VSTO.

VBA won't "die" anytime soon for the same reason Office or VB/VB.NET are still around. Legacy code. Almost every office in the US and probably around the world has VBA code in one of their office products ( excel, access, etc ).

Google spreadsheets was going to kill Excel, C# was going to kill VB, Sql Server was going to kill Access, etc. Look at how that turned out?

Maybe in an ideal world, but in the real world, there is a ridiculous amount of time, money and resources invested in VBA code. These sunk costs are very meaningful to corporations and governments and as long as corporations and governments give microsoft huge stacks of money, microsoft is going to keep VBA around.

Imagine Excel with Python.

Edit: I believe Libre has python.

https://www.xlwings.org/

I made some code using xlwings to allow you to emulate spreadsheets with machine learning models à la scikit-learn.

https://github.com/asemic-horizon/stanton

That's what Resolver One used to be. Sadly, it didn't survive.

https://youtu.be/u6EV2jiKRfc

As it is 2020 and VBA still exists. No.

We've taken 2019 out of the title above since if there's anything interesting about the article, it's not that.

Reply on news.ycombinator.com