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...
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#.
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.
.
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.
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'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
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.
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.
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.
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.
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.
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.
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.
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 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.
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.)
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
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.
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 ?
...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
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.
* 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.
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.
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).
Weirder things have happened, but whatever would replace it, would create an industry overnight.
Does anyone here have a side-by-side comparison of the LibreOffice BASIC vs the VBA?
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.
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.
What is the difference between what you are imagining and Java or python?
But current programs are 99% incidental complexity that it won't help solving, so programmers tend to avoid the language.
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.
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.
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.
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.
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... )
They don't seem to be in that mood, so no, not today.
The real question is whether VB6 will ever die.
10 PRINT "Nope"
20 GOTO 10Increase 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
And we already have VSTO.
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 ;)
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.
Edit: I believe Libre has python.