Introducing RDCore: RD-VBA Language Platform v0.1.0-alpha

Close-up of a weathered dark coin featuring a raised duck emblem and partial lettering
V I V A T ❤️ C U C U M I S
[English version follows]

Rubberduck renaît

La plateforme RDCore franchit aujourd’hui une étape importante avec sa première version alpha publiée officiellement. Cette pré-version peut être téléchargée et installée dès maintenant, et fonctionne sous Windows, Linux et macOS. Pour le moment, seules les plateformes 64 bits sont prises en charge.

La plateforme accueille RD‑VBA, une implémentation de la spécification MS‑VBAL corrigée sur certains points, enrichie sur d’autres, qui permet notamment d’utiliser PEEK et POKE dans le mode BASIC, ajoute les constantes de précompilation #If Linux et #If Linux64, et compile vers l’IL de .NET 10.

Téléchargez la version alpha ici : https://github.com/rubberduck-vba/RDCore/releases/tag/v0.1.0-alpha

RD-VBA

Le cœur du langage réside dans le SDK (NuGet : v0.1.2‑alpha). Il définit une variante de VBA ainsi qu’une implémentation complète de sa bibliothèque standard capable de s’exécuter partout où .NET est disponible, sans dépendre du tristement célèbre environnement d’exécution Microsoft Visual Basic ni d’une application hôte quelconque. La plateforme fournit son propre environnement d’exécution.

Automatisation COM

VBA a toujours été capable d’automatiser des objets COM sans être hébergé dans le processus concerné. C’est exactement ce qui se produit lorsqu’une variable reçoit un New Excel.Application ou qu’un appel à CreateObject(“Excel.Application”) est effectué.

Comme RD‑VBA s’exécute en dehors des applications Microsoft Office, cette approche devient désormais la méthode normale d’interaction avec COM.

rdc build

En contrepartie, vous gagnez la possibilité de compiler du code RD‑VBA en exécutables autonomes. Les commandes rdc build et rdc run font exactement ce que leur nom laisse entendre.

Serveur de langage

Le cœur du langage n’est qu’un modèle. Ce qui lui donne vie, c’est l’architecture client/serveur LSP qui constitue la plateforme. En son centre se trouve le serveur de langage, qui coordonne les échanges entre l’hôte d’exécution, le client ou l’EDI et les extensions de la plateforme.

Le serveur fournit l’implémentation du protocole LSP 3.17 par l’intermédiaire d’OmniSharp 0.19. Les erreurs de syntaxe, de compilation et, lorsque possible, d’exécution sont signalées sous forme de diagnostics dès la phase de conception.

Les listes d’autocomplétion sont disponibles pour les mots-clés et les identificateurs, et prennent en charge la recherche par bosses PascalCase : saisir « mb » permet par exemple de retrouver MidB et MsgBox.

La plateforme elle-même n’inclut pas d’EDI*; cependant, rdc.exe agit également comme client LSP autonome et lance une console interactive RD‑VBA/BASIC lorsqu’il est exécuté sans argument.

*mais attendez, ce n’est pas tout!

RDCore.HexIDE

Sans faire directement partie de la distribution RDCore, ce module complémentaire pour HexIDE agit comme client LSP. Il télécharge la plateforme si nécessaire, avec votre consentement, puis assure la liaison entre l’EDI et le serveur de langage.

Pour installer RDCore.HexIDE, téléchargez le paquet signé depuis sa publication GitHub, extrayez-le dans un dossier qui lui est propre sous le dossier addins de HexIDE, puis redémarrez HexIDE.

HexIDE est né comme projet d’EDI VB6 accompagné d’un interpréteur expérimental. Avec RDCore comme moteur, il devient naturellement l’environnement de référence pour les espaces de travail RD‑VBA.

Importation de code source MS‑VBA

Les utilisateurs de Rubberduck dans le VBIDE connaissent probablement déjà les fonctionnalités d’exportation et de synchronisation de projets destinées au travail sous gestion de versions.

Sinon, exportez simplement les modules de votre projet dans un dossier dédié puis ouvrez ce dossier comme espace de travail dans HexIDE. RDCore génère un fichier .rdproj qui recense tous les éléments appartenant au projet, qu’il s’agisse de code source ou non.

Votre espace de travail peut ainsi contenir des fichiers README.md, des images, des ressources localisées ou tout autre actif chargé à l’exécution.

Le code MS‑VBA qui suppose la présence d’un hôte devra être adapté pour recourir à l’automatisation COM externe. Le code qui utilise déjà cette approche devrait fonctionner tel quel.

Importation d’un projet VB6

Les projets VB6 existent déjà sous forme de fichiers et sont décrits par un fichier .vbp. Lorsqu’un tel espace de travail est ouvert dans RDCore, le fichier .rdproj correspondant est généré automatiquement à partir du projet VB6 existant.

Là encore, la compatibilité devrait être largement transparente, avec quelques réserves.

Applications graphiques

RDCore héberge nativement MSForms 2.0 sur les postes Windows qui disposent de cette bibliothèque en version 64 bits. En revanche, il n’héberge pas les formulaires VB classiques, même si tous les symboles associés sont définis au moment de la compilation.

Le projet RDForms est actuellement en développement et sera publié pendant le cycle alpha. Cette infrastructure d’interface utilisateur reposera sur le rendu multiplateforme Avalonia et sur le moteur d’exécution RDCore.

L’objectif est de réunifier les formulaires VB classiques et MSForms dans une seule plateforme moderne capable d’importer les fichiers historiques .frm et .frx, tout en offrant une API événementielle familière pour le code existant et une API MVVM pour les nouveaux développements.

Un concepteur XAML pour HexIDE fait également partie du programme.

D’ici la publication de RDForms, les interfaces utilisateur interactives restent limitées aux systèmes Windows disposant de MSForms 2.0 en version 64 bits.

Applications console

Les applications ne nécessitant aucune interface graphique peuvent s’exécuter partout où .NET est disponible.

Sous Windows, MsgBox utilise une boîte de dialogue Win32 native. Sur les autres plateformes, la fonction est fournie par l’environnement hôte (rdc) et affiche une boîte de dialogue simulée en mode texte dans la console. InputBox suit le même principe.

Lorsqu’une application est exécutée avec rdc run –unattended, toute tentative d’interaction avec l’utilisateur provoque une erreur d’exécution mettant fin au processus.


À vous de jouer!

RDCore entame son cycle alpha avec une implémentation LSP déjà remarquablement avancée. Les fonctionnalités essentielles attendues d’un environnement moderne sont présentes aujourd’hui et continueront d’évoluer au cours des prochains mois.

C’est donc le moment idéal pour explorer RDCore et HexIDE.

Et si vous ne savez pas par où commencer, le dépôt RDCore.Demo.NavalBattle constitue la vitrine officielle de la plateforme. Cette adaptation RD‑VBA du code MS‑VBA original publié sur ce blogue propose une interface MSForms sous Windows et une interface console sur toutes les autres plateformes.

Consultez le site de documentation utilisateur pour découvrir tout ce qu’il faut savoir sur RDCore.


[English]

Rubberduck, reborn.

The RDCore platform has officially released a tagged alpha build: a pre-release that you can download and install, and that runs on Windows, Linux, and macOS. For now, it is only available on 64-bit systems.

The platform is the home of RD-VBA, an implementation of the MS-VBAL specification, corrected in places and enhanced in others, that lets you PEEK and POKE at memory space in BASIC mode, adds #If Linux and #If Linux64 precompiler constants, and compiles to .NET 10 IL.

Download the alpha release here: https://github.com/rubberduck-vba/RDCore/releases/tag/v0.1.0-alpha

RD-VBA

The language core lives in the SDK (NuGet: v0.1.2-alpha) and defines a version of VBA and an implementation of its standard library that runs everywhere .NET runs, without the infamous Microsoft Visual Basic runtime and outside of any host application: the platform provides its own host.

COM Automation

VBA was always able to automate COM without needing to be hosted inside the COM process in question – it’s what you do whenever you set an object variable to a New Excel.Application, or when you CreateObject(“Excel.Application”). RD-VBA running outside a Microsoft Office host means you must do things that way now.

rdc build

What you gain in exchange is the ability to build standalone executables from RD-VBA source code, so CLI commands like rdc build and rdc run do exactly what they sound like they’re doing.

Language Server

The language core alone is just a model. What brings it to life is the LSP client/server architecture of the platform and its components, at the center of which we find the language server, which acts as a coordinator between the host and runtime, the client/IDE, and the platform extensions.

The server delivers LSP 3.17 through OmniSharp 0.19, progress reporting included. Syntax and compilation errors, and run-time errors whenever possible, are reported as design-time diagnostics. Completion lists are triggered for keywords and identifiers, and match PascalCase humps, so “mb” gets you “MidB” and “MsgBox”.

The platform itself doesn’t include an IDE*, but rdc.exe itself is a standalone LSP client that runs a fun RD-VBA/BASIC shell when executed without any command-line arguments.

*but wait, there’s more!

RDCore.HexIDE

Not directly part of the platform bundle, this LSP client is a HexIDE add-in that downloads the RDCore platform, with your consent, if it’s not already installed, and then becomes the bridge between the IDE and the language server.

To install the RDCore.HexIDE add-in, download the signed package from its GitHub release, extract it into its own folder under HexIDE’s addins folder, and restart HexIDE.

HexIDE started as a VB6 IDE project with a toy interpreter – with RDCore as a backend, it becomes the natural home of RD-VBA workspaces.

Importing MS-VBA source code

If you’re using Rubberduck in the VBIDE, you’re probably already familiar with the export project and synchronize folder features, which were intended for working with source control.

Otherwise, export your project’s modules to a dedicated local folder, and open that folder as a workspace in HexIDE: RDCore generates a .rdproj file that enumerates all the files that belong to the project, whether they’re source code or not – so your workspace can contain a README.md file and additional assets (images, resource strings, etc.) that source code loads at run-time.

MS-VBA source code that presumes a host must be adapted to automate COM from the sidelines. MS-VBA source code that already does this should work as-is.

Importing a VB6 project

VB6 projects already exist in the file system, and a .vbp file describes the project: opening such a workspace in RDCore will automatically generate the .rdproj file from the .vbp, and again should work as-is… with a few caveats.

GUI Applications

RDCore hosts native MSForms 2.0 on a Windows desktop that has the 64-bit library. It does not host VB Forms despite having all the symbols defined at compile time; hosting VB forms requires reengineering pieces that belong to Microsoft, and RDCore does not do that.

RDForms is under development and will ship during the alpha cycle; it is the UI framework that RDCore will ultimately be using: Avalonia cross-platform rendering with RDCore runtime support will be combining Classic VB Forms and MS-VBA’s MSForms into a unified, modern UI framework that seamlessly imports legacy .frx and .frm, and then exposes a familiar event-based API for legacy workloads, and an MVVM API for the new ones. A XAML designer for HexIDE is also part of the deal.

Until RDForms releases, interactive UI in RDCore remains limited to Windows machines that have Microsoft’s 64-bit MSForms 2.0 library installed.

Console Applications

An application that doesn’t need to display a UI can run anywhere .NET runs. On Windows, MsgBox is a native Win32 message box; otherwise, the function is implemented by the environment host (rdc) and draws a text-mode message box in the console; InputBox is in a similar condition.

When executed with rdc run with the –unattended switch on, code that requires such user interactions throws at run-time, failing the process.


Playtime!

RDCore launches its alpha cycle much further along its LSP implementation than most other LSP projects would: everything you expect is there and already works, and both the SDK and the platform will keep evolving in the next few months.

