What’s Wrong With VBA?

Posted on by Rubberduck VBA

The Most Dreaded Language

The annual Stack Overflow Developer Survey has always ranked VBA pretty high on the “most dreaded” languages. For some reason this year VB6 and VB.NET aren’t making the list, but VBA is sitting at the very top of it, with 75.2% of respondents “dreading” VBA.

VBA is a gateway language – it was for me, anyway. It gets things done, and abstracts away boilerplate that you don’t really need to worry about in order to, well, get things done. For some, that’s good enough. As long as it works. Code is written to be executed, right? What if we wrote code for it to be read instead? Code that’s easy to understand, is easier to maintain and to extend without breaking things. Code that’s well organized, that uses small specialized and reusable components that can be tested independently, …is just objectively better code. And nothing in VBA says it can’t be exactly that.

Nothing is wrong with VBA. Obscure code with weird variable names, twisted code that’s hard to read and ever harder to follow, can be written in every single programming language yet invented or not.

VBA is a version of “classic” Visual Basic (VB5, VB6) that is hosted in another application. For a number of years Microsoft was selling a VBA Software Development Kit (SDK), and with it you could embed VBA in your own product to enable scripting against your own COM API / object model library: you could write an ERP (Enterprise Resource Planning) system, CAD software, vector graphics drawing software, anything really – and enable user extensibility through the VBA SDK. These were the golden years of Visual Basic: everyone knew VB. I happened to be in college around these years, and Programming I involved VB6. It was a gateway language back then too: “real programmers” wrote C++.

Visual Basic happened a few years after QBasic, which succeeded to BASIC. Edsger W. Dijkstra famously had this to say about BASIC:

It is practically impossible to teach good programming to students that have had a prior exposure to BASIC: as potential programmers they are mentally mutilated beyond hope of regeneration.

And yet after all these years, BASIC is still alive, through VBA and VB.NET. Bad code is on the programmer, not the language. And if you want to learn, you will learn: don’t let anyone tell you otherwise. Every single programmer alive was once a beginner, and whether you’re writing VBA, Java, C++, C#, heck even PHP, or the latest flavor-of-the-week of Javascript, remove the curly braces and semicolons and rule of thumb they could all pass one for the other if the same person wrote them all: ignore the brogrammers that think whatever language they code in is better than yours. Your mind isn’t any more mutilated than any other’s for it.

VBA is a full-fledged, mature programming language that has proven itself multiple times over the past 20 years (and more). It’s not just procedural code either: Visual Basic projects can define custom classes and spawn real COM objects; objects that can present multiple interfaces, expose and handle events, and these capabilities open doors no toy language can even begin to dream opening. “But it doesn’t do class inheritance! It’s not real object-oriented programming!” – sure there are limitations; but while class inheritance is cool, it’s also often and easily abused. Composition is preferred over inheritance for many reasons, and VBA lets you compose objects as much as you need. What makes inheritance so nice is in no small part that you get to treat all derived classes as their common base class, which gives you polymorphism: Car, Plane, and Boat can all be treated as a Vehicle, and each object might have different means to implement a Move method. VBA code can do this too, using interfaces. For the most part, VBA is only as limiting as you make it.

Among the useful things other languages do that VBA doesn’t, we notably find reflection: the ability to write code that can inspect itself – for example being able to query a VBA project’s type library to locate a particular Enum type, iterate its members, and store the member names and their underlying values in a dictionary. Reflection is made possible in .NET with a very detailed type system that VBA doesn’t have: writing some kind of reflection API for VBA isn’t impossible, but demands very intimate knowledge of how VBA user code and types work internally, and a way to access the internal pointers to these COM structures. Reflection is extremely powerful, but comes at a cost: it is generally avoided in places where performance matters.

