So, I haven’t posted here for well over a year now, and Rubberduck ‘s last “green release” was in May of 2021 (18,773 downloads as of this writing – the download stats on the site’s front page are stale, as of last February). The website has been “under construction” forever, and all this time I barely answered or participated on Stack Overflow and have been pretty much a ghost in the project’s dev chat and GitHub repository.

Let’s call that a sabbatical. I mean, COVID lockdowns and 2020-2021 hit hard for pretty much everyone, so I’ll just say s..tuff happens and you just have to let things go and do your best to enjoy the ride regardless.

And I did! But as of 2022 July 1, I’m no longer a Microsoft MVP: I did not apply for a 4th renewal, because I know what MVPs do, and I know what I can do myself, and I can’t be honest with myself and pretend the little content I put up in 2021 makes a solid application worthy of consideration. It’s been an amazing experience, but I wouldn’t have been comfortable with accepting this annual award for what I did that year: the Microsoft MVP award means much more than that to me.

As of this writing, the repository has over 1.6K stars on GitHub, 275 forks, and a snapshot of it is even archived along with thousands of other public repositories in the GitHub Antarctic Vault! I’m proud of what we have accomplished, and proud to be able to say Rubberduck has made it beyond the wildest dreams we had for it back in 2015! Years fly… I haven’t written any VBA code in a while, but I wasn’t writing much VBA code anyway ever since C# has been putting the bread on my table, and that never stopped me (or anyone) from working on Rubberduck. I still have ideas and now & then think of where the project should be heading, and the time will definitely come when I can turn that into code again – just not now.

What about Rubberduck?

Rubberduck has come a long, a very long way, and we’ve learned a lot all along – devs and users alike! I kind of burned out though. Nothing seemed to ever make a dent in the growing mountain of issues, and the code base being quite daunting doesn’t make it particularly friendly to new contributors or would-be C# programmers coming from VBA: it was all going to be on pretty much just us to churn through it all. Like answering on Stack Overflow, it became a chore. I couldn’t write anymore, and figured the last post I wrote made a nice front page for the blog anyway.

I strongly doubt Rubberduck can get where we envisioned it for 3.0 in the current state of the project. We could get a v2.6 out there, perhaps up to 2.9 even, but we always wanted more out of a 3.0 build, and the scale and implications of the changes needed for that to happen are rather scary.

I want the next major version to do away with performance issues, from startup to teardown; I want a smaller memory footprint; asynchronous, continuous parsing; total control over what we get to show in a code pane… but we can’t have that without a complete overhaul of the entire architecture and a redesign of the deepest core mechanisms in Rubberduck.

So where’s Rubberduck headed as a project? On one hand we have a massive and mature code base with roughly 200 known issues (and over 700 unimplemented ideas); on the other hand we have a goal that cannot realistically be fulfilled incrementally from where we’re at. I think we need to start with a brand new shell, and bring in the original code piece by piece, adjusting them as we go.

Meanwhile twinBASIC is steadily progressing, but still isn’t quite ready to power a brand new language layer for Rubberduck yet, so we’re still stuck with our Antlr4 grammar and parser, and step one would be to implement a parser that can survive syntax errors so Rubberduck can still process code that cannot run. You see ever since we introduced Antlr4, we’ve been using its token positions to locate specific code in code panes; when a new line gets inserted or any part of any code moves around, a new parse is needed for that entire module to keep these tokens in sync with what’s in the VBE. This is what’s painted us into a corner of endless parse-edit-reparse cycles.

It’s not just the tokens, it’s also what we’ve built on top of it: the `Declaration` class has so many constructors with so many constructor parameters… everything that isn’t a comment, from a line label to variables and procedures, is a declaration. Over the years a decent inheritance hierarchy was built so we have many types of specialized declarations, but each and every one of them is a rather heavy object, and thousands upon thousands of them need to be spawned every time we parse so that the resolver can keep track of everything. Then the VBE is as always actively sabotaging our efforts by hiding attributes, so a complete parse must involve exporting every module to a temporary file that we parse for these hidden attributes, which then need to be associated with the correct declaration.