This makes the next few months the perfect time to play with RDCore and HexIDE, and if you don’t know where to start, there’s always the RDCore.Demo.NavalBattle repository, which is the platform’s showcase demo: it runs an MSForms UI on Windows, and a console UI everywhere else, and it’s an RD-VBA port of the original MS-VBA code initially published on this blog.

See the user documentation site for everything you need to know.

RDCore.CLI / Platform Updates

The RDCore language platform is taking shape and is about to get very serious about its ambitions: take MS-VBAL and implement it from the ground up into the LSP platform Rubberduck would have been if it became VBA.

I remember telling a friend last spring about my plans; “ok but what about the debugger?” they asked – I said where we’re going we don’t need a debugger, we are the debugger. Last week, we arrived.

MS-VBA halts your debugging session when you rename a symbol in the current instruction, presumably because the edit cannot be made in all places at once, leaving the program invalid in the interim steps.
RD-VBA merrily lets you resume after doing exactly the same, because renaming a symbol is an atomic operation that can perform multiple edits at once.

Edit and continue

Perhaps the single most defining feature of Classic Visual Basic, has arrived in RDCore: the ability to reshape the compiled program, observe and manipulate its symbols and values as it runs in a debugger.

E&C is a rather complex matter, and MS-VBA halts execution in quite several situations, for very valid reasons. The fact that RDCore allows resuming execution in places where MS-VBA would not, doesn’t take anything away from the historical feature: RDC’s internals are modeled in such a way that makes these limitations a bit easier to overcome, I suppose.

Edit and apply later

When RDCore cannot apply an edit, it often can (and does) hold on to the compiled version and issues a RDC20001 diagnostic that says the edit will be applied after the program restarts – rather than stopping the debug session right there and then.

Edit and take it back

If RDCore cannot apply an edit, and cannot hold on to the compiled version while keeping its state coherent, you can simply undo it, and then resume execution as if nothing happened.

See the documentation for a complete breakdown of the specific cases and the differences between RDCore and MS-VBA.

All debugger features are provided through standard DAP endpoints over stdio.


Code formatting

LSP defines endpoints for code formatting and format-as-you-type, but doesn’t specify what it specifically entails, leaving a language server free to do whatever it deems necessary to fulfill the request.

In RDCore, that means indenting the module of course – with legacy Rubberduck’s indenter settings imported from your Rubberduck configuration… a bit like how Rubberduck imported your legacy Smart Indenter settings on the first load.

In the REPL client (rdc.exe running standalone), formatting is applied whenever the program source code is read – so when you LIST or SAVE a .rdc program file.

Code formatting settings are editable (with completion lists!) from the interface with the CONFIG command, and take effect immediately.

Block foldings

At the start of any block, the LIST output will show ▽▲ glyphs in the margin, indicating the start of foldable and folded code blocks, respectively.

Use the FOLD command to toggle them; completion list offers the valid line numbers.

If a folded block hides a breakpoint, it is displayed in the margin on the folded line, even if that line doesn’t have a breakpoint.


Completion lists

Completion lists show available commands, keywords, standard library modules, functions, and procedures; enum arguments, object members. All with the signature and docstring underneath.

The one single thing that makes the biggest difference in how hard or easy it is to learn a programming language, is its completion lists: without it, everything feels like a magic incantation that you must know in order to make anything work at all.

With it, it’s like the standard library is telling you how to use it, and you can explore all the types and members and discover things, all without leaving your editor.

RD-VBA completion is snappy and relevant, and signature help provides the docstrings as you type – in the language configuration of your session for the standard library (although currently only available in French and English).


MSForms hosting

The RD-VBA runtime is now capable of hosting MSForms. It does not, at this stage, host its own “RDForms” cross-platform GUI components, so trying to run a user form in Linux or on a machine without the non-distributable MSForms 2.0 DLL will raise a runtime error instead of showing a UI.

At design/compile time though, all the symbols remain available, so you get completion lists and signature help regardless, and the code compiles.

We could describe it as RD-VBA replacing MS-VBA as the MSForms designer and runtime host here, taking the legacy Microsoft UI framework made for Office automation out of its box and into standalone RD-VBA applications.

This is ultimately just the fallback solution, a shortcut to get something out the door faster: the goal is for the beta stage to host its own implementation of a GUI framework that can run and render anywhere: the one HexIDE built to do the same for VB6 forms and controls.

Remember MVVM in VBA? The example form’s content is dynamically generated at run-time, and RDCore runs it and its events; it works!


Alpha release?

As of this writing, RDCore is still unreleased pre-alpha. To try it today, you must clone the repository on GitHub and build and deploy the platform yourself, and then it’ll deploy an unoptimized debug build that’s intended for local development and debugging.

The roadmap is quickly becoming a long green list of checked boxes, there are now over 13k unit tests and 80% coverage, so an alpha release can’t be too far down the line. What’s holding it up?

HexIDE integration

Releasing RDCore without a LSP client integration beyond its own REPL/CLI toy application wouldn’t do it justice: there are so many things RDCore can do as a platform, that the REPL client simply cannot express, the first of which is handling a project with anything more than a single procedure scope with BASIC-style line numbers. The REPL client is a LSP demo and platform integration testing tool; it isn’t “the product”, even if, without an IDE, it’s what gives you a small glimpse of what the platform and language server can do.

There could be a small VSCode extension that implements the SDK’s RDCoreClientApp and integrates the LSP server into VSCode… but VSC is a general-purpose jack of all trades, master of none editor that makes the experience awkward when you just want your good old VBIDE with the modern-day perks.

This is where HexIDE wins: like VSCode it has its language features provided by LSP server extensions. But the Hex is for 6, as in VB6 – it is an independent project under MIT licensing, which gives a fork more rights than what we intend to leverage to ultimately make it the RDCore platform’s official IDE.

By 2027, it will be possible to import a VBA project or open a .vbp in HexIDE; analyze, test, and build it (.net exe/dll) in both CI/CD pipelines and the editor with RDCore, and compile it to native x86/x64 and extend the language beyond VBA with twinBASIC – all without leaving your IDE, and none of it requiring you to be on a Windows desktop with Office installed.

RDCore.SDK

The MIT-licensed RDCore.SDK project at the center of the platform is being extracted into its own separate repository, and will be publishing a NuGet package that RDCore platform components and extensions (including the HexIDE extension) will be referencing like any other package. The idea is to make it as easy as possible to write RDCore platform extensions.

Because the language core and runtime and platform SDK is only the beginning for the RDCore platform: unit testing will soon build on top of it, for example – but the alpha release will not wait for the platform extensions.

Pre-Release: 2.5.92.x

I know it’s been forever, trust me… I know. But life happens as they say, and here we are a whole year and over 20,000 downloads later with some movement at last in the Rubberduck repository.

Also… Rubberduck is now officially a whole 10 years old! It’s completely incredible how far we’ve pushed this project, and I’m very proud of everything we did, undid, redid. The number of times we’ve collectively crashed the VBIDE together must overflow an Integer by now!

Development on the project started late in 2014, but the website and blog only did in early 2015, with the initial release of what was then not much more than an extension of a thought experiment.

The “official” 2.5.92 release will come later once it’s been out in the wild a little as a pre-release, but the announcement will refer to this present article for what’s new.

The broken website did not like having the new tag records inserted for some reason, but the fallback links point to the right place so it’s still relatively easy to find the installer download; you’ll find the executable listed under the assets of the latest release/tag on GitHub:

VersionDirect link
v2.5.91https://github.com/rubberduck-vba/Rubberduck/releases/download/v2.5.91/Rubberduck.Setup.2.5.9.6316.exe
v2.5.92.6346https://github.com/rubberduck-vba/Rubberduck/releases/download/Prerelease-v2.5.92.6346/Rubberduck.Setup.2.5.92.6346-pre.exe

New Inspections

A handful of new inspections are being introduced once again, this time around Option Base 1 and a little pet peeve of mine with Excel-specific code.

Parameterless Cells

As you know, in the Excel library Range.Cells is a parameterized get-only property that accepts a row or column index, or both, …or neither. Except when it’s not parameterized, it’ll just return exactly the parent Range object reference, making it an entirely superfluous member call. This new Excel-specific inspection will flag these parameterless calls. A quickfix could be implemented to automatically remove these calls, but flagging them as redundant is a good first step:

Public Sub DoSomething()
    Debug.Print Sheet1.Range("A1").Cells.Address '<<< inspection result here
End Sub

UPDATE 2025-02-01: Properly implementing this inspection requires more careful consideration that make it difficult to avoid false positives in the few specific situations where a parameterless Cells call does, actually, return different references depending on what other Range members were called before – this kind of tracking isn’t quite possible with v2.x, unfortunately. So this inspection isn’t making it to release, it’s been removed already.

Inconsistent Array Base and InconsistentParamArrayBase

When Option Base 1 is specified, implicitly sized arrays begin at index 1 instead of the more typical 0. However, ParamArray arrays will always be zero-based regardless of Option Base. Similarly, explicitly qualified VBA.Array function calls will also systematically yield a zero-based array. This new inspection flags these parameters and function calls as having a base that is inconsistent with the Option Base setting of the module. There’s no fix for this one either, it’s just a hint that could potentially help detect off-by-one errors.

Option Base 1
Public Sub DoSomething()
    Dim Values As Variant    
    Values = Array(42)
    Debug.Print LBound(Values) '<~ 1 as per Option Base
    
    Values = VBA.Array(42) '<<< inspection result here
    Debug.Print LBound(Values) '<~ not 1
End Sub

Another example:

Option Base 1
Public Sub DoSomething(ParamArray Values) '<<< inspection result here
    Debug.Print LBound(Values) '<~ not 1
End Sub

Rebalanced Inspection Defaults

For a while now, something had been bugging me about the inspection types and default severities: things tended to stick to the default fallbacks over time, and the categorization of a number of inspections felt wrong and overall unbalanced. Because inspection types are for legacy reasons part of the inspection configuration, if yours isn’t the default configuration you will not see an effect until you either reset everything to defaults, or manually edit the configuration file to remove everything about inspections.

Here’s the breakdown of how things were shuffled around with inspection types:

Inspection Typev2.5.91v2.5.92
Code Quality7360
Language Opportunities2321
Naming and Conventions1928
Rubberduck Opportunities312
Total118121

..and with the default severity levels:

Default Severityv2.5.91v2.5.92
DoNotShow33
Hint1122
Suggestion2534
Warning7238
Error724
Total118121

Notably, everything around annotations is now under Rubberduck Opportunities, and default severity levels are much more sensible; the 3 inspections disabled by default remain disabled:

  • RedundantByRef mirrors ImplicitByRef; both are valid takes, just different conventions and the more explicit one was made the default, but you can reconfigure them to match your style by enabling one and disabling the other.
  • StepIsNotSpecified mirrors RedundantStepOne; again both valid stances, this time with the less noisy one as the default configuration.
  • ShadowedDeclaration was disabled for performance reasons, if I recall correctly. Or it’s something about some difficult false negative situations making it somewhat too unreliable or experimental to ship enabled by default.

This clean-up touched every single inspection, and in the process the way to localize the resources has been standardized, which should fix the annoying partly-localized labels in some inspection results.

The inspection results toolwindow gets a new disable this inspection button right next to the Fix menu, which should make it easier to disable an inspection altogether with a single click, by selecting any of its results in the grid.

The “disable inspection” command remains available as a link/button in the bottom panel, but now also as a more prominently visible button on the main toolbar. Note that there is no confirmation prompt for this action for now.