VBA doesn’t support delegates, and doesn’t treat functions as first-class citizens: you can’t pass a function to another procedure in VBA; you pass the result of that function instead. This makes it hard to implement, for example, feature-rich data structures that can be queried and/or filtered: the querying and filtering needs to happen in-place using an explicit loop, and this makes the code more verbose than the equivalent in, say, C# or VB.NET, where such deeds would be accomplished using LINQ and other modern technology. But lambdas only appeared in Java rather recently, and their decades-long absence didn’t undermine Java’s claim to fame for all these years – .NET delegates are an incredibly useful tool to have at your disposal, but we can very well do without, albeit with a little bit more verbose code. And guess what? Fancypants LINQ code might be very elegant (if used well… it can also be a mind-wrecking nightmare), but .NET programmers tend to avoid using it in places where performance matters.

Error handling in VBA works with global runtime error state, and On Error statements that essentially set up conditional GoTo jumps. Other languages have exceptions and try/catch blocks… which essentially set up conditional GoTo jumps. Sure exceptions are great, and they can simplify error handling. But they are no silver bullet, and judging by the sheer amount of “real programmers” using them for flow control, or just plain swallowing them and moving on… bad exception handling in any language is just as bad as bad error handling in VBA.

The stigma around VBA and VB6 as a language, is also and perhaps even mostly due to the Visual Basic Editor (VBE) itself. As an IDE the VBE simply didn’t keep up, it was… pretty much abandoned. There’s a (now closed) question on Stack Overflow asking whether there are any refactoring tools for VBA. The top-voted answer was then a funny jab at the legacy editor, saying the only refactoring they know about, is search/replace (Ctrl+H). The editor itself feels like it’s actively working against writing full-blown object-oriented VBA code, or just plain old clean-reading code: all your classes are shoved under a single “Class Modules” folder, sorted alphabetically… so you resort to funky naming schemes just to visually regroup things by functionality. You might have toyed with interfaces before, but coding against them (i.e. to abstractions, not concrete types; c.f. the Dependency Inversion Principle) makes it impossible to navigate to the actual code that implements these interfaces. There’s no built-in support for unit testing, no mocking framework, no refactorings, no static code analysis, no code metrics, …and the list goes on and on.

The language does have its little annoying quirks (every language does), and some massively used type libraries (like Excel’s) do have their own little annoying quirks as well – but VBA as a language isn’t to blame for the quirkiness of some type libraries, even first-party ones developed by Microsoft.

VBA isn’t what’s wrong with VBA. The Visual Basic Editor is. If only there was a VBIDE add-in that made working with VBA more pleasant…

Published by Rubberduck VBA

I'm Mathieu Guindon (Microsoft MVP Office Apps & Services, 2018-2022), you may have known me as "Mat's Mug" on Stack Overflow and Code Review Stack Exchange. I manage the Rubberduck open-source project, whose goal is to bring the Visual Basic Editor (VBE) - VBA's IDE - into the 21st century, by providing features modern IDE's provide.