In order to happen at all, Rubberduck 3.0 needs to completely flip things around, so instead of synchronizing Rubberduck with the VBE, we’d be synchronizing the VBE with Rubberduck. The implications are pretty radical: the VBE gets reduced to a mere host, and Rubberduck needs to be the code editor now, instead of interacting (and fighting!) with the VBE. In other words, I hate to say this but Rubberduck can’t really go forward without essentially a full rewrite, and without implementing what amounts to a whole IDE, inside the VBE.

Known showstoppers include hooking up and interacting with the debugger: there is no API for this, so there wouldn’t be any way to track execution, hit breakpoints, and step through the source code in the custom editor; clearly it’s a sub-par user experience, but if it’s the best we can get at one point we’re going to have to bite the bullet and make it happen anyway, and have the legacy VBE take over when code is running or paused.

So what’s likely going to happen is that the 1.6K-stars repository will remain on the 2.x architecture, and a brand new repo will eventually be up for the 3.0 rewrite. This is obviously risky, because a “Grand Redesign in the Sky” rarely ends well, but I’m not seeing any other way. I guess we’ll just have to see where that goes. Worst-case, we scrap it, stick with the 2.x architecture, and let go of the dream.

What about the website?

Early this year I had a fully working .net 6 solution that I intended to host on Microsoft Azure, but the monthly fees would quickly have depleted the donations we get and it just would not have been sustainable at all, so I thought “hey no problem I’ll just deploy it to the current GoDaddy host”… but the latest version of .net that runs over there is Framework 4.8, which means the solution had to be re-targeted and many little pieces had to be reworked – that’s done now, but the Bootstrap stuff broke and the entire front-end makes no sense anymore and I haven’t really had a chance to spend much time fixing it since then.

Between getting the ball rolling on v3.0 and getting the new website online, my priority is on the website, because well, at the end of the day I’m paying for this hosting and https — many thanks to our donors for this!

The revamped website will have much less static content and pull feature pages from its database; members of the Rubberduck GitHub organisation can edit this content as needed, easily upload updated screenshots, etc. The idea is to make the website be a huge user manual that describes every single feature and sub-feature, without needing a new deployment every time a typo needs fixing. I can’t yet commit to an ETA, but as things in my life fall back into place I’ll start having more time for this in the next few weeks/months. Let’s just say I intend to have it up before the next edition of Hacktoberfest!

What about the blog?

I love writing, so I’m not done here. As a Microsoft MVP I ended up feeling pressured to produce regular content to justify my renewals, and that wasn’t healthy. As I usually took inspiration from various answering experiences on Stack Overflow, I didn’t want my first post in over a year to be just a casual one about programming in VBA: I needed an ice breaker of some sort, and this is it. I’ll be writing more regularly now, but at my own pace.

So yeah, I’m *slowly* coming back from a rather long hiatus with a lot of rather large projects on my plate, and I intend to not burn myself up doing any of it, so this will mean rigorously time-boxing my activities and keeping a sane balance between Rubberduck, the website, the blog, the MVVM library that I still intend to build with twinBASIC, but also with my day job and family life. I’ll be happy if that makes me a Microsoft MVP again in 2023, but it’s not what I’m shooting for.

Next post should be about the upcoming Rubberduck 2.5.3 green release, it’s well past due!

A few months ago I wrote about how I envisioned dealing with module and member attributes in the 2.1 cycle:

Annotation/Attributes: fixing these inspection, and the quick-fix that synchronizes annotations with module attributes and vice-versa, will finally expose VB module and member attributes to VBA code panes, using Rubberduck’s annotation syntax.

The idea was to use “magic comments” to annotate a module or a procedure with, for example, this:

'@Description("Does something.")
Public Sub DoSomething()
End Sub

And then since we know there’s a DescriptionAnnotation here for DoSomething, and we also know that there’s no VB_Description attribute for that member because of how we parse VBA code… then we’d issue an inspection result saying “annotations and attributes aren’t synchronized”, and you’d run a quickfix and there you have it, a comment that inserts invisible code.

And, it “works”. But there’s a showstopper here: the VBE itself.

You see the code that’s in the editor is not all the code that’s actually in that file: the VBE is hiding the attributes. So the line positions we get from the VBIDE API aren’t the line positions we’d get from reading the complete code… and this complicates things… rather massively.