Unit Testing

This is a very, very exciting release for the unit testing feature!

Performance Enhancements

Keeping the Test Explorer UI updated with a constantly changing source collection was taking a serious toll on the UI thread, incidentally the only one available for running VBA code. Thanks to a quick PR by contributor tommy9, the explorer now leverages WPF features that improve the situation.

Projects with a lot of tests can still significantly speed up their execution by hiding the toolwindow and selecting Run all tests from the Rubberduck menu. If the main thread isn’t busy drawing a UI element, it’s free to run the instructions of a test procedure.

Moq+VBA

Seven long years after the initial pull request was opened, it’s finally merged and ready to start a little revolution in the VBA unit testing world.

There are important limitations: user code cannot be mocked after it’s been modified, and the setup of ByRef parameters may not always work correctly, but the bulk of it is close enough (and has been for a long time) to be release-worthy. Part of what took so long was that we wanted to ship the full feature working exactly as intended, but there are some serious technical roadblocks and between entirely dropping a 7 years old pull request and merging it anyway, … I’m going with the merge. Perfect is the enemy of good, they say.

What’s happening here is nothing short of pure wizardry: we’re turning arbitrary COM objects into .net ones, and then we translate configuration calls written in VBA into .net expressions that literally get compiled and invoked on the fly to call the non-generic methods offered by Moq 4.8 (getting a bit old, but we’ve never had anything like this in VBA).

In other words, unit testing with Rubberduck now empowers you with quite a large part of what only becomes possible with a real mocking framework, which this is.

The mocking API is currently documented in the wiki, but here’s the crux of it… it’s a game changer:

    'arrange
    Dim Mock As Rubberduck.ComMock
    Set Mock = Mocks.Mock("Excel.Application") 'here we create a new mock using the Excel.Application progid
    'then we configure our mock as per our needs...
    Mock.SetupWithReturns "Name", "Mocked-Excel"
    Mock.SetupWithCallback "CalculateFull", AddressOf OnAppCalculate
    Dim Mocked As Excel.Application
    Set Mocked = Mock.Object 'ComMock.Object represents the mocked object and always implements the COM interface it's mocking
    'act
    'just making sure the mock works 🙂
    Debug.Print Mocked.Name
    Mocked.CalculateFull
    'assert
    'use the ComMock.Verify method to fail the test if a method that was setup was not invoked as per the test's specifications:
    Mock.Verify "CalculateFull", Mocks.Times.AtLeastOnce


There’s only a handful of simple objects to work with, and the API is clean, unambiguous, intuitive. The example above illustrates the enormous power that’s now in your hands by mocking the Excel.Application interface.

The COM mock lets you access the proxy object to pass it around as a surrogate implementation for a dependency that would otherwise compromise the “unit” part of it being a “unit test”; it’s a class type that’s spawned into existence from its definition, for which you can setup any member call in a test. Here we make the `Name` property return a different value just because we can, and when the macro invokes the `CalculateFull` method against this mock instance, rather than awaiting a full application-level compute you can simply verify that the method was invoked, or run a callback procedure (elegantly passing it to the API with the `AddressOf` operator) that can be parameterized if you need to accordingly alter some state.

I’ll write a whole article dedicated to this API soon, but together with the Fakes API, this really takes the Rubberduck unit testing feature to the next level (code coverage would be the next one). As long as you can identify the dependencies in your code (and come up with a way to inject them from the caller), you can now write a test that abstracts them away.

Other Tweaks

The Unassigned variable usage inspection will now honor the out prefix by convention, meaning it will no longer issue false positives when a variable is assigned by another procedure via a ByRef argument, as long as it’s named accordingly with an “out” prefix.

The About box will now mention “Win11” for Windows builds above 22000, even though the major version says 10. This was likely related to Rubberduck using an ancient .net Framework API to retrieve this OS version information.

Renaming an enum member in a way that requires the constant to be qualified, will now correctly use the enum type name rather than the name of the containing module, which was an edge case that could cause headaches and break the code.


What about v3?

If things had gone smoothly, you would know by now. They obviously haven’t, and not much has moved on that front since last May or so. I’m not abandoning the project, but building an entire editor client and a language server is honestly much more than I can chew at the moment, for multiple reasons… twinBASIC is coming, and while a commercial offering, it’s a VB6/VBA language server and compiler, and it reckons the whole editor part is already a solved problem. Rubberduck 3.0 was going to reinvent that wheel, which would have been a huge distraction.

So RD3 is going to be re-scoped a bit: the planned VBIDE integration / add-in part remains of course, but instead of making an entire editor from scratch, we’ll integrate into an existing, modern one that’s already an extensible LSP (Language Server Protocol) client, much like Visual Studio Code (but no, it’s not going to be VS Code). This instantly knocks off (well, removes outright) a gigantic, milestone.

With this v2.x release, the backlog of pending pull requests is finally cleared. 950+ open issues remain in the Rubberduck repository, but very few are actual unresolved bugs or realistically implementable feature ideas. Expect additional pull requests for missing translations, minor fixes and UI/UX enhancements, but the 2.x life cycle is now essentially completed… and it was about time: the technology used for building Rubberduck has evolved a lot in the last decade, with much of it either deprecated, or on the brink of falling out of official long-term support from Microsoft, which is making it harder and harder to easily and successfully build Rubberduck, hoping with fingers crossed that nothing breaks at every dependency update.

I bet VBA will outlive .NET Framework 4.8.1 LTS, which is non-ironically very funny to me… but for Rubberduck to keep moving forward, making the move from the now ancient .NET Framework over to current technology is inevitable – and development on v3 has already knocked that critical milestone, too.


Afterthoughts

I want to stop spreading too thin and actually finish the website now (as you can see, …the thing is falling apart!), and then with Rubberduck 2.x in its current state (more or less), I can finally draw a line and know exactly what v3.0 needs to be. My priorities have shifted quite much since 2021 for many reasons, and Rubberduck and the blog and my online presence in general basically had to hit the brakes. Life happens… doesn’t it.

I’ll always write code as a hobby, as I’ve done ever since I found out about programming. But I also played (badly) some guitar as a teen, and carried a few harmonicas with me to the Microsoft MVP Global Summit in 2018 and 2019 (the two I attended before they went virtual during the pandemic); these days I feel like I could write about the music theory I’ve learned since, or perhaps how to configure the reed gaps on a 10-hole diatonic, standard Richter-tuned harmonica to make it easy to play the hidden overblow notes. I haven’t been paid to maintain a line of VBA code for a very long time now, and I’ll always love it but I can’t say it’s my GoTo language anymore, but it’s all right – Rubberduck isn’t written in VBA anyway. But it’s becoming hard to find inspiration to write about some random VBA things, especially since I’ve stopped participating on Stack Overflow. I stopped, because they basically made an agreement with OpenAI and essentially stole (with a unilateral move in violation of the agreed-upon license) the volunteer work and helpful knowledge-dumps of tens of thousands of people (myself included) to train a… chatbot and make billions off of these people’s work, and then take art and reduce it a prompt.

I mean I get it, it’s fun. But it’s a very steep cost just for something that’s just innocent fun. This is the first and last AI-generated image you’ll ever see from me on this blog.

Perhaps I’m just turning into an old man screaming at a cloud (brought to you by AWS!), but… look, it doesn’t matter that you can prompt a machine to play some harmonica for you: you’ll never, ever, ever experience any AI-generated slop like when you’re actually playing the blues and counting that I-IV-V progression over these 12 bars. Same on guitar. Same on drums. Same with code.

State as a Service

Say you have a worksheet that contains a table with various settings, or options to run some macro with. Whatever the macro does, whatever it uses these values for, it must somehow solve this problem: how to get these values out of the worksheet and into the program?

We have a ListObject to play with, and since the table is in a specific worksheet, that’s the worksheet module we’re going to be editing. Since the very existence of that table matters for the rest of whatever this macro ends up doing, we’re going to make it clear it’s not an accident by making it a property of the worksheet (class) module – something like this:

Private Const TableName As String = "Table1"

Public Property Get SettingsTable() As ListObject
  Static Value As ListObject
  If Value Is Nothing Then
    On Error Resume Next
      Set Value = Me.ListObjects(TableName)
    On Error GoTo 0
  End If
  Set SettingsTable = Value
End Property

Property Get procedures usually do not raise an error – here if the table doesn’t exist the property will return Nothing, which should cause the calling code to blow up with error 91, and this would be reasonably expected behavior in this case. The Static local stands in for a module-scoped variable declaration that would be needed if the property had a setter procedure; because it’s only needed in one place, we can declare it locally and retain the module-scoped behavior with the Static keyword.

“Static” in VBA basically means “shared”, and “Shared” in VB.NET means what “static” means in essentially every other language with such semantics. Static as a scope modifier in .NET means a member belongs to the type (as opposed to an instance of the type in question), but in Classic-VB it is used for declaring local variables that retain their value between procedure calls, and if used as a modifier at procedure level it makes all locals behave as such… which [very likely] isn’t a good idea.

So we have now given ourselves access to the table, and we can just do Sheet1.SettingsTable to access it from anywhere.

But what if we don’t want that? If we know the settings each have a unique name and have a value that might be a String, a Double, a Date, or a Boolean. The table might just as well be empty for now, anyway.

If we don’t formalize access to the settings, then every place that needs them might be doing things differently! Imagine the chaos if sometimes an option is retrieved with Application.VLookup, elsewhere with a loop over SettingsTable.Rows, and then another one could be getting the value with an offset from the result of SettingsTable.DataBodyRange.Find, and there’s another couple of different but not always equivalently dangerous or misguided ways to go about retrieving a value from that table, and there’s no need to have all of them around.

We could write a function that accepts the name of a setting and returns a Variant holding the associated value if it exists, but what if we need to get the value multiple times, we’re going to read it off the worksheet every time?

Writing to a Range is perhaps the most expensive thing a macro can do, but reading anything from one comes close. In fact, a macro that performs well is usually a macro that limits its interactions with worksheets and the …entire Excel object model.

What we want is a method that iterates the table rows once, yielding an OptionValue class instance for each setting value. So we add this new class module and define an OptionValue class with a Name (String) and a Value (Variant) property.

But then if we call that method every time we want to get a setting value, things are going to be much worse than if we just used Application.VLookup every time, so what gives?

Grabbing every row of that table and turning them into as many OptionValue instances is an action, it wants to be a verb. Things we do with any kind of state are very often well described with a method’s name that starts with a verb, and that’s great already (especially if the rest of the name is actually somewhat descriptive), but a free-floating verb is up for grabs by anyone.

So we’re going to encapsulate it by making that method a member of a class for which SettingsService might be a good name: it’s a service that has the means to abstract away the worksheet and only expose OptionValue objects, and with it the rest of the code no longer needs to deal with the nitty-gritty details of how these objects come into existence, or how long they’ve been around.

Because we can write this class in such a way that we read the settings once from the worksheet (say, in the Initialize handler of the class itself), cache them in a private keyed collection (or private dictionary), and as long as our instance is alive we can return the cached option values whenever someone asks for them, and then they’ll be getting them without needing to hit the worksheet.

By adding an indexed property, we can even have a default member that makes sense, and the rest of the code can read its configuration like this:

Dim Settings As SettingsService
Set Settings = New SettingsService

If Settings("SomeSetting") Then
  Debug.Print "SomeSetting is ON"
Else
  Debug.Print "SomeSetting is OFF"
End If