21 thoughts on “What’s Wrong With VBA?”

  1. “Other languages have exceptions and try/catch blocks… which essentially set up conditional GoTo jumps. Sure exceptions are great, and they can simplify error handling. … bad exception handling in any language is just as bad as bad error handling in VBA.”
    Can you do a blog post/primer on error handling in VBA? It is still what I have the most trouble with by FAR. It always feels clunky and hard to use.

    Liked by 3 people

    2. I love VBA. Especially the way it separates the functions, subs by a line.
      Any big code is easy to handle due to this.
      It’s ability to view and edit the different blocks of code separately and also as aone big collection . I miss it badly in VScode

      Like

  2. I agree with these, 100%. I’m just starting to unravel the power of VBA. All thanks to this site and all the articles for explaining details on the level of beginners. All thanks to Rubberduck for “surfacing” the power of VBA and making coding an enjoyable experience.

    Liked by 1 person

  3. I’m not a professional programmer but I’ve found that perfectly acceptable try, catch, finally structures can be implemented in VBA by the use of On Error Resume Next, testing error numbers through err.number, and On error goto 0. The typical examples of error handling with labels and multiple gotos is utterly utterly horrible.

    Like

    1. There’s a (much) better way though: with vbWatchDog (a commercial commercial VBE add-in whose author contributed to Rubberduck), you get programmatic access to the call stack, and get to implement *actual* try/catch/finally. If there’s one single commercial add-in I can vouch for (it’s not all that expensive if you’re using VBA regularly), it has to be vbWatchDog. What it does to tap into VBA’s error handling is pure black magic awesomeness! I’ll be writing about error handling soon-ish, with and without vbWatchDog.

      Liked by 2 people

      1. I would also recommend adding a live tracking with VBA Telemetry. It brings together async tracking of VBA projects with Azure Application Insights. P.S. I’m the author.

        Like

  4. My experience of vbWatchdog is that you can only have one catch clause per sub/function so it is not a complete replacement.

    Like

  5. > My experience of vbWatchdog is that you can only have one catch clause per sub/function so it is not a complete replacement.

    That’s inaccurate – you can have multiple catches – the key is to use `Catch N` for specific handling of a particular error and `CatchAll` (which can only be one).

    Liked by 1 person

  6. I find myself in agreement with the vast majority of this article, except for the very last two points.

    The editor isn’t nearly as bad as you make it out to be. The VB 6 editor, which the VBA editor is heavily based upon, was way ahead of its time when it was released, and it has aged quite gracefully. No, it doesn’t have “refactoring” features, but who needs that? I write most of my code in a text editor like Programmer’s Notepad (regardless of programming language), which doesn’t have a refactoring tool, either. Visual Studio didn’t have refactoring tools for C++ until one or two releases ago, circa 2017. You don’t need an IDE to provide that. IntelliSense and a powerful text editor are sufficient, and VBA delivers on both counts. The killer feature of an IDE is its debugger, and VBA’s is also fantastic. Modern versions of Visual Studio are truly best-in-the-industry, and classic VB/VBA is not really that far behind for day-to-day debugging workflows.

    No, the *real* problem with VBA as a language is the error-handling, contrary to your assertion otherwise. On Error just doesn’t even come close to cutting it. Unstructured error handling is awful, and production code ends up cluttered with 80% error handling and 20% business logic. It’s so hard to get error-handling correct in VBA that most developers don’t even bother to include it.

    If I could fix one thing in classic VB or VBA, it would be the error-handling constructs—*hands down*, no exceptions.

    Liked by 1 person

    1. Thanks! Without debating IDE vs text editor, I don’t totally disagree with your assessment. But I’m a very satisfied long-time JetBrains ReSharper user and I find refactorings very useful when I do need them; ReSharper’s static code analysis taught me a lot about C#… I’m convinced code metrics and inspections can help beginners learn new tricks, and veterans clean up minor inconsistencies.
      The VBE was indeed glorious… In 1998. Today every modern IDE has features the VBE now lacks and will never get, if not through its extremely slim extensibility API.
      As for error handling… the good news is that vbWatchDog really fixes it. The bad news is that I think Wayne deserves every last penny he gets for his game-changing commercial add-in.

      Like

  7. At office 2016, if the code of the add-in has active lines on Error Error Resume Next, then when running macros that worked in 2010, there will be an error Appliction-defined or object-defined error.
    Even if you put Tools – Options – General-Break on Unhandled Errors – 2016 does not skip Resume.
    Now I do without Resume.

    A simple instruction to copy the sheet itself after itself.

    With Ws_Act
    .Copy After: = Ws_Act
    End with

    It has been successfully tested both by units and manually. “Suddenly” began to issue Automation Error.

    The copying instruction itself is good – the compiler added an error to it if there was a loss of type in the hierarchy – some other function took the WorkSheet argument, and they started giving it Nothing – and the VBE error is not at the place of type loss, but where the hell where you never think of something – copying a sheet.

    For example, there was a working code:

    Sheet_Top_Cast (_
    Schedule_Build (_

    Schedule_Build returned WorkSheet, and Sheet_Top_Cast accepted.

    Not yet appeared:

    Sheet_Top_Cast (_
    Graphic_Decorate (_
    Schedule_Build (_

    Graphic_Decorate did not return anything, and Sheet_Top_Cast was waiting for a WorkSheet. And VBE beat a mistake (evil tore) on a distant function with a copy of the sheet.

    After the decor decorator:

    Sheet_Top_Cast (_
    Sheet_New (_
    Graphic_Decorate (_
    Schedule_Build (_

    An incomprehensible error on .Copy disappeared.

    Thanks to the rubber duck, slowly I join the refactor of my code.

    Like

  8. While I agree with everything you said, I think you missed one critical problem with VBA; references. It’s very sensitive to it’s run time environment as a result, and that’s probably its biggest downfall.

    While it’s true you can inspect references before you start running code, it’s very difficult to do in practice. I wish this was something the VBA team had addressed in the past.

    There are other minor annoyances; phantom breakpoints, source vs p-code getting out of sync, etc. But those are rare and overall it’s a nice language to work in.

    Like

    1. True, early-bound refs are kind of version-specific… but I would venture to say that’s more of a Windows Registry and COM problem (“DLL Hell”?) than something to squarely blame VBA for. The VBE’s “add/remove references” dialog in itself is even more annoying IMO, and Rubberduck can/does address that, and now shows you what refs are broken right there in the Code Explorer, just like VS does.
      Most useful libraries people reference in VBA are frozen in time though, so version isn’t much of a problem: late-binding e.g. scrrun.dll (or msxml, or scripting regex, or ADODB, or, or…) is mostly useless, VBScript runtime is the same version of every Windows box ever built in the last 20+ years. It *is* more of a problem when automating other Office applications though, but referencing the earliest version you’re going to run against typically goes pretty smoothly; problems begin when you build on 2016 and try to run it on e.g. 2010, especially if you’re using members that didn’t exist in the 2010 library… But late-binding can’t save you from that either!
      There’s so much misinformation out there about late-binding, it’s scary. I read just last week some highly-ranking “VBA best practices” post claiming all refs should be late-bound all the time, …so that the code can still run if the library is missing, but not a single word on guarding against a run-time binding failure; it’s basically claiming late-bound code magically works if the library is nowhere to be found on the target machine.

      That said I’ve had source code go out of sync with the compiled/running code in C# too, with hollow breakpoints not being hit. Truth is, any non-trivial piece of code can break in many non-trivial ways… VBA just happens to not be an exception.

      Like

  9. One thing I personally find very limiting about the VBA IDE is the MDI. Why it is that I can drag any of the other windows such as the Locals Window or the Immediate Window outside of the parent, but I can’t drag actual code windows outside the parent is beyond me. I work with 6 monitors and stretching the VBE parent window across them is painful and still makes it a pain to manage the active code windows. I’d love to be able to more efficiently use my screen real estate. Any change the IDE could be modified to support an SDI. Clearly Microsoft saw the benefit of moving to an SDI for Office 2013, but the VBA IDE didn’t get the same treatment.

    Like

  10. I’m a fan of vbWatchDog as well. I’ve used it since it came out. It means you can set up a central error-handler and then, usually, forget about error-handling. If you want to catch specific errors you can do that to. Brilliant product and, no, I am not the author.

    Like

  11. After the introduction of Power Query (M) and Power Pivot (DAX) in Excel from 2010 the role of VBA in the Data pull world (ETL + Analysis) is greatly reduced. It still has some scope in the Data Push world (Write back to DB) and Formatting reports and Workflow automation

    Like

  12. Honestly, i’ve written tens-of-thousands-of-lines-of-code in VBA and I have never really hated it once I truly understood it.
    I think the biggest problem people have with VBA is that everything can be done dozens of ways, but most of those ways aren’t even close to efficient in terms of processing time.
    I’ve created full-fledged applications with over 30 “macros” embedded in them (activated by buttons on a dashboard and triggered by buttons on each sheet), integrated Excel and Office, Excel and Outlook. Created detailed, table formatted email reports that are auto generated after reviewing and scraping a complete data sheet — this single macro alone saved my boss hours of manual work during our most key busy time of the year.
    This most likely isn’t even touching the surface of the possibilities. If someone hates VBA, they simply need to study more. I do myself – I can only imagine what I am to learn next.

    Like

Leave a comment