We have one parse pass on the exported code to pick up these attributes. Then one parser pass on the in-editor code to build our knowledge of the code as the user sees it.

So we have two ModuleRewriter, each working independently on their own version of the same code. And when the code pane rewriter… rewrites, we run into an annoying VBE quirk I [annoyingly] had already experienced before:

I think that’s a VBE quirk – editing the signature of the method, whether manually or with Rubberduck, tends to obliterate the VB_Attributes (and the shortcut key is a VB_Attribute). — ThunderFrame

We need a “code pane pass” to avoid mangling the code with offset line positions, because the VBE is hiding the attributes.

What’s so important about these attributes anyway? Why bother?

Our resolver needs the critical metadata that’s hidden there. Without them, knowing the actual scope of a declaration is impossible. Nowhere else can we pick up a hint about a VB_PredeclaredId attribute, or VB_Exposed. Besides, Rubberduck knowing about VB_Description attributes has very interesting implications, UX-wise. Even better once we handle line breaks:

The Solution

Of course there’s a solution. We’ve been chatting about this solution for a long time now. There won’t be subclassed code panes. The VBE is literally fighting with everything it’s got, to keep its scrollbars in place and visible – it’s a better idea to pick one’s battles and drop that idea. So instead we’re going to abuse docked toolwindows and put our entire UI in a single such toolwindow – and that includes the current toolwindows/panels, …as well as the code panes client area.

Once we have our very own code pane user control, what happens in it is under our complete supervision, and we’re free to intervene at any point in the process of code being modified, in any conceivable way.

So the idea would be to bring the attributes themselves into the code panes (optionally hidden), and shave off an entire parser pass just like that. This pretty much makes the whole annotation+inspection+quickfix combo rather useless.

So instead of pursuing a lost cause, I’ve decided to drop my work on getting attributes to synchronize with annotations, and get on with the other 2.1 cycle targets: the attributes problem will just solve itself when we get there.

In the meantime I’d recommend to disable the MissingAnnotation and MissingAttribute inspections. Not that the inspections themselves are harmful, but the SynchronizeModuleAttributes quickfix is: that quickfix is being decommissioned.

Your VBA project is embedded in an Excel workbook. It references the VBA standard library; it references the library that exposes the host application’s (i.e. in this case, Excel’s) object model; it includes global-scope objects of types that are declared in these libraries – like Sheet1 (an Excel.Worksheet instance) and ThisWorkbook (an Excel.Workbook instance). These free, global-scope objects are right here to take and run with.

You’re free to use them, wisely.

True, they’re global – they can be accessed from anywhere in the code.
They can… and that doesn’t mean they should.

And if you’re willing to do whatever it takes to abstract away the host application’s object model in your “business logic”, then you can isolate your logic from the worksheet boilerplate and write pretty much the same code you’d be writing in, say, VB.NET… or any other object-oriented language for that matter.

Abstracting Worksheets

There is no worksheet. There is data. Data going in, data going out: data is all it is. When the data is coming from a database, many programmers immediately say “I know! Let’s implement a repository pattern!“, or otherwise come up with various ways to abstract away the data-handling boilerplate. If you think of worksheets as data, then it’s not any different, really.

So we shall treat worksheets as such: data. What do we need to do with this data?

Some tried to make worksheets Implements interfaces, and ran into issues (here too, and oh another). I completely agree with this post, which basically boils down to don’t.

Whatever you do, don’t make worksheets implement an interface.

Wrap them instead. Make a proxy class implement the interface you need, and then make sure everything that needs anything on a worksheet, accesses it through an interface, say IWorkbookData.FooSheet, where FooSheet is a property that returns a FooSheetProxy instance, exposed as an IFooSheet.

The only thing that ever needs to use a FooSheet directly, is a FooSheetProxy.

I don’t know about you, but I don’t like diagrams. So how about some real-world code instead?

Say you have an order form workbook, that you distribute to your sales reps and that come back filled with customer order details. Now imagine you need a macro that reads the form contents and imports it into your ERP system.

You could write a macro that reads the customer account number in cell O8, the order date in cell J6, the delivery and cancel dates in cells J8 and J10, and loops through rows 33 to 73 to pull the model codes from column F, and the per-size quantities in columns V through AQ… and it would work.