Settings(“SomeSetting”) stands in for so many things here, all of which would distract from what this macro is supposed to be doing, however working with Variant like this is annoying, and the use of default members is abstracting away mechanics that we’d usually rather be explicit about, so we should instead expose typed methods, so we know (we/us, but also the compiler and Rubberduck here) what actual types and members we’re dealing with:

Dim Settings As SettingsService
Set Settings = New SettingsService

If Settings.GetBoolean("SomeSetting") Then
  Debug.Print "SomeSetting is ON"
Else
  Debug.Print "SomeSetting is OFF"
End If

Where GetBoolean being a method/function rather than a property should make us feel much better about throwing errors: if the setting doesn’t exist, we blow up. If the setting exists but its Variant subtype isn’t Boolean then we probably want to blow up rather than return gibberish. If it exists and it’s of the expected data type, we return the setting value, converted to an actual Boolean.

That means SettingsService also needs GetDate, GetDouble (and maybe GetInteger), and of course GetString, leaving the Variant values completely encapsulated in the service: callers don’t need to care about any of that, and that’s neat.


There is no Worksheet

The only thing that needs to do anything with SettingsSheet is the SettingsService. Nothing else needs to access it for any reason whatsoever, because we have a service that fully abstracts it away, so it might as well not be there.

And the macro should still work, assuming it knows how to deal with and recover from a missing setting value.

Settings could be moved to a flat file, another workbook, or a database, and only one method would need to change: the one that’s reading and caching the settings from the worksheet, would instead be connecting and querying a database – and would still only need to hit it once.

And none of anything else would need to change, because it’s all completely yielding that responsibility to this service.

Compare to what it would be like to change inline VLookups and Range.Find calls (wherever they are) to read from another source, and you can quickly see the benefits of having sane abstraction levels.

Code that desperately wants to control how everything is done at the lowest level of detail, is tedious and heavy. It’s hard to tell what the role of such a procedure is, because too many things are going on and the signal gets drowned in noise.

There doesn’t need to be a service class, or even an OptionValue class: any distinct procedure scope that’s clearly responsible for retrieving a valid setting value is a good step forward. But moving the state into an object makes it easier to control its lifetime, and by encapsulating behavior we clean up the calling modules the same way extracting procedure out of a larger scope cleans up that larger scope. It reduces the cognitive load and complexity by moving away code that’s concerned with the peripherals of any given macro’s purpose, and thus increases the cohesion of the macro’s module because things that aren’t directly related to what the macro is specifically responsible for, are simply elsewhere, where they belong.

Typed Arrays Must Die

Haha. Yeah, no, I mean, really. Let me explain.

Variant is Awesome

A lot has been written about why Variant should be avoided whenever a more specific data type can be used instead. Indeed it makes little sense to use a Variant (explicit or not) when you could be working with a Long integer, a Date, or a String instead. That’s true for all intrinsic data types.

For object types, using a Variant in place of a specific class interface makes any call against it inherently late-bound, meaning it can only be resolved at run-time, because the actual class/interface type is not known at compile-time… and that’s why the VBE won’t be showing any member/completion list or parameter info for anything this Variant could be used for. Definitely no good, besides Object is a better option for explicit late binding.

Wait I thought you said it was awesome?

Totally. See, its nature makes it the perfect data type to return for any Excel UDF (user-defined function): when the function succeeds, it can return a different variant subtype than when the function fails, so an error-handling subroutine in a UDF can make the function return an actual Variant/Error value to the worksheet and yes, that’s awesome.

But wait, there’s more!

Arrays. Variant is awesome for arrays – because typed arrays must die.


Ok. What is a Typed Array?

It’s one of those language quirks that make VBA so… I’ll say adorable.

By “typed” I mean an array that’s declared as such, so any old “array of strings” would be it:

Option Explicit
Private DynamicallySizedArray() As String
Private FixedSizeArray(1 To 10) As String

The distinction between fixed and dynamically sized arrays is important in the language specifications, but let’s just take note of the fact that the syntax asks that we specify the array bounds right after the identifier name.

This is important, because As String(10) is a syntax error, not an array.

Same when you declare a parameter:

Private Sub DoSomething(ByRef Values() As String)
End Sub

Note the explicit ByRef modifier: arrays can only ever be passed by reference. Declaring this parameter ByVal would be a compile-time error, and this has interesting implications once we start considering whether and how these arrays can get assigned to.

Because the value parameter of a property is always passed by value (even if it says ByRef!), typed arrays are immediately problematic with property definitions:

Public Property Let SomeValues([ByVal] Values() As String)
End Property

And what if you wanted to return a typed array? It would look something like this:

Public Function GetSomeValues() As String()
End Function

Note that specifying array bounds or string length would be illegal here, and that because this is a member signature the sets of parentheses right after the identifier name denotes the parameter list and has to be included even when there are no parameters to declare.

I mean it’s for a lot of good and valid reasons, but the bottom line is, the syntax is inconsistent and confusing… and that’s just how bad it is at compile time. It gets worse.

ReDim

I need to talk about ReDim while I’m here. Somewhat recently, I was working on the RD3 type system and went on to implement ReDim for the array types, and I was surprised (not really 😅) at how much VBA defers to run-time. You’ll get a compile error for trying to ReDim a With block variable or a ParamArray parameter array value, but all these are run-time errors with the Preserve modifier specified:

  • Attempting to change the number of dimensions of the array
  • Attempting to change the lower boundary of the array
  • Attempting to change the upper boundary of a dimension that isn’t the last dimension of the array

Can’t assign to array

A fixed-size array can be assigned to if the value has the same number of dimensions, that the dimensions are the same size, and all the values are the default value for the declared type. A resizable array can be assigned to if… it has no dimensions.

This context-dependent behavior is something that either occupies mental space, or causes problems that the compiler will not detect until it’s too late (although, RD3 diagnostics might change that).


Variant/Array

Mental space is important when programming, because there’s a lot of things that need to be taken into consideration, and we don’t need the language itself to insert its own complexities in our code.

Variant can hold anything, including arrays. Simply put, we declare things As Variant and use a pluralized identifier name: ByVal Things As Variant.

Suddenly, working with arrays in VBA becomes much simpler – because a Variant/Array does behave exactly as one would expect: it’s all pointers underneath, so the difference is subtle, but by adding just one “layer” of pointer indirection by “hiding” our array behind a Variant, we can do everything we can otherwise do with an array, and we can write function signatures without thinking twice about where to put the parentheses.

Public Function GetSomeValues() As String()
End Function

Becomes:

Public Function GetSomeValues() As Variant
End Function

Because it’s behind a Variant, we’re now free to pass arrays around as we please – of course no array data actually goes anywhere, it’s the pointers we’re moving around, in a way that’s quite similar to how objects (pointers) are passed around.

So when you pass a Variant by value, you’re passing a copy of a pointer that says “your array is over here”. Exactly like an object reference, in fact.

Without the Variant wrapping around the array, when we try to pass it by value it’s the entire literal array we’d be copying, and VBA refuses to do this, and forces arrays to be passed by reference.

In other words shoving arrays into a Variant makes them get passed as references regardless of whether we’re doing this ByRef or ByVal, which is exactly what VBA wants.

Private Sub DoSomething(ByVal Values As Variant)
End Sub

It’s also what we want, because our mental concept of passing an array to a procedure works like this too: if we simply accept the fact that it’s a pointer that we’re actually passing, then the rules around ByRef/ByVal remain relatively simple and there is no need to worry about code that compiles but might blow up at run-time, because when we assign the array pointer we’re assigning to a Variant and not an array, so there is no restriction here, it “just works”.


Conclusions

Arrays are extremely useful in VBA, but using them at the abstraction level they were originally intended to be used at is full of traps and caveats that make them difficult to work with. By working with Variant instead, we’re still dealing with arrays, but we’re no longer dealing with the restrictions that are inherent to array variables.

So there, it’s not that typed arrays must die, it’s just that arrays in VBA are much less irritating when VBA doesn’t know it’s looking at one.

Rubberduck 2.5.91

Release 2.5.9 came with an unfortunate bug where an exception in the version check feature would blow everything up and fail the startup of the add-in. It was of course quickly fixed in a subsequent pre-release build, and meanwhile other enhancements have been merged, so here we are with a new release that fixes the error handling of the version check service and correctly parses empty instructions.

You can find the GitHub tag/release here: https://github.com/rubberduck-vba/Rubberduck/releases/tag/v2.5.91

Parser Fixes

One parser bug was reported through an interesting edge case that was reminiscent of good old ThunderCode, looking something like this:

If condition Then:::::::::::DoSomething

It is of course grammatically correct, which means Rubberduck should be able to ingest it. Thanks to yet another contribution by @MDoerner this version correctly parses such empty instructions, which feels like it opens up an opportunity for an inspection that flags them, and a quick fix to remove them.

This parser tweak is definitely making it to RD3!

Other Hacktoberfest contributions include:

  • Correctly replacing any Exit Sub with Exit Function statements when converting a Sub procedure into a Function.
  • Fixing an unsafe conversion from Char to Int that was causing a crash in PowerPoint 2019.
  • COM Interop / RCW handling solidifying shutdown by taking measures to ensure reading or writing a CommandBarButton’s caption or enabled state does not happen during shutdown / after the COM objects have been destroyed.
  • Allowing multiple line continuations before the dot operator in member access expressions.

Version Check Bug

That one is purely on me rushing to release without realizing that the web API call wasn’t surrounded by a try/catch block, and since it was an asynchronous operation… any uncaught exception would take down the host process, which is utterly uncool.

The fix was, obviously, to handle errors around the network I/O. Of course a simple work-around was to simply disable the version check on startup, but unless you’re familiar with Rubberduck configurations, tweaking these without being able to load Rubberduck in the VBE to bring up the settings dialog wasn’t exactly a user-friendly experience.

Note: the web API that refreshes the website and responds to version check requests is still down! I’ve manually inserted the database records for this release, so it shows up on the website.

Extract Method

There’s an off-by-one selection glitch with this refactoring that’s also getting fixed in this release, thanks to a timely contribution by @tommy9. This selection glitch was disabling the refactoring command when it shouldn’t be disabled, making it more difficult to use than it should be.


RD3 Progress

Meanwhile I’ve been giving quite a bit of love to v3, working on the new settings model and UI. I needed to get settings out of the way early on, because everything wants to access the configuration settings, including the locations of the server executables: I got to a point where hard-coding these was no longer practical, and since debug builds don’t generate the assemblies in a single “install folder”, being able to point them to local debug builds was a good excuse to get the settings model under way.

Data Driven UI

Historically in Rubberduck, adding a new setting meant adding a property to one of the setting groups, crafting a dedicated UI section for it in the settings UI, and then editing a .XML configuration file to define the defaults. All this work means adding a new setting is a whole undertaking in its own right, and that’s essentially a deterrent to liberally sprinkling configurable options any time we come across a decision and go “hey it would be nice if this was configurable!”.

RD3 treats settings at a different abstraction level: instead of having a “GeneralSettings” class with a property for each setting, we go meta and now GeneralSettings is becoming a settings group, and the individual settings are becoming record type instances, where each setting has a name, a description, a data type, default and current values, and additional metadata to identify hidden settings that should not appear in the dialog (e.g. the flag indicating whether Rubberduck prompted to import legacy indenter settings), or settings that are recommended to keep their default value (e.g. server executables’ locations).

Because there are settings of pretty much every data type, templating the settings UI before everything else means everything else gets to use already-templated controls, resulting in a consistent UI that looks and behaves the same across the entire application.

The still-WIP RD3 settings dialog showing some language client settings.