…Until it doesn’t anymore, because an order came back without a customer account number because it’s a new customer and the data validation wouldn’t let them enter an account that didn’t exist at the time you issued the order form. Or you had to shift all the sized units columns to the right because someone asked if it was possible to enter arbitrary notes at line item level. Or a new category needed to be added and now you have two size scales atop your sized units columns, and you can’t just grab the size codes from row 31 anymore. In other words, it works, until someone else uses (or sees) it and the requirements change.

Sounds familiar?

If you’ve written a script-like god-procedure that starts at the top and finishes with a MsgBox "Completed!" call at the bottom, (because that’s all VBA is good for, right?), then you’re going to modify your code, increase the cyclomatic complexity with new cases and conditions, and rinse and repeat. Been there, done that.

Not anymore.

Name things.

Abstraction is key. Your code doesn’t really care about what’s in cell O8. Your code needs to know about a customer account number. So you name that range Header_AccountNumber, proceed to name all the things, and before you know it you’re looking at Header_OrderDate, Header_DeliveryDate and Header_CancelDate, and then Details_DetailArea and Details_SizedUnits named ranges, you’ve ajusted your code to use them instead of hard-coding cell references, and that’s already a tremendous improvement: now the code isn’t going to break every time something needs to move around.

But you’re still looking at a god-like procedure that does everything, and the only way to test it is to run it: the more complex things are, the less possible it is to cover everything and guarantee that the code behaves as intended in every single corner case. So you download Rubberduck and think “I’m going to write a bunch of unit tests”, and there you are, writing “unit tests” that interact with the real worksheet, firing worksheet events at every change, calculating totals and whatnot: they’re automated tests, but they’re not unit tests. You simply can’t write unit tests against a god-like macro procedure that knows everything and does everything. Abstraction level needs to go further up.

The [Order Form] worksheet has a code-behind module. Here’s mine:

'@Folder("OrderForm.OrderInfo")
Option Explicit

Yup. That’s absolutely all of it. Your princess is in another castle. A “proxy” class implements an interface that the rest of the code uses. The interface exposes everything we’re going to need – something like this:

'@Folder("OrderForm.OrderInfo")
Option Explicit

Public Property Get AccountNumber() As String
End Property

Public Property Get OrderNumber() As String
End Property

'...

Public Function CreateOrderHeaderEntity() As ISageEntity
End Function

Public Function CreateOrderDetailEntities() As VBA.Collection
End Function

Public Sub Lockdown(Optional ByVal locked As Boolean = True)
End Sub

Then there’s a proxy class that implements this interface; the AccountNumber property implementation might look like this:

Private Property Get IOrderSheet_AccountNumber() As String
    Dim value As Variant
    value = OrderSheet.Range("Header_Account").value
    If Not IsError(value) Then IOrderSheet_AccountNumber = value
End Property

And then the CreateOrderHeaderEntity creates and returns an object that your “import into ERP system” macro will consume, using the named ranges defined on OrderSheet. Now instead of depending directly on OrderSheet, your macro depends on this OrderSheetProxy class, and you can even refactor the macro into its own class and make it work against an IOrderSheet instead.

What gives? Well, now that you have code that works off an IOrderSheet interface, you can write some OrderSheetTestProxy implementation that doesn’t even know or care about the actual OrderSheet worksheet, and just like that, you can write unit tests that don’t use any worksheet at all, and still be able to automatically test the entire set of functionliaties!

Of course this isn’t the full picture, but it gives an idea. A recent order form project of mine currently contains 86 class modules, 3 standard modules, 11 user forms, and 25 worksheets (total worksheet code-behind procedures: 0) – not counting anything test-related – and using this pattern (combined with MVP), the code is extremely clear and surprisingly simple; most macros look more or less like this:

Public Sub AddCustomerAccount()
    Dim proxy As IWorkbookData
    Set proxy = New WorkbookProxy
    If Not proxy.SettingsSheet.EnableAddCustomerAccounts Then
        MsgBox MSG_FeatureDisabled, vbExclamation
        Exit Sub
    End If

    With New AccountsPresenter
        .Present proxy
    End With
End Sub

Without abstraction, this project would be a huge unscalable copy-pasta mess, impossible to extend or maintain, let alone debug.

See, there is no worksheet!