Unless it’s for a new data type that wasn’t already templated, adding a new setting in RD3 means adding a new record class for the setting, defining its default value and metadata in the same place, and then adding an instance to an existing setting group – and that’s all there is to it… well, excluding the associated string resources, each of which will need translations (most of this doesn’t exist in RD2).

Language Server Protocol

The VBE addin itself is implementing a LSP client, but its server is going to be the Rubberduck Editor process: the editor will be notifying the addin whenever it needs to synchronize source files into the VBE. In other words the editor is both a server and a client, depending on whether it’s talking to the addin or to the language server.

At this stage the addin is initializing a language client when it launches the editor, and as soon as the editor completes the LSP initialization handshake with the addin, it starts the language server process and initializes its own language client.

While both the addin and the editor implement LSP initialization, the two processes have vastly different responsibilities: the addin doesn’t deal with documents, and doesn’t care about semantic tokens or diagnostics: its primary responsibility is to get the code files into and out of the hosted VBA project, and provide an insulation layer between COM and the rest of Rubberduck. The editor however, is going to implement most of the LSP-defined features.

All Json-RPC communications are happening across process standard I/O for now, but this isn’t viable because when the editor talks to the addin, it needs to do so in a channel that’s separate from when it talks to the language server (things could get really funky otherwise). Named pipes will solve this, but in order to keep things moving forward I’ve opted to leave the addin behind (it’s almost completed!) and build the editor as a standalone application (which greatly simplifies debugging). We’ll get the named pipes working to implement the synchronization with the VBE, but the VBE is otherwise not needed for now, so standard I/O it is, and we’ll look into named pipes when they’re needed.

Project Templates

As I was piecing together everything I needed to serialize the .rdproj file and initialize a workspace, I came across the need to create a blank project that references the VBA library… and as I was hard-coding this sensible default I realized I could just copy an existing project file instead of hard-coding it, and things snowballed from there a bit, and so RD3 will come with a default empty project that references the VBA library, but also templates for various Microsoft Office hosts, and then why not have a template for a host-agnostic MVP project!

In Visual Studio when you start a new ASP.NET MVC project, you get a bare-bones working application that’s already somewhat organized, with the source files under specialized folders; the RD3 Rubberduck Editor will be able to do the same.

Creating a new project in the Rubberduck Editor lets you pick a project template and not start from scratch every time.
Templates are just special folders with folders and code files; all the folders under the Templates folder are considered project templates!

Project/workspace files solve the problem of “remembering” which files were opened, so it should be easy to restore them – the same feature is how template projects will open in the editor with the README.md document tab opened.

Folders

Making VBA code as git-friendly as possible is, I believe, one of the best ways to ensure VBA lives on; being able to organize a VBA project into actual folders is an important part of this, but the lack of namespaces makes it a challenge… that’s solved with the .rdproj file. Indeed, it makes a way to ensure no RD3 project can have two source files that result in importing a module into the VBE… and then overwriting it when importing the next source file.

Taking RD2 @Folder annotations as a baseline, the relationship between modules and folders is reversed in RD3: RD2 folders were just a representation of where modules fit in a tree, folders could only exist if there was a module to define it. Now that we’re moving to the file system, a project can have empty folders, and modules don’t (can’t) have a say on what folder they live under.

This poses a compatibility problem with RD2 projects, that we’re going to address by acknowledging the RD2 annotations and migrating the project to the RD3 paradigm by creating actual folders under the project workspace, and moving the source files there; the @Folder annotation comments should then be removed. This functionality is not implemented yet despite the “new project” command being completed, because it’s the language server that will tell the editor about this when it starts issuing diagnostics… and we’re not there yet.

Forward

Rubberduck 3.0 can now create a new project, load an existing one, and save workspace changes to the file system. Loading a workspace/project loads all the file contents in memory in the editor process, and that’s where we’re at: the next step is to come up with a UI to explore the files in a workspace and pick one or more to actually open in an editor tab. This workspace explorer UI is similar to the VBE’s project explorer in that it knows everything there is to know about what files are in a project, and what content is in each file… but unlike the RD2 code explorer it knows nothing about any semantics, so it doesn’t/can’t drill down to member level – it’s really all about files and folders.

Once the Workspace Explorer UI is done, I’ll be focusing on the editor shell UI again, this time to work on the docking panels and document tab system: then we’ll have the infrastructure in place to add all the toolwindows we need, and document tabs that – finally – actually display file contents… whether that’s a markdown document, a plain text file, or a VBA source file.

Opening a workspace has implications with the LSP server: when you open a project in the editor, it sends everything it knows about these files to the server process through Json-RPC communications – the server process never accesses the files directly; files “belong to” the editor process.

To be continued…

Declaring and Using Variables in VBA

Among the very first language keywords one comes across when learning VBA, is the Dim keyword; declaring and using variables is easily the first step one takes on their journey away from the macro recorder.

About Scopes

Before we can really understand what variables do and what they’re useful for, we need to have a minimal grasp of the concept of scoping. When you record a macro, the executable instructions get written for you inside a procedure scope that’s delimited with Sub and End Sub tokens (tokens are the grammatical elements of the language, not necessarily single keywords), with the identifier name of the macro after the Sub keyword:

Sub DoSomething()
    ' executable code goes here
End Sub

Exactly none of the above code is executable, but compiling it creates an entry point that the VBA runtime can invoke and execute, because the procedure is implicitly public and as such, can be accessed from outside the “Module1” module it exists in (with or without Option Private Module). In other words the above code could tell us explicitly what the scope of the DoSomething procedure is, using the Public keyword before the Sub token:

Public Sub DoSomething()
    ' executable code goes here
End Sub

If we used Private instead, then Excel (or whatever the host application is) could not “see” it, so you would no longer find DoSomething in the list of available macros, and other modules in the same VBA project couldn’t “see” or invoke it either; a private procedure is only callable from other procedures in the same module.

Standard modules are themselves public, so you can refer to them from any other module in your project, and invoke their public members using the member access operator, the dot:

Public Sub DoStuff()
   Module1.DoSomething
End Sub

Because public members of public modules become part of a global namespace, the public members can be referred to without an explicit qualifier:

Public Sub DoStuff()
    DoSomething
End Sub

While convenient to type, it also somewhat obscures exactly what code is being invoked: without an IDE and a “navigate to definition” command, it would be pretty hard to know where that other procedure is located.

The global namespace contains not only the public identifiers from your VBA project, but also all the public identifiers from every referenced library, and they don’t need to be qualified either so that’s how you can invoke the VBA.Interaction.MsgBox function without qualifying with the library or module it’s defined in. If you write your own MsgBox function, every unqualified MsgBox call in that project is now invoking that new custom function, because VBA always prioritizes the host VBA project’s own type library over the referenced ones (every VBA project references the VBA standard library and the type library that defines the COM extension and automation model for the host application).

But that’s all going outward from a module: within a module, there are two levels of scoping: module level members can be accessed from anywhere in the module, and procedure level declarations can be accessed from anywhere inside that procedure.

Module-level declarations use Public and Private modifiers, and procedure-level ones use the Dim keyword. Dim is legal at module level too, but because Private and Public are only legal at module level (you can’t use them for procedure scope / “local” declarations), Rubberduck encourages you to use Dim for locals only.

For example a variable declared in a conditional block is allocated when the stack frame is entered regardless of the state when the condition gets evaluated, and a variable declared inside a loop body is the same variable outside that loop, and for every iteration of that loop as well: there is no “block scope” in VBA.

Non-Executable Statements

Procedures don’t only contain executable instructions: Dim statements, like statements with Private and Public modifiers, are declarative and do not do anything. You cannot place a debugger breakpoint (F9) on such statements, either. This is important to keep in mind: the smallest scope in VBA is the procedure scope, and it includes the parameters and all the local declarations of that procedure – regardless of where in the procedure body they’re declared at, so the reason to declare variables as you need them has more to do with reducing mental load and making it easier to later extract a method by moving a chunk of code into another procedure scope. Declaring all locals at the top of a procedure often results in unused variables dangling, because of the constant up-and-down, back-and-forth scrolling that inevitably happens when a procedure eventually grows; the further a variable is out of its context, the more it becomes a liability.

Const statements (to declare constant values) are also legal in local/procedure scope, and they’re identically non-executable; the same applies to Static declarations (variables that retain their value between invocations).

ReDim statements however are executable, even though they also count as a compile-time declaration – but they don’t count as a duplicate declaration, so the presence of ReDim doesn’t really justify skipping an initial Dim declaration.

Explicitness as an Option

Not only access modifiers can be implicit in VBA; the language lets you define a Variant variable on the fly, without a prior explicit declaration. If this behavior is practical for getting the job done and will indeed work perfectly fine, it’s also unnecessarily putting you at risk of typos that will only become a problem at run-time, if you’re lucky close enough to the source of the problem to hunt down and debug. By specifying Option Explicit at the top of every module, the compiler will treat implicit declarations as compile-time errors, telling you about the problem before it even becomes one.

Option Explicit has its limits though, and won’t protect you from typos in late-bound member calls, where invoking a member that doesn’t exist on a given object throws error 438 at run-time.

When to Declare a Variable

There are many reasons to declare a variable, but if you’re cleaning up macro recorder code the first thing you’ll want to do is to remove the dependency on Selection and qualify Range and Cells member calls with a proper Worksheet object.

For example before might look like this:

Sub Macro1
    Range("A10") = 42
    Sheet2.Activate
    Range("B10") = 42
End Sub

And after might look like this:

Public Sub Macro1()
    Dim Sheet As Worksheet
    Set Sheet = ActiveSheet
    Sheet.Range("A10") = 42
    Sheet2.Activate
    Sheet.Range("B10") = 42
End Sub

The two procedures do exactly the same thing, but only one of them is doing it reliably. If the Sheet2 worksheet is already active, then there’s no difference and both versions produce identical output. Otherwise, one of them writes to whatever the ActiveSheet is, activates Sheet2, and then writes to that sheet.

There’s a notion of state in the first snippet that adds to the number of things you need to track and think about in order to understand what’s going on. Using variables, exactly what sheet is active at any point during execution has no impact whatsoever on the second snippet, beyond the initial assignment.

It’s that (global) state that’s behind erratic behavior such as code working differently when you leave it alone than when you step through – especially when loops start getting involved. Managing that global state makes everything harder than necessary.

Keep your state close, and your ducky closer, they say.

Set: With or Without?

Not being explicit can make the code read ambiguously, especially when you consider that objects in VBA can have default members. In the above snippets, the value 42 reads like it’s assigned to… the object that’s returned by the Range property getter of the Worksheet class. And that’s weird, because normally you would assign to a property of an object, not the object itself. VBA understands what it needs to do here, because the Range class says “I have a default member!” and that default member is implemented in such a way that giving it the value 42 does exactly the same as if the Range.Value member was being invoked explicitly. Because that behavior is an implementation detail, it means the only way to know is to read its documentation.

The Set keyword modifies an assignment instruction and says “we’re assigning an object reference”, so VBA doesn’t try to check if there’s a default member on the left-hand side of the assignment operator, and the compiler expects an object reference on the right-hand side, …and then only throws at run-time when that isn’t the case – but because this information is all statically available at compile-time, Rubberduck can warn about such suspicious assignments.

So to assign a variable that holds a reference to a Range object, we must use the Set keyword. To assign a variable that holds the value of a Range object, we must not use the Set keyword. Declaring an explicit data type for every variable (meaning not only declaring things, but also typing them) helps prevent very preventable bugs and subtle issues that can be hard to debug.

As SomethingExplicit

Whether Public or Private, whether local or global, most variables are better off with a specific data type using an As clause:

  • Dim IsSomething
  • Dim SomeNumber As Long
  • Dim SomeAmount As Currency
  • Dim SomeValue As Double
  • Dim SomeDateTime As Date
  • Dim SomeText As String
  • Dim SomeSheet As Worksheets
  • Dim SomeCell As Range

Using an explicit data/class/interface type, especially with objects, helps keep things early-bound, meaning both the compiler and static code analysis tools (like Rubberduck) can better tell what’s going on before the code actually gets to run.

We can often chain member calls; the Worksheets collection’s indexer necessarily yields a Worksheet object, no?

Public Sub Macro1()
    ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = 42
End Sub

If you manually type this instruction, you’ll notice something awkward that should be unexpected when you type the dot operator after Worksheets(“Sheet1”), because the property returns an Object interface… which tells VBA it has members that can be invoked, but leaves no compile-time clue about any of them. That’s why the Range member call is late-bound and only resolved at run-time, and because the compiler has no idea what the members are until the code is running, it cannot populate the completion list with the members of Worksheet, and will merrily compile and attempt to invoke a Range member.

By breaking the chain and declaring variables, we restore compile-time validations:

Public Sub Macro1()
    Dim Sheet As Worksheet
    Set Sheet = ActiveWorkbook.Worksheets("Sheet2")
    Sheet.Range("A1").Value = 42
End Sub

When NOT to Declare Variables

Variables are so nice, sometimes we declare them even when we don’t need them. There are many valid reasons to use a variable, including abstracting the result of an expression behind its value. Assuming every variable is assigned and referenced somewhere, there are still certain variables that are always redundant!

Objects are sneaky little things… not only can they have a default member that gets implicitly invoked, they can also have a default instance that lives in the global scope and is always named after the class it’s an instance of.

Declaring a local variable to hold a copy of a reference to an object that’s already globally accessible, is always redundant! Document modules (in Excel that’s ThisWorkbook and the Worksheet modules) and UserForms always have such a default instance:

Public Sub Macro1()
    Dim WB As Workbook
    Set WB = ThisWorkbook 'redundant and obscures intent!
    Dim Sheet As Worksheet
    Set Sheet = Sheet1 'redundant, just use Sheet1 directly!
End Sub

Sprinkle Generously

Variables are a simple but powerful tool in your arsenal. Using them enhances the abstraction level of your code, practices your brain to stop and think about naming things, can help prevent binding errors and remove implicit late-binding / keep your code entirely visible to the compiler and Rubberduck. Used wisely, variables can make a huge difference between messy and redundant macro-recorder code and squeaky-clean, professionally-written VBA code.

Lightweight MVVM in VBA

A little while ago already, I went and explored dynamic UI with MSForms in VBA through a lens tinted with Windows Presentation Foundation (WPF) concepts, and ended up implementing a working prototype Model-View-ViewModel (MVVM) framework for VBA… across a hundred and some modules covering everything from property and command bindings to input and model validation. I’m still planning to build an actual COM library for it one day – for now I’m entirely focused on everything around Rubberduck3.

Although… the last month or so has actually been mostly about publishing the new website and setting up the Ko-fi shop: the new website is not without issues (search links are broken, for one), but the source code ownership has been transferred to the rubberduck-vba organization on GitHub and I’m satisfied enough with it to move on.

But then there’s operating the shop. When an order comes in, there’s a worksheet (duh!) with a Sales table where I enter the invoice line items sold using a Stock Keeping Unit (SKU) code that identifies each item sold; the Inventory table picks up the sale and calculates a new Available to Sell figure.

But tracking items sold isn’t the whole picture: an Invoice table tracks the actual totals including the shipping charges and actual shipping costs (currently 24% underwater, but I’ve since adjusted the shipping charges to better reflect reality), computing the Cost of Goods Sold, and ultimately a profit margin.

So for each invoice, I know I need:

  • Invoice number and date
  • Billing/shipping information (name, address, etc.)
  • The number of units sold per SKU, with the amount paid by the customer
  • The shipping charge paid by the customer

And then I manually prepare the invoice document. Such a waste of time, right? Of course I couldn’t leave it at that – all I needed was a UserForm to enter all that, and a command that would update the merchandise planning workbook and prepare the invoice document for me.

Thing is, I wanted that form to use property bindings and some extent of MVVM, but I wasn’t going to import the 100+ modules of the old MVVM prototype code. So instead, I made a “lite” version.

The accompanying code for this article is in the Rubberduck Examples repository.

Property Bindings

Bindings and the propagation of property value changes are the core mechanics that make MVVM work, and we don’t need dozens of classes for that.

We do need INotifyPropertyChanged and IHandlePropertyChanged interfaces:

Option Explicit
Public Sub OnPropertyChanged(ByVal Source As Object, ByVal Name As String)
End Sub
Public Sub RegisterHandler(ByVal Handler As IHandlePropertyChanged)
End Sub
Option Explicit
Public Sub OnPropertyChanged(ByVal Source As Object, ByVal Name As String)
End Sub

These interfaces are important, because the bindings need to handle property changed events; the View Model needs to invoke the registered callbacks. This is used in place of actual events, because interfaces in VBA don’t expose events, and we want an abstraction around property changes, so that everything that needs to notify about property changes can do so in a standardized way.

The IHandlePropertyChanged interface is to be implemented by property binding classes, such as this TextBoxValueBinding class:

Option Explicit
Implements IHandlePropertyChanged
Private WithEvents UI As MSForms.TextBox

Private Type TBinding
    Source As Object
    SourceProperty As String
End Type

Private This As TBinding

Public Sub Initialize(ByVal Control As MSForms.TextBox, ByVal Source As Object, ByVal SourceProperty As String)
    Set UI = Control
    Set This.Source = Source
    This.SourceProperty = SourceProperty
    If TypeOf Source Is INotifyPropertyChanged Then RegisterPropertyChanges Source
End Sub

Private Sub RegisterPropertyChanges(ByVal Source As INotifyPropertyChanged)
    Source.RegisterHandler Me
End Sub

Private Sub IHandlePropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal Name As String)
    If Source Is This.Source And Name = This.SourceProperty Then
        UI.Text = VBA.Interaction.CallByName(This.Source, This.SourceProperty, VbGet)
    End If
End Sub

Private Sub UI_Change()
    VBA.Interaction.CallByName This.Source, This.SourceProperty, VbLet, UI.Value
End Sub

A binding has a source and a target object and property; the source is a ViewModel object, and the target is a MSForms control, in this case a TextBox. The binding must handle the control’s events to update the source whenever the value of the target changes. In this limited version we’re only going to handle the Change event, but if we wanted we could go further and handle KeyDown here to implement input validation. Some error handling wouldn’t hurt, either.

Because everything that involves notifying about property changes is standardized through interfaces, we can make a PropertyChangeNotification helper class to register the handlers:

Option Explicit
Private Handlers As VBA.Collection

Public Sub AddHandler(ByVal Handler As IHandlePropertyChanged)
    Handlers.Add Handler
End Sub

Public Sub Notify(ByVal Source As Object, ByVal Name As String)
    Dim Handler As IHandlePropertyChanged
    For Each Handler In Handlers
        Handler.OnPropertyChanged Source, Name
    Next
End Sub

Private Sub Class_Initialize()
    Set Handlers = New VBA.Collection
End Sub

This class is responsible for holding a reference to a collection of handlers, and a Notify method invokes the OnPropertyChange method on each registered handler.

ViewModel

The OrderHeaderModel class is the binding source, so it exposes a property representing the value of each field in the form. The Property Let procedures are all structured as follows:

  • If current encapsulated value is not equal to the new value…
    • Set the current value to the new value
    • Notify of a property change

ViewModel classses need to implement INotifyPropertyChange, and the implementation simply uses an instance of the helper class above to do its thing:

Option Explicit
Implements INotifyPropertyChanged

Private Notification As New PropertyChangeNotification

'...

Private Sub OnPropertyChanged(ByVal Name As String)
    INotifyPropertyChanged_OnPropertyChanged Me, Name
End Sub

Private Sub INotifyPropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal Name As String)
    Notification.Notify Source, Name
End Sub

Private Sub INotifyPropertyChanged_RegisterHandler(ByVal Handler As IHandlePropertyChanged)
    Notification.AddHandler Handler
End Sub

The private OnPropertyChanged method further simplifies the notification by providing the Source argument, which needs to be an instance of the ViewModel, so that’s always Me. So the properties all look more or less like this:

Public Property Get OrderNumber() As Long
    OrderNumber = This.OrderNumber
End Property

Public Property Let OrderNumber(ByVal Value As Long)
    If This.OrderDate <> Value Then
        This.OrderNumber = Value
        OnPropertyChanged "OrderNumber"
    End If
End Property

The ViewModel is inherently domain-specific, so for a form that collects information about an order we’re going to be looking at properties like OrderNumber, OrderDate, BillToName, ShipToAddress, etc.; in another application, a ViewModel could be a completely different thing – it all really depends on what the thing is meant to do. But no matter what the domain is, a ViewModel will be implementing INotifyPropertyChanged as shown above.

View

Implementing the View (the form’s code-behind module) boils down to setting up all the necessary bindings, and we do this using a PropertyBindings helper module:

Option Explicit

'@Description "Binds a MSForms.Control property to a source property"
Public Function BindProperty(ByVal Control As MSForms.Control, ByVal ControlProperty As String, ByVal SourceProperty As String, ByVal Source As Object, Optional ByVal InvertBoolean As Boolean = False) As OneWayPropertyBinding
    
    Dim Binding As OneWayPropertyBinding
    Set Binding = New OneWayPropertyBinding
    
    Binding.Initialize Control, ControlProperty, Source, SourceProperty, InvertBoolean
    
    Set BindProperty = Binding

End Function

'@Description "Binds the Text/Value of a MSForms.TextBox to a source property"
Public Function BindTextBox(ByVal Control As MSForms.TextBox, ByVal SourceProperty As String, ByVal Source As Object) As TextBoxValueBinding
    
    Dim Binding As TextBoxValueBinding
    Set Binding = New TextBoxValueBinding
    
    Binding.Initialize Control, Source, SourceProperty
    
    Set BindTextBox = Binding
    
End Function

'@Description "Binds the Text of a MSForms.ComboBox to a String source property"
Public Function BindComboBox(ByVal Control As MSForms.ComboBox, ByVal SourceProperty As String, ByVal Source As Object) As ComboBoxValueBinding
    
    Dim Binding As ComboBoxValueBinding
    Set Binding = New ComboBoxValueBinding
    
    Binding.Initialize Control, Source, SourceProperty
    
    Set BindComboBox = Binding

End Function

'@Description "Binds the Value of a MSForms.CheckBox to a Boolean source property"
Public Function BindCheckBox(ByVal Control As MSForms.CheckBox, ByVal SourceProperty As String, ByVal Source As Object) As CheckBoxValueBinding
    
    Dim Binding As CheckBoxValueBinding
    Set Binding = New CheckBoxValueBinding
    
    Binding.Initialize Control, Source, SourceProperty
    
    Set BindCheckBox = Binding

End Function

As you can see each MSForms control gets its Binding class, and a OneWayPropertyBinding binds a source property to a target property without notifying for target changes (so without listening for control events) – this is useful for binding labels, ListBox/ComboBox contents, and anything else that doesn’t involve control events.

The form has a private ConfigureBindings method (invoked from the UserForm_Initialize handler) where we essentially map each one of the form controls to corresponding ViewModel properties:

Private Sub ConfigureBindings(ByVal Model As INotifyPropertyChanged)

    Const EnabledProperty As String = "Enabled"
    Const ListProperty As String = "List"
    
    This.Bindings.Add BindTextBox(Me.BillToNameBox, "BillToName", This.OrderModel)
    This.Bindings.Add BindTextBox(Me.BillToAddressLine1, "BillToLine1", This.OrderModel)
    This.Bindings.Add BindTextBox(Me.BillToAddressLine2, "BillToLine2", This.OrderModel)
    This.Bindings.Add BindTextBox(Me.BillToAddressLine3, "BillToLine3", This.OrderModel)
    This.Bindings.Add BindTextBox(Me.BillToEmailBox, "EmailAddress", This.OrderModel)
    This.Bindings.Add BindCheckBox(Me.BillToContributorBox, "IsContributor", This.OrderModel)
    
    This.Bindings.Add BindCheckBox(Me.ShipToSameBox, "ShipToBillingAddress", This.OrderModel)
    This.Bindings.Add BindTextBox(Me.ShipToNameBox, "ShipToName", This.OrderModel)
    This.Bindings.Add BindTextBox(Me.ShipToAddressLine1, "ShipToLine1", This.OrderModel)
    This.Bindings.Add BindTextBox(Me.ShipToAddressLine2, "ShipToLine2", This.OrderModel)
    This.Bindings.Add BindTextBox(Me.ShipToAddressLine3, "ShipToLine3", This.OrderModel)
    
    This.Bindings.Add BindProperty(Me.ShipToAddressLabel, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True)
    This.Bindings.Add BindProperty(Me.ShipToNameLabel, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True)
    This.Bindings.Add BindProperty(Me.ShipToNameBox, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True)
    This.Bindings.Add BindProperty(Me.ShipToAddressLine1, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True)
    This.Bindings.Add BindProperty(Me.ShipToAddressLine2, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True)
    This.Bindings.Add BindProperty(Me.ShipToAddressLine3, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True)
    
    This.Bindings.Add BindProperty(Me.ItemSkuSelectBox, ListProperty, "Value", InventorySheet.Table.ListColumns("SKU").DataBodyRange)
    This.Bindings.Add BindComboBox(Me.ItemSkuSelectBox, "SKU", This.OrderModel.NewLineItem)
    This.Bindings.Add BindTextBox(Me.ItemQuantityBox, "Quantity", This.OrderModel.NewLineItem)
    This.Bindings.Add BindTextBox(Me.ItemPriceBox, "Price", This.OrderModel.NewLineItem)
    
    This.Bindings.Add BindProperty(Me.LineItemsList, ListProperty, "LineItems", This.OrderModel)

End Sub

This rather straightforward configuration completely replaces event handlers. That’s right: the bindings take care of the control events for us, so checking the ShipToSameBox checkbox automatically disables the ShipToNameLabel, ShipToAddressLabel, ShipToAddressLine1, ShipToAddressLine2, and ShipToAddressLine3 controls on the form, and un-checking it automatically enables them, and we don’t need to explicitly handle any control events to achieve this. Small note: here the View is accessing a table in InventorySheet directly, and it shouldn’t be doing that, because what SKUs are available belongs in the Model, not the View: I should instead implement a service that accesses the worksheet for me and supplies the available SKU codes.

With the form controls effectively abstracted away by the ViewModel, we never need to directly interact with MSForms to affect the View, because the property bindings do this automatically for us. This means commands can affect just the ViewModel, and doing that will automatically keep the View in sync.

Commands

This lite version of MVVM doesn’t (yet?) have command bindings, but UI commands are still abstracted behind an ICommand interface. In my case I needed a command to add a new order line item, so I implemented it like this:

Option Explicit
Implements ICommand

Private Function ICommand_CanExecute(ByVal Parameter As Object) As Boolean
    ICommand_CanExecute = TypeOf Parameter Is OrderHeaderModel
End Function

Private Sub ICommand_Execute(ByVal Parameter As Object)
    If Not TypeOf Parameter Is OrderHeaderModel Then Err.Raise 5
    
    Dim Model As OrderHeaderModel
    Set Model = Parameter
    
    Dim Item As OrderLineItemModel
    Set Item = New OrderLineItemModel
    
    Item.SKU = Model.NewLineItem.SKU
    Item.Quantity = Model.NewLineItem.Quantity
    Item.Price = Model.NewLineItem.Price
    
    Model.AddLineItem Item
    
End Sub

This code is completely oblivious of any form or form controls: it only knows about the OrderHeaderModel and OrderLineItemModel classes, and what it needs to do with them. Why bother implementing this in a separate class, rather than in the form’s code-behind?

Without command bindings, we do need to handle command buttons’ Click event:

Private Sub AddLineItemButton_Click()
    CmdAddLineItem.Execute OrderModel
End Sub

I don’t like having logic in event handlers, so this one-liner is perfect. Without a command class the View would need to have more code, code that isn’t directly related to the View itself, and then the commands’ dependencies would become the View‘s dependencies, and that would be wrong: if I made a “Save to Database” button, I’d want the ADODB stuff anywhere but in the form’s code-behind; command classes can have their own dependencies, so pulling commands into their own classes keeps the View cohesive and focused on its purpose.

I’m finding that MVVM works best with relatively complex forms such as this one, where some fields’ enabled state might depend on some checkbox control’s value, for example. There’s something oddly satisfying typing something in a textbox and seeing another (disabled!) textbox get updated with the same content, knowing zero event handling is going on in the form.

Viability

If the full-featured MVVM framework isn’t viable in VBA, a more lightweight version of the UI paradigm certainly is: this particular VBA project doesn’t have dozens of class modules, and yet still manages to leverage what makes Model-View-ViewModel such a compelling architecture.

Rubberduck.Fakes Gets an Upgrade

One of the objectively coolest features in Rubberduck is the Fakes API. Code that pops a MsgBox for example, needs a way to work without actually popping that message box, otherwise that code cannot be unit tested… without somehow hijacking the MsgBox function. The Fakes API does exactly that: it hooks into the VBA runtime, intercepts specific internal function calls, and makes it return exactly what your test setup …set up.

This API can stop time, or Now can be told to return 1:59AM on first invocation, 1:00AM on the next, and then we can test and assert that some time-sensitive logic survives a daylight savings time toggle, or how Timer-dependent code behaves at midnight.

Let’s take a look at the members of the IFakesProvider interface.

Fakes Provider

Fakes for many of the internal VBA standard library functions exist since the initial release of the feature, although some providers wouldn’t always play nicely together – thanks to a recent pull request from @tommy9 these issues have been resolved, and a merry bunch of additional implementations are now available in pre-release builds:

NameDescriptionParameter names
MsgBoxConfigures VBA.Interaction.MsgBox callsFakes.Params.MsgBox
InputBoxConfigures VBA.Interaction.InputBox callsFakes.Params.InputBox
BeepConfigures VBA.Interaction.Beep calls
EnvironConfigures VBA.Interaction.Environ callsFakes.Params.Environ
TimerConfigures VBA.DateTime.Timer calls
DoEventsConfigures VBA.Interaction.DoEvents calls
ShellConfigures VBA.Interaction.Shell callsFakes.Params.Shell
SendKeysConfigures VBA.Interaction.SendKeys callsFakes.Params.SendKeys
KillConfigures VBA.FileSystem.Kill callsFakes.Params.Kill
MkDirConfigures VBA.FileSystem.MkDir callsFakes.Params.MkDir
RmDirConfigures VBA.FileSystem.RmDir callsFakes.Params.RmDir
ChDirConfigures VBA.FileSystem.ChDir callsFakes.Params.ChDir
ChDriveConfigures VBA.FileSystem.ChDrive callsFakes.Params.ChDrive
CurDirConfigures VBA.FileSystem.CurDir callsFakes.Params.CurDir
NowConfigures VBA.DateTime.Now calls
TimeConfigures VBA.DateTime.Time calls
DateConfigures VBA.DateTime.Date calls
Rnd*Configures VBA.Math.Rnd callsFakes.Params.Rnd
DeleteSetting*Configures VBA.Interaction.DeleteSetting callsFakes.Params.DeleteSetting
SaveSetting*Configures VBA.Interaction.SaveSetting callsFakes.Params.SaveSetting
Randomize*Configures VBA.Math.Randomize callsFakes.Params.Randomize
GetAllSettings*Configures VBA.Interaction.GetAllSettings calls
SetAttr*Configures VBA.FileSystem.SetAttr callsFakes.Params.SetAttr
GetAttr*Configures VBA.FileSystem.GetAttr callsFakes.Params.GetAttr
FileLen*Configures VBA.FileSystem.FileLen callsFakes.Params.FileLen
FileDateTime*Configures VBA.FileSystem.FileDateTime callsFakes.Params.FileDateTime
FreeFile*Configures VBA.FileSystem.FreeFile callsFakes.Params.FreeFile
IMEStatus*Configures VBA.Information.IMEStatus calls
Dir*Configures VBA.FileSystem.Dir callsFakes.Params.Dir
FileCopy*Configures VBA.FileSystem.FileCopy callsFakes.Params.FileCopy
*Members marked with an asterisk are only available in pre-release builds for now.

Parameter Names

The IVerify.ParameterXyz members make a unit test fail if the specified parameter wasn’t given a specified value, but the parameter names must be passed as strings. This is a UX issue: the API essentially requires hard-coded magic string literals in its users’ code; this is obviously error-prone and feels a bit arcane to use. The IFakesProvider interface has been given a Params property that gets an instance of a class that exposes the parameter names for each of the IFake implementations, as shown in the list above, and the screenshot below:

Picking the correct parameter name from a drop-down completion list beats risking a typo, doesn’t it?

Note: the PR for this feature has not yet been merged at the time of this writing.

Testing Without Fakes (aka Testing with Stubs)

Unit tests have a 3-part structure: first we arrange the test, then we act by invoking the method we want to test; lastly, we assert that an actual result matches the expectations. When using fakes, we configure them in the arrange part of the test, and in the assert part we can verify whether (and/or how many times) a particular method was invoked with a particular parameterization.

Let’s say we had a procedure we wanted to write some tests for:

Public Sub TestMe()
    If MsgBox("Print random number?", vbYesNo + vbQuestion, "Test") = vbYes Then
        Debug.Print Now & vbTab & Rnd * 42
    Else
        Debug.Print Now
    End If
End Sub

If we wanted to make this logic fully testable without the Fakes API, we would need to inject (likely as parameters) abstractions for MsgBox, Now, and Debug dependencies: instead of invoking MsgBox directly, the procedure would be invoking the Prompt method of an interface/class that wraps the MsgBox functionality. Unit tests would need a stub implementation of that interface in order to allow some level of configuration setup – an invocation counter, for example. A fully testable version of the above code might then look like this:

Public Sub TestMe(ByVal MessageBox As IMsgBox, ByVal Random As IRnd, ByVal DateTime As IDateTime, ByVal Logger As ILogger)
    If MessageBox.Prompt("Print random number?", "Test") = vbYes Then
        Logger.LogDebug DateTime.Now & vbTab & Random.Next * 42
    Else
        Logger.LogDebug DateTime.Now
    End If
End Sub

The method is testable, because the caller controls all the dependencies. We’re probably injecting an IMsgBox that pops a MsgBox, an IRnd that wraps Rnd, a DateTime parameter that returns VBA.DateTime.Now and an ILogger that writes to the debug pane, but we don’t know any of that. I fact, we could very well run this method with an ILogger that writes to some log file or even to a database; the IRnd implementation could consistently be returning 0.4 on every call, IDateTime.Now could return Now adjusted to UTC, and IMsgBox might actually display a fancy custom modal UserForm dialog – either way, TestMe doesn’t need to change for any of that to happen: it does what it needs to do, in this case fetching the next random number and outputting it along with the current date/time if a user prompt is answered with a “Yes”, otherwise just output the current date/time. It’s the interfaces that provide the abstraction that’s necessary to decouple the dependencies from the logic we want to test. We could implement these interfaces with stubs that simply count the number of times each member is invoked, and the logic we’re testing would still hold.

We could then write tests that validate the conditional logic:

'@TestMethod
Public Sub TestMe_WhenPromptYes_GetsNextRandomValue()
    ' Arrange
    Dim MsgBoxStub As StubMsgBox ' implements IMsgBox, but we want the stub functionality here
    Set MsgBoxStub = New StubMsgBox
    MsgBoxStub.Returns vbYes
    Dim RndStub As StubRnd ' implements IRnd, but we want the stub functionality here too
    Set RndStub = New StubRnd
    ' Act
    Module1.TestMe MsgBoxStub, RndStub, New DateTimeStub, New LoggerStub
    ' Assert
    Assert.Equals 1, RndStub.InvokeCount
End Sub
'@TestMethod
Public Sub TestMe_WhenPromptNo_DoesNotGetNextRandomValue()
    ' Arrange
    Dim MsgBoxStub As StubMsgBox
    Set MsgBoxStub = New StubMsgBox
    MsgBoxStub.Returns vbNo
    Dim RndStub As StubRnd
    Set RndStub = New StubRnd
    ' Act
    Module1.TestMe MsgBoxStub, RndStub, New DateTimeStub, New LoggerStub
    ' Assert
    Assert.Equals 0, RndStub.InvokeCount
End Sub

These stub implementations are class modules that need to be written to support such tests. StubMsgBox would implement IMsgBox and expose a public Returns method to configure its return value; StubRnd would implement IRnd and expose a public InvokeCount property that returns the number of times the IRnd.Next method was called. In other words, it’s quite a bit of boilerplate code that we’d usually rather not need to write.

Let’s see how using the Fakes API changes that.

Using Rubberduck.FakesProvider

The standard test module template defines Assert and Fakes private fields. When early-bound (needs a reference to the Rubberduck type library), the declarations and initialization look like this:

'@TestModule
Option Explicit
Option Private Module
Private Assert As Rubberduck.AssertClass
Private Fakes As Rubberduck.FakesProvider
'@ModuleInitialize
Public Sub ModuleInitialize()
    Set Assert = CreateObject("Rubberduck.AssertClass")
    Set Fakes = CreateObject("Rubberduck.FakesProvider")
End Sub

The Fakes API implements three of the four stubs for us, so we still need an implementation for ILogger, but now the method remains fully testable even with direct MsgBox, Now and Rnd calls:

Public Sub TestMe(ILogger Logger)
    If MsgBox("Print random number?", vbYesNo + vbQuestion, "Test") = vbYes Then
        Logger.LogDebug Now & vbTab & Rnd * 42
    Else
        Logger.LogDebug Now
    End If
End Sub

With an ILogger stub we could write a test that validates what’s being logged in each conditional branch (or we could decide that we don’t need an ILogger interface and we’re fine with tests actually writing to the debug pane, and leave Debug.Print statements in place), but let’s just stick with the same two tests we wrote above without the Fakes API. They look like this now:

'@TestMethod
Public Sub TestMe_WhenPromptYes_GetsNextRandomValue()
    
    ' Arrange
    Fakes.MsgBox.Returns vbYes
    ' Act
    Module1.TestMe New LoggerStub ' ILogger is irrelevant for this test
    ' Assert
    Fakes.Rnd.Verify.Once
End Sub
'@TestMethod
Public Sub TestMe_WhenPromptNo_DoesNotGetNextRandomValue()
    
    ' Arrange
    Fakes.MsgBox.Returns vbNo
    ' Act
    Module1.TestMe New LoggerStub ' ILogger is irrelevant for this test
    ' Assert
    Fakes.Rnd.Verify.Never
End Sub 

We configure the MsgBox fake to return the value we need, we invoke the method under test, and then we verify that the Rnd fake was invoked once or never, depending on what we’re testing. A failed verification will fail the test the same as a failed Assert call.

The fakes automatically track invocations, and remember what parameter values each invocation was made with. Setup can optionally supply an invocation number (1-based) to configure specific invocations, and verification can be made against specific invocation numbers as well, and we could have a failing test that validates whether Randomize is invoked when Rnd is called.

API Details

The IFake interface exposes members for the setup/configuration of fakes:

NameDescription
AssignsByRefConfigures the fake such as an invocation assigns the specified value to the specified ByRef argument.
PassthroughGets/sets whether invocations should pass through to the native call.
RaisesErrorConfigures the fake such as an invocation raises the specified run-time error.
ReturnsConfigures the fake such as the specified invocation returns the specified value.
ReturnsWhenConfigures the fake such as the specified invocation returns the specified value
given a specific parameter value.
VerifyGets an interface for verifying invocations performed during the test. See IVerify.
The members of Rubberduck.IFake

The IVerify interface exposes members for verifying what happened during the “Act” phase of the test:

NameDescription
AtLeastVerifies that the faked procedure was called a specified minimum number of times.
AtLeastOnceVerifies that the faked procedure was called one or more times.
AtMostVerifies that the faked procedure was called a specified maximum number of times.
AtMostOnceVerifies that the faked procedure was not called or was only called once.
BetweenVerifies that the number of times the faked procedure was called falls within the supplied range.
ExactlyVerifies that the faked procedure was called a specified number of times.
NeverVerifies that the faked procedure was called exactly 0 times.
OnceVerifies that the faked procedure was called exactly one time.
ParameterVerifies that the value of a given parameter to the faked procedure matches a specific value.
ParameterInRangeVerifies that the value of a given parameter to the faked procedure falls within a specified range.
ParameterIsPassedVerifies that an optional parameter was passed to the faked procedure. The value is not evaluated.
ParameterIsTypeVerifies that the passed value of a given parameter was of a type that matches the given type name.
The members of Rubberduck.IVerify

There’s also an IStub interface: it’s a subset of IFake, without the Returns setup methods. Thus, IStub is used for faking Sub procedures, and IFake for Function and Property procedures.


When to Stub Standard Library Members

Members of VBA.FileSystem not covered include EOF and LOF functions, Loc, Seek, and Reset. VBA I/O keywords Name, Open, and Close operate at a lower level than the standard library and aren’t covered, either. VBA.Interaction.CreateObject and VBA.Interaction.GetObject, VBA.Interaction.AppActivate, VBA.Interaction.CallByName, and the hidden VBA.Interaction.MacScript function, aren’t implemented.

Perhaps CreateObject and GetObject calls belong behind an abstract factory and a provider interface, respectively, and perhaps CallByName doesn’t really need hooking anyway. In any case there are a number of file I/O operations that cannot be faked and demand an abstraction layer between the I/O work and the code that commands it: that’s when you’re going to want to write stub implementations.

If you’re writing a macro that makes an HTTP request and processes its response, consider abstracting the HttpClient stuff behind an interface (something like Function HttpGet(ByVal Url As String)): the macro code will gain in readability and focus, and then if you inject that interface as a parameter, then a unit test can inject a stub implementation for it, and you can write tests that handle (or not?) an HTTP client error, or process such or such JSON or HTML payload – without hitting any actual network and making any actual HTTP requests.

Until we can do mocking with Rubberduck, writing test stubs for our system-boundary interfaces is going to have to be it. Mocking would remove the need to explicitly implement most test stubs, by enabling the same kind of customization as with fakes, but with your own interfaces/classes. Or Excel’s. Or anything, in theory.


Rubberduck 3.0 Progress Update

The next major version of Rubberduck is currently in [very] early development stages – saying that there is a lot of work ahead would be quite an understatement, but the skeleton is slowly taking shape, and things are looking very, very good.

Since the beginning of the project, Rubberduck’s user interface components (other than dialogs) have always been hosted in traditional, native dockable toolwindows. We built everything on top of the VBIDE editor, using Office CommandBar UI to simulate a status bar and make up for the lack of in-editor integration. Over the years this early design decision slowly became a burden: tearing down the many dockable toolwindows contributed to a pesky access violation crash on exit, low-level hooks for keyboard shortcuts constantly need to detach and re-attach as focus switches between the VBE main window and other applications, autocompletion/self-closing pairs was a nightmare to implement, and while the all-or-nothing approach to parsing made it so that we could always assume we were looking at valid VBA code that could be compiled, it also painted us into a corner where actually moving towards what we wanted Rubberduck to achieve by v3.0 would be extremely difficult, if not impossible.

Behold, the Rubberduck Editor

Rubberduck’s input was always driven by the Visual Basic Editor – now the code in the VBE is going to be output by Rubberduck. Of course, the code will go both ways, but now hidden attributes probably won’t need to be hidden anymore, and the editor can now be exactly what we envision it to be.

There will only be a single toolwindow that will host the editor and UI components like the Code Explorer. At this early stage my focus is entirely on the editor itself, but the idea is ultimately to get actual document tabs and a more practical and friendly docking manager.

Here’s what it looks like as of this writing:

The dropdowns don’t have a real item source yet, but the mock data gives a good idea of what it’s going to be like to edit VBA code with Rubberduck in the future.

Typing “Sub” and hitting the spacebar immediately completes the block and places a new folding node:

The faint dotted underline under “Sub” is a text marker; the editor has the ability to display various such markers at the exact desired position in the document, so we will be using them to show inspection results right there – with tooltips:

Hint-level results will be denoted with this dotted underline indicator; suggestion level will be a green squiggly underline, warnings a blue squiggle, and error level results will appear as red squiggles:

There will also be a new “ducky button” that pops up when the caret is on one such marker, and lets you pick a quick-fix in-place to address an inspection result:


The indenter still needs to be wired up, but this editor will ultimately indent your code as you type it. All the autocompletion features also need to be ported over to work here, and then we’ll want searchable and filterable IntelliSense, parameter info tooltips, and we’ll need to simulate the VBIDE “prettification” that occurs when a line is validated, so that public sub becomes Public Sub and identifiers take the casing they’re declared with.

We get an undo stack that can handle much more than 20 steps, and did I mention the status bar?

For now, all it does is report the current caret position in the editor, but Rubberduck 3.0 will be using it to report parsing progress, instead of the CommandBar button/label we’ve been abusing forever.

There will probably still be a command bar of some sort, but it will be part of the WPF/XAML managed UI; the old Rubberduck CommandBar will be decommissioned.

The one thing that’s 100% guaranteed to not happen in the new Rubberduck editor, is everything that needs to happen beyond design-time: there is no hook into the VBIDE debugger, so Rubberduck has no way of tracking the current instruction. As a result, the editor will be sadly useless in debug mode.


The editor work is just the beginning: Rubberduck 3.0 currently doesn’t even have a parser, let alone any inspections. In the next few months, the very heart of Rubberduck will be reworked to function with the new editor. It’s essentially like rewriting Rubberduck, but with an editor we fully control instead of one we constantly need to fight with.

Meanwhile v2.5.2 is approaching 25K downloads, and there’s quite a bit of work in 2.5.x that hasn’t been “officially” released yet, including everything that happened during a very successful Hacktoberfest 2022: we’ll be releasing v2.5.3 in the near future – stay tuned!