Using a WithEvents variable to handle the MSForms.Control events of, say, a TextBox control has the irritating tendency to throw a rather puzzling run-time error 459 “Object or class does not support the set of events”. To be honest, I had completely forgotten about this when I started working on this MVVM framework. I had even posted an answer on Stack Overflow and my learning-it-the-hard-way is immortalized on that page.
…there’s a bit of COM hackery going on behind the scenes; there’s enough smokes & mirrors for VBA to successfully compile the above, but, basically, you’re looking at a glitch in The Matrix (Rubberduck’s resolver has similar “nope” issues with MSForms controls): there isn’t any obvious way to get VBA to bind a dynamic control object to its MSForms.Control events.
-Mathieu Guindon, Apr 18 ’19
What I hadn’t noticed until today, was that another user had posted an answer to that question a few hours later that day – and that answer ultimately leads to the groundbreaking manual wiring-up of what VBA normally does automagically under the hood when we declare a WithEvents variable.
pUnk’d
The code I’m about to share is heavily based on the work shared on Stack Overflow by user Evr, and uses the ConnectToConnectionPoint Win32 API that, it must be mentioned, comes with a caveat:
This function is available through Windows XP and Windows Server 2003. It might be altered or unavailable in subsequent versions of Windows.
Regardless, it works (for now anyway, …if we lose Mac support for this specific capability).
Rubberduck uses similar connection points to handle a number of VBE events that aren’t otherwise exposed, so I knew this was going to work one way or another. The idea is to pass an IUnknown pointer to an object that exposes members with very specific VB_UserMemId attribute values, and have accordingly very specific member signatures.
This post lists a bunch of such attributes – however since there aren’t any problems with binding regular TextBox and CommandButton events (these do work with simple WithEvents event providers), I’m only interested in these:
Event
VB_UserMemId
AfterUpdate
-2147384832
BeforeUpdate
-2147384831
Enter
-2147384830
Exit
-2147384829
The VB_UserMemId attribute values for each of the MSForms.Control events.
This is going to be a little bit lower-level than usual, but every VBA user class has an IUnknown pointer, So we can use any class module that has the members with the appropriate VB_UserMemId attribute values, and pass that as the pUnk pointer argument.
So, here’s the punk in question, exactly as I currently have it:
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "ControlEventsPunk"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Attribute VB_Description = "Provides an event sink to relay MSForms.Control events."
'@Folder MVVM.Infrastructure.Win32
'@ModuleDescription "Provides an event sink to relay MSForms.Control events."
'based on https://stackoverflow.com/a/51936950
Option Explicit
Implements IControlEvents
Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(0 To 7) As Byte
End Type
'[This function is available through Windows XP and Windows Server 2003. It might be altered or unavailable in subsequent versions of Windows.]
'https://docs.microsoft.com/en-us/windows/win32/api/shlwapi/nf-shlwapi-connecttoconnectionpoint
#If VBA7 Then
Private Declare PtrSafe Function ConnectToConnectionPoint Lib "shlwapi" Alias "#168" (ByVal Punk As stdole.IUnknown, ByRef riidEvent As GUID, ByVal fConnect As Long, ByVal PunkTarget As stdole.IUnknown, ByRef pdwCookie As Long, Optional ByVal ppcpOut As LongPtr) As Long
#Else
Private Declare Function ConnectToConnectionPoint Lib "shlwapi" Alias "#168" (ByVal punk As stdole.IUnknown, ByRef riidEvent As GUID, ByVal fConnect As Long, ByVal punkTarget As stdole.IUnknown, ByRef pdwCookie As Long, Optional ByVal ppcpOut As Long) As Long
#End If
Private Type TState
RefIID As GUID 'The IID of the interface on the connection point container whose connection point object is being requested.
Connected As Boolean
PunkTarget As Object
Cookie As Long
Handlers As Collection
End Type
'from https://stackoverflow.com/a/61893857 (same user as #51936950!)
Private Const ExitEventID As Long = -2147384829
Private Const EnterEventID As Long = -2147384830
Private Const BeforeUpdateEventID As Long = -2147384831
Private Const AfterUpdateEventID As Long = -2147384832
Private This As TState
'@Description "Gets/sets the target MSForms.Control reference."
Public Property Get Target() As Object
Attribute Target.VB_Description = "Gets/sets the target MSForms.Control reference."
Set Target = This.PunkTarget
End Property
Public Property Set Target(ByVal RHS As Object)
Set This.PunkTarget = RHS
End Property
'@Description "Registers the listener."
Public Function Connect() As Boolean
Attribute Connect.VB_Description = "Registers the listener."
GuardClauses.GuardNullReference This.PunkTarget, TypeName(Me), "Target is not set."
ConnectToConnectionPoint Me, This.RefIID, True, This.PunkTarget, This.Cookie, 0&
This.Connected = This.Cookie <> 0
Connect = This.Connected
End Function
'@Description "De-registers the listener."
Public Function Disconnect() As Boolean
Attribute Connect.VB_Description = "De-registers the listener."
If Not This.Connected Then Exit Function
ConnectToConnectionPoint Me, This.RefIID, False, This.PunkTarget, This.Cookie, 0&
This.Connected = False
Disconnect = True
End Function
'@Description "A callback that handles MSForms.Control.AfterUpdate events for the registered target control."
Public Sub OnAfterUpdate()
Attribute OnAfterUpdate.VB_UserMemId = -2147384832
Attribute OnAfterUpdate.VB_Description = "A callback that handles MSForms.Control.AfterUpdate events for the registered target control."
Dim Handler As IHandleControlEvents
For Each Handler In This.Handlers
Handler.HandleAfterUpdate
Next
End Sub
'@Description "A callback that handles MSForms.Control.BeforeUpdate events for the registered target control."
Public Sub OnBeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Attribute OnBeforeUpdate.VB_UserMemId = -2147384831
Attribute OnBeforeUpdate.VB_Description = "A callback that handles MSForms.Control.BeforeUpdate events for the registered target control."
Dim Handler As IHandleControlEvents
For Each Handler In This.Handlers
Handler.HandleBeforeUpdate Cancel
Next
End Sub
'@Description "A callback that handles MSForms.Control.Exit events for the registered target control."
Public Sub OnExit(ByVal Cancel As MSForms.ReturnBoolean)
Attribute OnExit.VB_UserMemId = -2147384829
Attribute OnExit.VB_Description = "A callback that handles MSForms.Control.Exit events for the registered target control."
Dim Handler As IHandleControlEvents
For Each Handler In This.Handlers
Handler.HandleExit Cancel
Next
End Sub
'@Description "A callback that handles MSForms.Control.Enter events for the registered target control."
Public Sub OnEnter()
Attribute OnEnter.VB_UserMemId = -2147384830
Attribute OnEnter.VB_Description = "A callback that handles MSForms.Control.Enter events for the registered target control."
Dim Handler As IHandleControlEvents
For Each Handler In This.Handlers
Handler.HandleEnter
Next
End Sub
'@Description "Registers the specified object to handle the relayed control events."
Public Sub RegisterHandler(ByVal Handler As IHandleControlEvents)
Attribute RegisterHandler.VB_Description = "Registers the specified object to handle the relayed control events."
This.Handlers.Add Handler
End Sub
Private Sub Class_Initialize()
Set This.Handlers = New Collection
This.RefIID.Data1 = &H20400
This.RefIID.Data4(0) = &HC0
This.RefIID.Data4(7) = &H46
End Sub
Private Sub IControlEvents_OnAfterUpdate()
OnAfterUpdate
End Sub
Private Sub IControlEvents_OnBeforeUpdate(ByVal Cancel As MSForms.IReturnBoolean)
OnBeforeUpdate Cancel
End Sub
Private Sub IControlEvents_OnEnter()
OnEnter
End Sub
Private Sub IControlEvents_OnExit(ByVal Cancel As MSForms.IReturnBoolean)
OnExit Cancel
End Sub
Private Sub IControlEvents_RegisterHandler(ByVal Handler As IHandleControlEvents)
RegisterHandler Handler
End Sub
Let’s ignore the IControlEvents interface for now. The class has a Target – that’ll be our TextBox control instance. So we set the Target, and then we can invoke Connect, and when we’re done we can invoke Disconnect to explicitly undo the wiring-up.
Then we have an OnEnter method with VB_UserMemId = -2147384830, which makes it an event handler procedure for MSForms.Control.Enter. The name of the procedure isn’t relevant, but it’s important that the procedure is parameterless.
Similarly, the name of the OnExit procedure has no importance, but it must have a single ByVal Cancel As MSForms.ReturnBoolean parameter (only ByVal and the data type matter). For events that have more than one parameter, the order is also important.
In theory that’s all we need: we could go on and handle Control.Exit in this OnExit procedure, and call it a day. In fact you can probably do that right away – however I need another step for my purposes, because I’m going to need my PropertyBindingBase class to propagate these events “up” to, say, some TextBoxPropertyBinding class that can implement some TextBox-specific behavior for the Control events.
Propagating Events
I had already a working pattern for my INotifyPropertyChange requirements to propagate property changes across objects, and the pattern is applicable here too. See, I could have declared a Public Event Exit(ByRef Cancel As MSForms.ReturnBoolean) on the ControlEventsPunk class, and then I could have used a WithEvents variable to handle them – and that would have worked too. Except I don’t want to use events here, because events work well as implementation details… but they can’t be exposed on an interface, which makes them actually more complicated to work with.
There are two interfaces: one that defines the “events” and exposes a method to register “handlers”, and the other mandates the presence of a callback for each “event”. For INotifyPropertyChange the handler interface was named IHandlePropertyChange, so I went with IControlEvents and IHandleControlEvents.
So, the “provider” interface looks like this:
'@Folder MVVM.Infrastructure.Bindings.Abstract
'@ModuleDescription "Provides the infrastructure to relay MSForms.Control events."
Option Explicit
Public Sub RegisterHandler(ByVal Handler As IHandleControlEvents)
End Sub
Public Sub OnEnter()
End Sub
Public Sub OnExit(ByVal Cancel As MSForms.ReturnBoolean)
End Sub
Public Sub OnAfterUpdate()
End Sub
Public Sub OnBeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
End Sub
And then the “handler” interface looks like this:
'@Folder MVVM.Infrastructure.Bindings.Abstract
'@ModuleDescription "An object that can be registered as a handler for IControlEvents callbacks."
Option Explicit
Public Sub HandleEnter()
End Sub
Public Sub HandleExit(ByVal Cancel As MSForms.ReturnBoolean)
End Sub
Public Sub HandleAfterUpdate()
End Sub
Public Sub HandleBeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
End Sub
So, looking back at the ControlEventsPunk class, we find that the implementation for RegisterHandler consists in adding the provided Handler object to an encapsulated Collection that holds all the registered handlers; when we “handle” a control event, we iterate all registered handlers and invoke them all in a sequence. When an event has a Cancel parameter, the last handler that ran gets the final say on whether the parameter should be True or False, and each handler receives the Cancel value that was set by the previous handler than ran.
This is a slightly different paradigm than your regular VBA/VB6 auto-wired events, where one event only ever has one handler: now these work more like the multicast delegates that events are in .NET, with an “invocation list” and the ability to add/remove (although, I haven’t implemented the removal) handlers dynamically at run-time – except the “handlers” are full-fledged VBA objects here, rather than .NET delegates.
Whenever the MVVM infrastructure needs to propagate events, I use this pattern instead. This was my first time actually implementing an Observer Pattern, and hadn’t even realized! (thanks Max!) – that isn’t a pattern you see often in event-capable languages, but I can definitely see this proven, solid abstraction (Java developers would probably be rather familiar with that one) become my new favorite go-to pattern to expose events on an interface in VBA… But there’s probably a reason the first time I come across a situation where that pattern is really handy (and actually needed, for testability), is when I’m writing framework-level (i.e. an API intended to be used by code that isn’t written yet) code that’s very much as deep into the OOP rabbit hole as I’ve ever been in VBA (or any other language for that matter)… and there’s still no rock bottom in sight.
In any case, now that we have a way to handle and propagate control events, we can have MVVM property bindings that can format TextBox.Text on exit, i.e. we can have a ViewModel that knows SomeProperty has a value of 25.59, and the Text property of the bound textbox control can say $25.59 just by specifying a FormatString (like “Currency”, for example) when we create the binding.
For the next post in this series I think we’re ready to deep-dive into the actual binding mechanics, and I’ll have the updated MVVM infrastructure code on GitHub by then.
I have recently written (100% VBA) a proof-of-concept for a Model-View-ViewModel (MVVM) framework, and since the prototype works exactly as needed (with some rough edges of course)… I’ve decided to explore what Rubberduck can do to make MVVM fully supported, but going down that path poses a serious problem that needs a very good and well thought-out solution.
A Vision of a Framework
When you start a new project in Visual Studio (including 6.0 /VB6), the IDE prompts for a project type, essentially asking “what are we building today?“
In VBA the assumption is that you just want to write a bit of script to automate some document manipulation. And then the framework so to speak, is the VBA Standard Library: functions, methods, constants, and actual objects too; all globally-scoped for convenience and quick-and-easy access: a fully spelled-out VBA.Interaction.MsgBox function call is a rare sight! Combined with the nonexistence of namespaces, the flip side is that the global scope is easily polluted, and name collisions are inevitable since anything exposed by any library becomes globally accessible. This makes fully-qualified global function calls appear sporadically sprinkled in the code, which can be confusing. I digress, but what I mean to get at is that this is part of what made Microsoft make the shift to the .NET platform in the early 2000’s, and eventually abandon the Visual Basic Editor to its fate. The COM platform and Win32 API was the framework, and Win32 programming languages built on top of that.
This leaves two approaches for a vision of a “framework” for VBA:
Package a type library and ship it.
Pros: any COM-visible library will work, can be written in .NET
Cons: projects now have a hard dependency on a specific type library; updating is a mess, etc.
Embed the framework into VBA projects, pretty much like JavaScript does.
Pros: devs are in charge of everything, framework is 100% VBA and inherently open-source, updating is essentially seamless for any non-breaking change, no early-bound dependencies, graceful late-bound degradation, etc.
Cons: VBA devs and maintainers that aren’t using Rubberduck will be massively lost in the source code (framework would cleanly leverage @Folder annotations), but then when the host application allows it this could be mitigated by embedding the code into its own separate VBA project and reference it from other projects (e.g. ship an Excel add-in with the framework code your VBA project depends on).
I think I’m slightly biased here, but I think this rules out the type library approach regardless. So we need a way to make this work in VBA, with VBA source code that lives in a GitHub repository with vetted, trusted content.
Where Rubberduck fits in
Like Visual Studio, Rubberduck could prompt VBA devs with “what are we building today?” and offer to pull various “bundles” of modules from this GitHub repository into the active project. Rubberduck would request the available “bundles” from api.rubberduckvba.com, which would return with “bundle metadata” describing each “package” (is “nugget” forbidden to use as a name for these / play on “nuget” (the package manager for .NET)?), and then list them in a nice little dialog.
The “nugget” metadata would include a name, a description, and the path to each file to download for it. Every package would be the same “version”, but the tool could easily request any particular “tag” or “release” version, and/or pull from “main” or from “next” branches, and the source code / framework itself could then easily be a collaborative effort, with its own features and projects and milestones and collaborators, completely separate from the C# Rubberduck code base.
This complete decoupling from Rubberduck means you don’t need to use Rubberduck to leverage this VBA code in your VBA projects, and new tags / “releases” would be entirely independent of Rubberduck’s own release cycles. That means you’re using, say, future-Rubberduck 2.7.4 and the “nuggets” feature offers “v1.0 [main]” and “v1.1 [next]”; one day you’re still using Rubberduck 2.7.4 but now you get “v1.1 [main]”, “v1.0”, and “v1.2 [next]” to chose from, and if you updated the “nuggets” in your project from v1.0 to v1.1 then Rubberduck inspections would flag uses of any obsolete members that would now be decorated with @Obsolete annotations… it’s almost like this annotation was presciently made for this.
But before we can even think of implementing something like this and make MVVM infrastructure the very first “nugget”, we need a rock-solid framework in the first place.
Unit Tests
I had already written the prototype in a highly decoupled manner, mindful of dependencies and how things could later be tested from the outside. I’m very much not-a-zealot when it comes to things like Test-Driven Development (TDD), but I do firmly believe unit tests provide a solid safety net and documentation for everything that matters – especially if the project is to make any kind of framework, where things need to provably work.
And then it makes a wonderful opportunity to blog about writing unit tests with Rubberduck, something I really haven’t written nearly enough about.
Tests? Why?!
Just by writing these tests, I’ve found and fixed edge-case bugs and improved decoupling and cohesion by extracting (and naming!) smaller chunks of functionality into their own separate class module. The result is quite objectively better, simpler code.
Last but not least, writing testable code (let alone the tests!) in VBA makes a great way to learn these more advanced notions and concepts in a language you’re already familiar with.
If you’re new to VBA and programming in general, or if you’re not a programmer and you’re only interested in making macros, then reading any further may make your head spin a bit (if that’s already under way… I’m sorry!), so don’t hesitate to ask here or on the examples repository on GitHub if you have any questions! This article is covering a rather advanced topic, beyond classes and interfaces, but keep in mind that unit testing does not requireOOP! It just so happens that object-oriented code adhering to SOLID principles tends to be easily testable.
This is an ongoing project and I’m still working on the test suite and refactoring things; I wouldn’t want to upload the code to GitHub in its current shape, so I’ll come back here with a link once I have something that’s relatively complete.
Where to Start?
There’s a relatively small but very critical piece of functionality that makes a good place to begin in the MVVM infrastructure code (see previous article): the BindingPath class, which I’ve pulled out of PropertyBinding this week. The (still too large for its own good) PropertyBinding class is no longer concerned with the intricacies of resolving property names and values: both this.Source and this.Target are declared As IBindingPath in a PropertyBinding now, which feels exactly right.
The purpose of a BindingPath is to take a “binding context” object and a “binding path” string (the binding path is always relative to the binding context), and to resolve the member call represented there. For example, this would be a valid use of the class:
Dim Path As IBindingPath
Set Path = BindingPath.Create(Sheet1.Shapes("Shape1").TextFrame.Characters, "Text")
This Path object implements TryReadPropertyValue and TryWritePropertyValue methods that the BindingManager can invoke as needed.
'@Folder MVVM.Infrastructure.Bindings
'@ModuleDescription "An object that can resolve a string property path to a value."
'@PredeclaredId
Option Explicit
Implements IBindingPath
Private Type TState
Context As Object
Path As String
Object As Object
PropertyName As String
End Type
Private This As TState
'@Description "Creates a new binding path from the specified property path string and binding context."
Public Function Create(ByVal Context As Object, ByVal Path As String) As IBindingPath
GuardClauses.GuardNonDefaultInstance Me, BindingPath, TypeName(Me)
GuardClauses.GuardNullReference Context, TypeName(Me)
GuardClauses.GuardEmptyString Path, TypeName(Me)
Dim Result As BindingPath
Set Result = New BindingPath
Set Result.Context = Context
Result.Path = Path
Result.Resolve
Set Create = Result
End Function
'@Description "Gets/Sets the binding context."
Public Property Get Context() As Object
Set Context = This.Context
End Property
Public Property Set Context(ByVal RHS As Object)
GuardClauses.GuardDefaultInstance Me, BindingPath, TypeName(Me)
GuardClauses.GuardNullReference RHS, TypeName(Me)
GuardClauses.GuardDoubleInitialization This.Context, TypeName(Me)
Set This.Context = RHS
End Property
'@Description "Gets/Sets a string representing a property path against the binding context."
Public Property Get Path() As String
Path = This.Path
End Property
Public Property Let Path(ByVal RHS As String)
GuardClauses.GuardDefaultInstance Me, BindingPath, TypeName(Me)
GuardClauses.GuardEmptyString RHS, TypeName(Me)
GuardClauses.GuardDoubleInitialization This.Path, TypeName(Me)
This.Path = RHS
End Property
'@Description "Gets the bound object reference."
Public Property Get Object() As Object
Set Object = This.Object
End Property
'@Description "Gets the name of the bound property."
Public Property Get PropertyName() As String
PropertyName = This.PropertyName
End Property
'@Description "Resolves the Path to a bound object and property."
Public Sub Resolve()
This.PropertyName = ResolvePropertyName(This.Path)
Set This.Object = ResolvePropertyPath(This.Context, This.Path)
End Sub
Private Function ResolvePropertyName(ByVal PropertyPath As String) As String
Dim Parts As Variant
Parts = Strings.Split(PropertyPath, ".")
ResolvePropertyName = Parts(UBound(Parts))
End Function
Private Function ResolvePropertyPath(ByVal Context As Object, ByVal PropertyPath As String) As Object
Dim Parts As Variant
Parts = Strings.Split(PropertyPath, ".")
If UBound(Parts) = LBound(Parts) Then
Set ResolvePropertyPath = Context
Else
Dim RecursiveProperty As Object
Set RecursiveProperty = CallByName(Context, Parts(0), VbGet)
If RecursiveProperty Is Nothing Then Exit Function
Set ResolvePropertyPath = ResolvePropertyPath(RecursiveProperty, Right$(PropertyPath, Len(PropertyPath) - Len(Parts(0)) - 1))
End If
End Function
Private Property Get IBindingPath_Context() As Object
Set IBindingPath_Context = This.Context
End Property
Private Property Get IBindingPath_Path() As String
IBindingPath_Path = This.Path
End Property
Private Property Get IBindingPath_Object() As Object
Set IBindingPath_Object = This.Object
End Property
Private Property Get IBindingPath_PropertyName() As String
IBindingPath_PropertyName = This.PropertyName
End Property
Private Sub IBindingPath_Resolve()
Resolve
End Sub
Private Function IBindingPath_ToString() As String
IBindingPath_ToString = StringBuilder _
.AppendFormat("Context: {0}; Path: {1}", TypeName(This.Context), This.Path) _
.ToString
End Function
Private Function IBindingPath_TryReadPropertyValue(ByRef outValue As Variant) As Boolean
If This.Object Is Nothing Then Resolve
On Error Resume Next
outValue = VBA.Interaction.CallByName(This.Object, This.PropertyName, VbGet)
IBindingPath_TryReadPropertyValue = (Err.Number = 0)
On Error GoTo 0
End Function
Private Function IBindingPath_TryWritePropertyValue(ByVal Value As Variant) As Boolean
If This.Object Is Nothing Then Resolve
On Error Resume Next
VBA.Interaction.CallByName This.Object, This.PropertyName, VbLet, Value
IBindingPath_TryWritePropertyValue = (Err.Number = 0)
On Error GoTo 0
End Function
Here’s our complete “system under test” (SUT) as far as the BindingPathTests module goes. We have a Create factory method, Context and Path properties, just like the class we’re testing.
The path object is itself read-only once initialized, but the binding source may resolve to Nothing or to a different object reference over the course of the object’s lifetime: say we want a binding path to SomeViewModel.SomeObjectProperty; when we first create the binding, SomeObjectProperty might very well be Nothing, and then it’s later Set-assigned to a valid object reference. This is why the IBindingPath interface needs to expose a Resolve method, so that IPropertyBinding can invoke it as needed, as the binding is being applied.
We’ll want a test for every guard clause, and each method needs at least one test as well.
So, I’m going to add a new test module and call it BindingPathTests. Rubberduck’s templates are good-enough to depict the mechanics and how things work at a high level, but if you stick to the templates you’ll quickly find your unit tests rather boring, wordy, and repetitive: we must break out of the mold, there isn’t one true way to do this!
Rubberduck discovers unit tests in standard modules annotated with @TestModule. Test methods are any [parameterless, for now] method annotated with a @TestMethod annotation that can have a category string – the Test Explorer can group your tests using these categories. The declarations section of a test module must include a declaration (early or late bound) for an Rubberduck.AssertClass or Rubberduck.PermissiveAssertClass (both implement the same internal interface; the “permissive” one has VBA-like equality semantics, and the default one has stricter type equality requirements (a Long can’t be equal to a Double, for example). The default test template also defines a FakesProvider object, but we’re not going to need it now (if we needed to test logic that involved e.g. branching on the result of a MsgBox function call, we could hook into the MsgBox function and configure it to return what the test needs it to return, which is honestly wicked awesome). So our test module might look something like this at first:
'@Folder Tests.Bindings
'@TestModule
Option Explicit
Option Private Module
#Const LateBind = LateBindTests
#If LateBind Then
Private Assert As Object
#Else
Private Assert As Rubberduck.AssertClass
#End If
With this conditionally-compiled setup, all we need to toggle between late and early binding is to define a project-scoped conditional compilation argument: bring up the project properties and type LateBindTests=0 or LateBindTests=1 in that box, and just like that you can control conditional compilation project-wide without modifying a single module.
The first thing to do is to get the test state defined, and implement TestInitialize and TestCleanup methods that configure this state – in the case of BindingManagerTests, I’m going to add a private type and a private field to define and hold the current test state:
Private Type TState
ExpectedErrNumber As Long
ExpectedErrSource As String
ExpectedErrorCaught As Boolean
ConcreteSUT As BindingManager
AbstractSUT As IBindingManager
HandlePropertyChangedSUT As IHandlePropertyChanged
BindingSource As TestBindingObject
BindingTarget As TestBindingObject
SourcePropertyPath As String
TargetPropertyPath As String
Command As TestCommand
End Type
Private Test As TState
Unit Testing Paradigm
Test modules are special, in the sense that they aren’t (absolutely shouldn’t be anyway) accessible from any code path in the project. Rubberduck invokes them one by one when you run a command like “run all tests” or “repeat last run”. But there’s a little more to it than that, worthy of mention.
VBA being single-threaded, tests are invoked by Rubberduck on the UI/main thread, and uses a bit of trickery to keep its own UI somewhat responsive. Each module runs sequentially, and each test inside each module runs sequentially as well – but the test execution order still shouldn’t be considered deterministic, and each test should be completely independent of every other test, such that executing all tests in any given order always produces the same outcomes.
A test that makes no assertions will be green/successful. When writing unit tests, the first thing you want to see is a test that’s failing (you can’t trust a test you have never seen fail!), and with Rubberduck in order to give a test a reason to fail, you use Assert methods (wiki).
When Rubberduck begins processing a test module, it invokes the methods (again, sequentially but not in an order that should matter) marked @ModuleInitialize in the module – ideally that would be only one method.
This is where the Assert object should be assigned (the default test templates do this):
'@ModuleInitialize
Private Sub ModuleInitialize()
#If LateBind Then
'requires HKCU registration of the Rubberduck COM library.
Set Assert = CreateObject("Rubberduck.PermissiveAssertClass")
#Else
'requires project reference to the Rubberduck COM library.
Set Assert = New Rubberduck.PermissiveAssertClass
#End If
End Sub
Rubberduck’s test engine will then execute all methods (usually cleaner with only one though) annotated with @TestInitializebefore executing each test in the module; that is the best place to put the wordy setup code that would otherwise need to be in pretty much every single test of the module:
'@TestInitialize
Private Sub TestInitialize()
Dim Context As TestBindingObject
Set Context = New TestBindingObject
Set Context.TestBindingObjectProperty = New TestBindingObject
Test.Path = "TestBindingObjectProperty.TestStringProperty"
Test.PropertyName = "TestStringProperty"
Set Test.BindingSource = Context.TestBindingObjectProperty
Set Test.BindingContext = Context
Set Test.ConcreteSUT = BindingPath.Create(Test.BindingContext, Test.Path)
Set Test.AbstractSUT = Test.ConcreteSUT
End Sub
By moving the test state to module level rather than having it local to each test, we already eliminate a lot of code duplication, and the Test module variable makes a rather nifty way to access the current test state, too!
Methods annotated with @TestCleanup are automatically invoked after each test in the module; in order to avoid accidentally sharing state between tests, every object reference should be explicitly set to Nothing, and values of intrinsic data types should be explicitly reset to their respective default value:
'@TestCleanup
Private Sub TestCleanup()
Set Test.ConcreteSUT = Nothing
Set Test.AbstractSUT = Nothing
Set Test.BindingSource = Nothing
Set Test.BindingContext = Nothing
Test.Path = vbNullString
Test.PropertyName = vbNullString
Test.ExpectedErrNumber = 0
Test.ExpectedErrSource = vbNullString
Test.ExpectedErrorCaught = False
End Sub
What Goes Into the Test State?
A number of members should always be in the Test state structure:
ConcreteSUT (or just SUT) and AbstractSUT both point to the same object, through the default interface (BindingPath) and the explicit one (IBindingPath), respectively.
If the system under test class implements additional interfaces, having a pointer to the SUT object with these interfaces is also useful. For example the TState type for the BindingManager class has a HandlePropertyChangedSUT As IHandlePropertyChanged member, because the class implements this interface.
Default property values and dependency setup: we want a basic default SUT configured and ready to be tested (or fine-tuned and then tested).
ExpectedErrNumber, ExpectedErrSource, and ExpectedErrorCaught are useful when a test is expecting a given input to produce a particular specific error.
Expecting Errors
The “expected error” test method template works for its purpose, but having this on-error-assert logic duplicated everywhere is rather ugly. Consider pulling that logic into a private method instead (I’m considering adding this into Rubberduck’s test module templates):
Private Sub ExpectError()
Dim Message As String
If Err.Number = Test.ExpectedErrNumber Then
If (Test.ExpectedErrSource = vbNullString) Or (Err.Source = Test.ExpectedErrSource) Then
Test.ExpectedErrorCaught = True
Else
Message = "An error was raised, but not from the expected source. " & _
"Expected: '" & TypeName(Test.ConcreteSUT) & "'; Actual: '" & Err.Source & "'."
End If
ElseIf Err.Number <> 0 Then
Message = "An error was raised, but not with the expected number. Expected: '" & Test.ExpectedErrNumber & "'; Actual: '" & Err.Number & "'."
Else
Message = "No error was raised."
End If
If Not Test.ExpectedErrorCaught Then Assert.Fail Message
End Sub
With this infrastructure in place, the unit tests for all guard clauses in the module can look like this – it’s still effectively doing Arrange-Act-Assert like the test method templates strongly suggest, only implicitly so (each “A” is essentially its own statement, see comments in the tests below):
'@TestMethod("GuardClauses")
Private Sub Create_GuardsNullBindingContext()
Test.ExpectedErrNumber = GuardClauseErrors.ObjectCannotBeNothing '<~ Arrange
On Error Resume Next
BindingPath.Create Nothing, Test.Path '<~ Act
ExpectError '<~ Assert
On Error GoTo 0
End Sub
'@TestMethod("GuardClauses")
Private Sub Create_GuardsEmptyPath()
Test.ExpectedErrNumber = GuardClauseErrors.StringCannotBeEmpty '<~ Arrange
On Error Resume Next
BindingPath.Create Test.BindingContext, vbNullString '<~ Act
ExpectError '<~ Assert
On Error GoTo 0
End Sub
'@TestMethod("GuardClauses")
Private Sub Create_GuardsNonDefaultInstance()
Test.ExpectedErrNumber = GuardClauseErrors.InvalidFromNonDefaultInstance '<~ Arrange
On Error Resume Next
With New BindingPath
.Create Test.BindingContext, Test.Path '<~ Act
ExpectError '<~ Assert
End With
On Error GoTo 0
End Sub
And then similar tests exist for the respective guard clauses of Context and Path members. Having tests that validate that guard clauses are doing their job is great: it tells us exactly how not to use the class… and that doesn’t tell us much about what a BindingPath object actually does.
Testing the Actual Functionality
The methods we’re testing need to be written in a way that makes it possible for a test to determine whether it’s doing its job correctly or not. For functions and properties, the return value is the perfect thing to Assert on. For Sub procedures, you have to Assert on the side-effects, and have verifiable and useful, reliable ways to verify them.
These two tests validate that the BindingPath returned by the Create factory method has resolved the PropertyName and Object properties, respectively.
'@TestMethod("Bindings")
Private Sub Create_ResolvesPropertyName()
Dim SUT As BindingPath
Set SUT = BindingPath.Create(Test.BindingContext, Test.Path)
Assert.IsFalse SUT.PropertyName = vbNullString
End Sub
'@TestMethod("Bindings")
Private Sub Create_ResolvesBindingSource()
Dim SUT As BindingPath
Set SUT = BindingPath.Create(Test.BindingContext, Test.Path)
Assert.IsNotNothing SUT.Object
End Sub
I could have made multiple assertions in a test, like this…
'@TestMethod("Bindings")
Private Sub Create_ResolvesBindingSource()
Dim SUT As BindingPath
Set SUT = BindingPath.Create(Test.BindingContext, Test.Path)
Assert.IsFalse SUT.PropertyName = vbNullString
Assert.IsNotNothing SUT.Object
End Sub
The Test Explorer would say “IsFalse assert failed” or “IsNotNothing assert failed”, so it’s arguably (perhaps pragmatically so) still useful and clear enough why that test would fail (and if you had multiple Assert.IsFalse calls in a test you could provide a different message for each)… but really as a rule of thumb, tests want to have one reason to fail. If the conditions to meaningfully pass or fail a test aren’t present, use Assert.Inconclusive to report the test as such:
'@TestMethod("Bindings")
Private Sub Resolve_SetsBindingSource()
With New BindingPath
.Path = Test.Path
Set .Context = Test.BindingContext
If Not .Object Is Nothing Then Assert.Inconclusive "Object reference is unexpectedly set."
.Resolve
Assert.AreSame Test.BindingSource, .Object
End With
End Sub
'@TestMethod("Bindings")
Private Sub Resolve_SetsBindingPropertyName()
With New BindingPath
.Path = Test.Path
Set .Context = Test.BindingContext
If .PropertyName <> vbNullString Then Assert.Inconclusive "PropertyName is unexpectedly non-empty."
.Resolve
Assert.AreEqual Test.PropertyName, .PropertyName
End With
End Sub
This mechanism is especially useful when the test state isn’t in local scope and there’s a real possibility that the TestInitialize method is eventually modified and inadvertently breaks a test. Such conditional Assert.Inconclusive calls are definitely a form of defensive programming, just like having guard clauses throwing custom meaningful errors.
Note that while we know that the BindingPath.Create function invokes the Resolve method, the tests for Resolve don’t involve Create: the Path and Context are being explicitly spelled out, and the .Resolve method is invoked from a New instance.
And that’s pretty much everything there is to test in the BindingPath class.
There’s one thing I haven’t mentioned yet, that you might have caught in the TState type:
BindingSource As TestBindingObject
BindingTarget As TestBindingObject
This TestBindingObject is a test stub: it’s a dependency of the class (it’s the “binding context” of the test path) and it’s a real object, but it is implemented in a bit of a special way that the BindingPath tests don’t do justice to.
Test Stubs
Eventually Rubberduck’s unit testing framework will feature a COM-visible wrapper around Moq, a popular mocking framework for .NET that Rubberduck already uses for its own unit test requirements. When this happens Rubberduck unit tests will no longer need such “test stubs”. Instead, the framework will generate them at run-time and make them work exactly as specified/configured by a unit test, and “just like that” VBA/VB6 suddenly becomes surprisingly close to being pretty much on par with professional, current-day IDE tooling.
The ITestStub interface simply formalizes the concept:
'@Exposed
'@Folder Tests.Stubs
'@ModuleDescription "An object that stubs an interface for testing purposes."
'@Interface
Option Explicit
'@Description "Gets the number of times the specified member was invoked in the lifetime of the object."
Public Property Get MemberInvokes(ByVal MemberName As String) As Long
End Property
'@Description "Gets a string representation of the object's internal state, for debugging purposes (not intended for asserts!)."
Public Function ToString() As String
End Function
A TestStubBase “base class” provides the common implementation mechanics that every class implementing ITestStub will want to use – the idea is to use a keyed data structure to track the number of times each member is invoked during the lifetime of the object:
'@Folder Tests.Stubs
Option Explicit
Private Type TState
MemberInvokes As Dictionary
End Type
Private This As TState
'@Description "Tracks a new invoke of the specified member."
Public Sub OnInvoke(ByVal MemberName As String)
Dim newValue As Long
If This.MemberInvokes.Exists(MemberName) Then
newValue = This.MemberInvokes.Item(MemberName) + 1
This.MemberInvokes.Remove MemberName
Else
newValue = 1
End If
This.MemberInvokes.Add MemberName, newValue
End Sub
'@Description "Gets the number of invokes made against the specified member in the lifetime of this object."
Public Property Get MemberInvokes(ByVal MemberName As String) As Long
If This.MemberInvokes.Exists(MemberName) Then
MemberInvokes = This.MemberInvokes.Item(MemberName)
Else
MemberInvokes = 0
End If
End Property
'@Description "Gets a string listing the MemberInvokes cache content."
Public Function ToString() As String
Dim MemberNames As Variant
MemberNames = This.MemberInvokes.Keys
With New StringBuilder
Dim i As Long
For i = LBound(MemberNames) To UBound(MemberNames)
Dim Name As String
Name = MemberNames(i)
.AppendFormat "{0} was invoked {1} time(s)", Name, This.MemberInvokes.Item(Name)
Next
ToString = .ToString
End With
End Function
Private Sub Class_Initialize()
Set This.MemberInvokes = New Dictionary
End Sub
With this small bit of infrastructure, the TestBindingObject class is a full-fledged mock object that can increment a counter whenever a member is invoked, and that can be injected as a dependency for anything that needs an IViewModel:
'@Folder Tests.Stubs
'@ModuleDescription "An object that can stub a binding source or target for unit tests."
Option Explicit
Implements ITestStub
Implements IViewModel
Implements INotifyPropertyChanged
Private Type TState
Stub As TestStubBase
Handlers As Collection
TestStringProperty As String
TestNumericProperty As Long
TestBindingObjectProperty As TestBindingObject
Validation As IHandleValidationError
End Type
Private This As TState
Public Property Get TestStringProperty() As String
This.Stub.OnInvoke "TestStringProperty.Get"
TestStringProperty = This.TestStringProperty
End Property
Public Property Let TestStringProperty(ByVal RHS As String)
This.Stub.OnInvoke "TestStringProperty.Let"
If This.TestStringProperty <> RHS Then
This.TestStringProperty = RHS
OnPropertyChanged Me, "TestStringProperty"
End If
End Property
Public Property Get TestNumericProperty() As Long
This.Stub.OnInvoke "TestNumericProperty.Get"
TestNumericProperty = This.TestNumericProperty
End Property
Public Property Let TestNumericProperty(ByVal RHS As Long)
This.Stub.OnInvoke "TestNumericProperty.Let"
If This.TestNumericProperty <> RHS Then
This.TestNumericProperty = RHS
OnPropertyChanged Me, "TestNumericProperty"
End If
End Property
Public Property Get TestBindingObjectProperty() As TestBindingObject
This.Stub.OnInvoke "TestBindingObjectProperty.Get"
Set TestBindingObjectProperty = This.TestBindingObjectProperty
End Property
Public Property Set TestBindingObjectProperty(ByVal RHS As TestBindingObject)
This.Stub.OnInvoke "TestBindingObjectProperty.Set"
If Not This.TestBindingObjectProperty Is RHS Then
Set This.TestBindingObjectProperty = RHS
OnPropertyChanged Me, "TestBindingObjectProperty"
End If
End Property
Private Sub OnPropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
Dim Handler As IHandlePropertyChanged
For Each Handler In This.Handlers
Handler.OnPropertyChanged Source, PropertyName
Next
End Sub
Private Sub Class_Initialize()
Set This.Stub = New TestStubBase
Set This.Handlers = New Collection
Set This.Validation = ValidationManager.Create
End Sub
Private Sub INotifyPropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
OnPropertyChanged Source, PropertyName
End Sub
Private Sub INotifyPropertyChanged_RegisterHandler(ByVal Handler As IHandlePropertyChanged)
This.Handlers.Add Handler
End Sub
Private Property Get ITestStub_MemberInvokes(ByVal MemberName As String) As Long
ITestStub_MemberInvokes = This.Stub.MemberInvokes(MemberName)
End Property
Private Function ITestStub_ToString() As String
ITestStub_ToString = This.Stub.ToString
End Function
Private Property Get IViewModel_Validation() As IHandleValidationError
Set IViewModel_Validation = This.Validation
End Property
This functionality will be extremely useful when testing the actual property bindings: for example we can assert that a method was invoked exactly once, and fail a test if the method was invoked twice (and/or if it never was).
There’s a lot more to discuss about unit testing in VBA with Rubberduck! I hope this article gives a good idea of how to get the best out of Rubberduck’s unit testing feature.
We’ve seen in UserForm1.Show what makes a Smart UI solution brittle, and how to separate the UI concerns from rest of the logic with the Model-View-Presenter (MVP) UI pattern. MVP works nicely with the MSForms library (UserForms in VBA), just like it does with its .NET Windows Forms successor. While the pattern does a good job of enhancing the testability of application logic, it also comes with its drawbacks: the View’s code-behind (that is, the code module “behind” the form designer) is still littered with noisy event handlers and boilerplate code, and the back-and-forth communication between the View and the Presenter feels somewhat clunky with events and event handlers.
Rubberduck’s UI elements are made with the Windows Presentation Foundation (WPF) UI framework, which completely redefines how everything about UI programming works, starting with the XML/markup-based (XAML) design, but the single most compelling element is just how awesome its data binding capabilities are.
We can leverage in VBA what makes Model-View-ViewModel (MVVM) awesome in C# without going nuts and writing a whole UI framework from scratch, but we’re still going to need a bit of an abstract infrastructure to work with. It took the will to do it and only costed a hair or two, but as far as I can tell this works perfectly fine, at least at the proof-of-concept stage.
This article is the first in a series that revolves around MVVM in VBA as I work (very much part-time) on the rubberduckdb content admin tool. There’s quite a bit of code to make this magic happen, so let’s kick this off with what it does and how to use it – subsequent articles will dive into how the MVVM infrastructure internals work. As usual the accompanying code can be found in theexamples repository on GitHub (give it a star, and fork it, then make pull requests with your contributions during Hacktoberfest next month and you can get a t-shirt, stickers, and other free stuff, courtesy of Digital Ocean!).
Overview
The code in the examples repository isn’t the reason I wrote this: I mentioned in the previous post that I was working on an application to maintain the website content, and decided to explore the Model-View-ViewModel pattern for that one. Truth be told, MVVM is hands-down my favorite UI pattern, by far. This is simply the cleanest UI code I’ve ever written in VBA, and I love it!
The app is work in progress, but the property and command bindings work!
The result is an extremely decoupled, very extensible, completely testable architecture where every user action (“command”) is formally defined, can be programmatically simulated/tested with real, stubbed, or faked dependencies, and can be bound to multiple UI elements and programmatically executed as needed.
MVVM Quick Checklist
These would be the rules to follow as far a relationships go between the components of the MVVM pattern:
View (i.e. the UserForm) knows about the ViewModel, but not the Model;
ViewModel knows about commands, but nothing about a View;
Exactly what the Model actually is/isn’t/should/shouldn’t be, is honestly not a debate I’m interested in – I’ll just call whatever set of classes is responsible for hydrating my ViewModel with data my “model” and sleep at night. What matters is that whatever you call the Model knows nothing of a View or ViewModel, it exists on its own.
Before we dive into bindings and the infrastructure code, we need to talk about the command pattern.
Commands
A command is an object that implements an ICommand interface that might look like this:
'@Folder MVVM.Infrastructure
'@ModuleDescription "An object that represents an executable command."
'@Interface
'@Exposed
Option Explicit
'@Description "Returns True if the command is enabled given the provided binding context (ViewModel)."
Public Function CanExecute(ByVal Context As Object) As Boolean
End Function
'@Description "Executes the command given the provided binding context (ViewModel)."
Public Sub Execute(ByVal Context As Object)
End Sub
'@Description "Gets a user-friendly description of the command."
Public Property Get Description() As String
End Property
In the case of a CommandBinding the Context parameter is always the DataContext / ViewModel (for now anyway), but manual invokes could supply other kinds of parameters. Not all implementations need to account for the ViewModel, a CanExecute function that simply returns True is often perfectly fine. The Description is used to set a tooltip on the target UI element of the command binding.
The implementation of a command can be very simple or very complex, depending on the needs. A command might have one or more dependencies, for example a ReloadCommand might want to be injected with some IDbContext object that exposes a SelectAllTheThings function and the implementation might pull them from a database, or make them up from hard-coded strings: the command has no business knowing where the data comes from and how it’s acquired.
Each command is its own class, and encapsulates the logic for enabling/disabling its associated control and executing the command. This leaves the UserForm module completely devoid of any logic that isn’t purely a presentation concern – although a lot can be achieved solely with property bindings and validation error formatters.
The infrastructure code comes with AcceptCommand and CancelCommand implementations, both useful to wire up [Ok], [Cancel], or [Close] dialog buttons.
AcceptCommand
The AcceptCommand can be used as-is for any View that can be closed with a command involving similar semantics. It is implemented as follows:
'@Exposed
'@Folder MVVM.Infrastructure.Commands
'@ModuleDescription "A command that closes (hides) a View."
'@PredeclaredId
Option Explicit
Implements ICommand
Private Type TState
View As IView
End Type
Private this As TState
'@Description "Creates a new instance of this command."
Public Function Create(ByVal View As IView) As ICommand
Dim result As AcceptCommand
Set result = New AcceptCommand
Set result.View = View
Set Create = result
End Function
Public Property Get View() As IView
Set View = this.View
End Property
Public Property Set View(ByVal RHS As IView)
GuardClauses.GuardDoubleInitialization this.View, TypeName(Me)
Set this.View = RHS
End Property
Private Function ICommand_CanExecute(ByVal Context As Object) As Boolean
Dim ViewModel As IViewModel
If TypeOf Context Is IViewModel Then
Set ViewModel = Context
If Not ViewModel.Validation Is Nothing Then
ICommand_CanExecute = ViewModel.Validation.IsValid
Exit Function
End If
End If
ICommand_CanExecute = True
End Function
Private Property Get ICommand_Description() As String
ICommand_Description = "Accept changes and close."
End Property
Private Sub ICommand_Execute(ByVal Context As Object)
this.View.Hide
End Sub
CancelCommand
This command is similar to the AcceptCommand in that it simply invokes a method in the View. This implementation could easily be enhanced by making the ViewModel track “dirty” (modified) state and prompting the user when they are about to discard unsaved changes.
'@Folder MVVM.Infrastructure.Commands
'@ModuleDescription "A command that closes (hides) a cancellable View in a cancelled state."
'@PredeclaredId
'@Exposed
Option Explicit
Implements ICommand
Private Type TState
View As ICancellable
End Type
Private this As TState
'@Description "Creates a new instance of this command."
Public Function Create(ByVal View As ICancellable) As ICommand
Dim result As CancelCommand
Set result = New CancelCommand
Set result.View = View
Set Create = result
End Function
Public Property Get View() As ICancellable
Set View = this.View
End Property
Public Property Set View(ByVal RHS As ICancellable)
GuardClauses.GuardDoubleInitialization this.View, TypeName(Me)
Set this.View = RHS
End Property
Private Function ICommand_CanExecute(ByVal Context As Object) As Boolean
ICommand_CanExecute = True
End Function
Private Property Get ICommand_Description() As String
ICommand_Description = "Cancel pending changes and close."
End Property
Private Sub ICommand_Execute(ByVal Context As Object)
this.View.OnCancel
End Sub
This gives us very good indications about how the pattern wants user actions to be implemented:
Class can have a @PredeclaredId annotation and expose a factory method to property-inject any dependencies; here a IView object, but a custom SaveChangesCommand would likely get injected with some DbContext service class.
All commands need a description; that description is user-facing as a tooltip on the binding target (usually a CommandButton).
CanExecute can be as simple as an unconditional ICommand_CanExecute = True, or as complex as needed (it has access to the ViewModel context); keep in mind that this method can be invoked relatively often, and should perform well and return quickly.
It’s a simple interface with a simple purpose: attach a command to a button. The EvaluateCanExecute method invokes the command’s CanExecute function and accordingly enables or disables the Target control.
By implementing all UI commands as ICommand objects, we keep both the View and the ViewModel free of command logic and Click handlers. By adopting the command pattern, we give ourselves all the opportunities to achieve low coupling and high cohesion. That is, small and specialized modules that depend on abstractions that can be injected from the outside.
Property Bindings
In XAML we use a special string syntax (“markup extensions”) to bind the value of, say, a ViewModel property, to that of a UI element property:
As long as the ViewModel implements INotifyPropertyChanged and the property fires the PropertyChanged event when its value changes, WPF can automatically keep the UI in sync with the ViewModel and the ViewModel in sync with the UI. WPF data bindings are extremely flexible and can also bind to static and dynamic resources, or other UI elements, and they are actually slightly more complex than that, but this captures the essence.
Obviously MVVM with MSForms in VBA isn’t going to involve any kind of special string syntax, but the concept of a PropertyBinding can very much be encapsulated into an object (and XAML compiles down to objects and methods, too). At its core, a binding is a pretty simple thing: a source, a target, and a method to update them.
Technically nothing prevents binding a target to any object type (although with limitations, since non-user code won’t be implementing INotifyPropertyChanged), but for the sake of clarity:
The binding Source is the ViewModel
The SourcePropertyPath is the name of a property of the ViewModel
The binding Target is the MSForms control
The binding TargetProperty is the name of a property of the MSForms control
Note that the SourcePropertyPath resolves recursively and can be a property of a property…of a property – as long as the string ultimately resolves to a non-object member.
The IBindingManager.BindPropertyPath method is pretty flexible and accepts a number of optional parameters while implementing sensible defaults for common MSForms controls’ “default property binding”. For example, you don’t need to specify a TargetProperty when binding a ViewModel property to a MSForms.TextBox: it will automatically binds to the Text property, but will accept to bind any other property.
The optional arguments are especially useful for custom data validation, but some of them also control various knobs that determine what and how the binding updates.
Value
Behavior
TwoWayBinding
Binding will update the source when the target changes, and will update the target when the source changes.
OneWayBinding
Binding will update the target when the source changes.
OneWayToSource
Binding will update the source when the target changes.
OneTimeBinding
Binding will only update the target once.
The BindingMode enum values
Value
Behavior
OnPropertyChanged
Binding will update when the bound property value changes.
OnKeyPress
Binding will update the source at each keypress. Only available for TextBox controls. Data validation may prevent the keypress from reaching the UI element.
OnExit
Binding will update the source just before target loses focus. Data validation may cancel the exit and leave the caret inside. This update source trigger is the most efficient since it only updates bindings when the user has finished providing a value.
The UpdateSourceTriggerenum values
Property Paths
The binding manager is able to recursively resolve a member path, so if your ViewModel has a ThingSection property that is itself a ViewModel with its own bindings and commands, that itself has a Thing property, know that the binding path can legally be “ThingSection.Thing“, and as long as the Source is the ViewModel object where a ThingSection property exists, and that the ThingSection porperty yields an object that has a Thing property, then all is good and the binding works. If ThingSection were to be Nothing when the binding is updated, then the target is assigned with a default value depending on the type. For example if ThingSection.Thing was bound to some TextBox1 control and the ThingSection property of the ViewModel was Nothing, then the Text property would end up being an empty string – note that this default value may be illegal, depending on what data validation is in place.
Data Validation
Every property binding can attach any IValueValidator implementation that encapsulates specialized, bespoke validation rules. The infrastructure code doesn’t include any custom validator, but the example show how one can be implemented. The interface mandates an IsValid function that returns a Boolean (True when valid), and a user-friendly Message property that the ValidationManager uses to create tooltips.
'@Folder MVVM.Example
Option Explicit
Implements IValueValidator
Private Function IValueValidator_IsValid(ByVal Value As Variant, ByVal Source As Object, ByVal Target As Object) As Boolean
IValueValidator_IsValid = Len(Trim$(Value)) > 0
End Function
Private Property Get IValueValidator_Message() As String
IValueValidator_Message = "Value cannot be empty."
End Property
The IsValid method provides you with the Value being validated, the binding Source, and the binding Target objects, which means every validator has access to everything exposed by the ViewModel; note that the method being a Function strongly suggests that it should not have side-effects. Avoid mutating ViewModel properties in a validator, but the message can be constructed dynamically if the validator is made to hold module-level state… although I would really strive to avoid making custom validators stateful.
While the underlying data validation mechanics are relatively complex, believe it or not there is no other step needed to implement custom validation for your property bindings: IBindingManager.BindPropertyPath is happy to take in any validator object, as long as it implements the IValueValidator interface.
Presenting Validation Errors
Without taking any steps to format validation errors, commands that can only execute against a valid ViewModel will automatically get disabled, but the input field with the invalid value won’t give the user any clue. By providing an IValidationErrorFormatter implementation when registering the binding, you get to control whether hidden UI elements should be displayed when there’s a validation error.
The ValidationErrorFormatter class meets most simple scenarios. Use the factory method to create an instance with a specific target UI element, then chain builder method calls to configure the formatting inline with a nice, fluent syntax:
Set Formatter = ValidationErrorFormatter.Create(Me.PathBox) _
.WithErrorBackgroundColor(vbYellow) _
.WithErrorBorderColor
Method
Purpose
Create
Factory method, ensures every instance is created with a target UI element.
WithErrorBackgroundColor
Makes the target have a different background color given a validation error. If no color is specified, a default “error background color” (light red) is used.
WithErrorBorderColor
Makes the target have a different border color given a validation error. If no color is specified, a default “error border color” (dark red) is used. Method has no effect if the UI control isn’t “flat style” or if the border style isn’t “fixed single”.
WithErrorForeColor
Makes the target have a different fore (text) color given a validation error. If no color is specified, a default “error border color” (dark red) is used.
WithErrorFontBold
Makes the target use a bold font weight given a validation error. Method has no effect if the UI element uses a bolded font face without a validation error.
WithTargetOnlyVisibleOnError
Makes the target UI element normally hidden, only to be made visible given a validation error. Particularly useful with aggregated formatters, to bind the visibility of a label and/or an icon control to the presence of a validation error.
The factory and builder methods of the ValidationErrorFormatter class.
The example code uses an AggregateErrorFormatter to tie multiple ValidationErrorFormatter instances (and thus possibly multiple different target UI controls) to the the same binding.
Value Converters
IBindingManager.BindPropertyPath can take an optional IValueConverter parameter when a conversion is needed between the source and the target, or between the target and the source. One useful value converter can be one like the InverseBooleanConverter implementation, which can be used in a binding where True in the source needs to bind to False in the target.
The interface mandates the presence of Convert and ConvertBack functions, respectively invoked when the binding value is going to the target and the source. Again, pure functions and performance-sensitive implementations should be preferred over side-effecting code.
'@Folder MVVM.Infrastructure.Bindings.Converters
'@ModuleDescription "A value converter that inverts a Boolean value."
'@PredeclaredId
'@Exposed
Option Explicit
Implements IValueConverter
Public Function Default() As IValueConverter
GuardClauses.GuardNonDefaultInstance Me, InverseBooleanConverter
Set Default = InverseBooleanConverter
End Function
Private Function IValueConverter_Convert(ByVal Value As Variant) As Variant
IValueConverter_Convert = Not CBool(Value)
End Function
Private Function IValueConverter_ConvertBack(ByVal Value As Variant) As Variant
IValueConverter_ConvertBack = Not CBool(Value)
End Function
Converters used in single-directional bindings don’t need to necessarily make both functions return a value that makes sense: sometimes a value can be converted to another but cannot round-trip back to the original, and that’s fine.
String Formatting
One aspect of property bindings I haven’t tackled yet, is the whole StringFormat deal. Once that is implemented and working, the string representation of the target control will be better separated from its actual value. And a sensible default format for some data types (Date, Currency) can even be inferred from the type of the source property!
Another thing string formatting would enable, is the ability to interpolate the value within a string. For example there could be a property binding defined like this:
And the NetAmountBox would read “USD$ 1,386.77” given the value 1386.77, and the binding would never get confused and would always know that the underlying value is a numeric value of 1386.77 and not a formatted string. Now, until that is done, string formatting probably needs to involve custom value converters. When string formatting works in property bindings, any converter will get invoked before: it’s always going to be the converted value that gets formatted.
ViewModel
Every ViewModel class is inherently application-specific and will look different, but there will be recurring themes:
Every field in the View wants to bind to a ViewModel property, and then you’ll want extra properties for various other things, so the ViewModel quickly grows more properties than comfort allows. Make smaller “ViewModel” classes by regrouping related properties, and bind with a property path rather than a plain property name.
Property changes need to propagate to the “main” ViewModel (the “data context”) somehow, so making all ViewModel classes fire a PropertyChanged event as appropriate is a good idea. Hold a WithEvents reference to the “child” ViewModel, and handle propagation by raising the “parent” ViewModel’s own PropertyChanged event, all the way up to the “main” ViewModel, where the handler nudges command bindings to evaluate whether commands can execute. One solution could be to register all command bindings with some CommandManager object that would have to implement IHandlePropertyChanged and would relieve the ViewModel of needing to do this.
Each ViewModel should implement at least two interfaces:
IViewModel, because we need a way to access the validation error handler and this interface makes a good spot for it.
INotifyPropertyChanged, to notify data bindings when a ViewModel property changes.
Here is the IViewModel implementation for the example code – the idea is really to expose properties for the view to bind, and we must not forget to notify handlers when a property value changes – notice the RHS-checking logic in the Property Let member:
'@Folder MVVM.Example
'@ModuleDescription "An example ViewModel implementation for some dialog."
'@PredeclaredId
Implements IViewModel
Implements INotifyPropertyChanged
Option Explicit
Public Event PropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
Private Type TViewModel
'INotifyPropertyChanged state:
Handlers As Collection
'CommandBindings:
SomeCommand As ICommand
'Read/Write PropertyBindings:
SourcePath As String
SomeOption As Boolean
SomeOtherOption As Boolean
End Type
Private this As TViewModel
Private WithEvents ValidationHandler As ValidationManager
Public Function Create() As IViewModel
GuardClauses.GuardNonDefaultInstance Me, ExampleViewModel, TypeName(Me)
Dim result As ExampleViewModel
Set result = New ExampleViewModel
Set Create = result
End Function
Public Property Get Validation() As IHandleValidationError
Set Validation = ValidationHandler
End Property
Public Property Get SourcePath() As String
SourcePath = this.SourcePath
End Property
Public Property Let SourcePath(ByVal RHS As String)
If this.SourcePath <> RHS Then
this.SourcePath = RHS
OnPropertyChanged "SourcePath"
End If
End Property
Public Property Get SomeOption() As Boolean
SomeOption = this.SomeOption
End Property
Public Property Let SomeOption(ByVal RHS As Boolean)
If this.SomeOption <> RHS Then
this.SomeOption = RHS
OnPropertyChanged "SomeOption"
End If
End Property
Public Property Get SomeOtherOption() As Boolean
SomeOtherOption = this.SomeOtherOption
End Property
Public Property Let SomeOtherOption(ByVal RHS As Boolean)
If this.SomeOtherOption <> RHS Then
this.SomeOtherOption = RHS
OnPropertyChanged "SomeOtherOption"
End If
End Property
Public Property Get SomeCommand() As ICommand
Set SomeCommand = this.SomeCommand
End Property
Public Property Set SomeCommand(ByVal RHS As ICommand)
Set this.SomeCommand = RHS
End Property
Public Property Get SomeOptionName() As String
SomeOptionName = "Auto"
End Property
Public Property Get SomeOtherOptionName() As String
SomeOtherOptionName = "Manual/Browse"
End Property
Public Property Get Instructions() As String
Instructions = "Lorem ipsum dolor sit amet, consectetur adipiscing elit."
End Property
Private Sub OnPropertyChanged(ByVal PropertyName As String)
RaiseEvent PropertyChanged(Me, PropertyName)
Dim Handler As IHandlePropertyChanged
For Each Handler In this.Handlers
Handler.OnPropertyChanged Me, PropertyName
Next
End Sub
Private Sub Class_Initialize()
Set this.Handlers = New Collection
Set ValidationHandler = ValidationManager.Create
End Sub
Private Sub INotifyPropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
OnPropertyChanged PropertyName
End Sub
Private Sub INotifyPropertyChanged_RegisterHandler(ByVal Handler As IHandlePropertyChanged)
this.Handlers.Add Handler
End Sub
Private Property Get IViewModel_Validation() As IHandleValidationError
Set IViewModel_Validation = ValidationHandler
End Property
Private Sub ValidationHandler_PropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
OnPropertyChanged PropertyName
End Sub
Nothing much of interest here, other than the INotifyPropertyChanged implementation and the fact that a ViewModel is really just a fancy word for a class that exposes a bunch of properties that magically keep in sync with UI controls!
View
In a Smart UI, that module is, more often than not, a complete wreck. In Model-View-Presenter it quickly gets cluttered with many one-liner event handlers, and something just feels clunky about the MVP pattern. Now, I’m trying really hard, but I can’t think of a single reason to not want UserForm code-behind to look like this all the time… this is absolutely all of it, there’s no cheating going on:
'@Folder MVVM.Example
'@ModuleDescription "An example implementation of a View."
Implements IView
Implements ICancellable
Option Explicit
Private Type TView
'IView state:
ViewModel As ExampleViewModel
'ICancellable state:
IsCancelled As Boolean
'Data binding helper dependency:
Bindings As IBindingManager
End Type
Private this As TView
'@Description "A factory method to create new instances of this View, already wired-up to a ViewModel."
Public Function Create(ByVal ViewModel As ExampleViewModel, ByVal Bindings As IBindingManager) As IView
GuardClauses.GuardNonDefaultInstance Me, ExampleView, TypeName(Me)
GuardClauses.GuardNullReference ViewModel, TypeName(Me)
GuardClauses.GuardNullReference Bindings, TypeName(Me)
Dim result As ExampleView
Set result = New ExampleView
Set result.Bindings = Bindings
Set result.ViewModel = ViewModel
Set Create = result
End Function
Private Property Get IsDefaultInstance() As Boolean
IsDefaultInstance = Me Is ExampleView
End Property
'@Description "Gets/sets the ViewModel to use as a context for property and command bindings."
Public Property Get ViewModel() As ExampleViewModel
Set ViewModel = this.ViewModel
End Property
Public Property Set ViewModel(ByVal RHS As ExampleViewModel)
GuardClauses.GuardExpression IsDefaultInstance, TypeName(Me)
GuardClauses.GuardNullReference RHS
Set this.ViewModel = RHS
InitializeBindings
End Property
'@Description "Gets/sets the binding manager implementation."
Public Property Get Bindings() As IBindingManager
Set Bindings = this.Bindings
End Property
Public Property Set Bindings(ByVal RHS As IBindingManager)
GuardClauses.GuardExpression IsDefaultInstance, TypeName(Me)
GuardClauses.GuardDoubleInitialization this.Bindings, TypeName(Me)
GuardClauses.GuardNullReference RHS
Set this.Bindings = RHS
End Property
Private Sub BindViewModelCommands()
With Bindings
.BindCommand ViewModel, Me.OkButton, AcceptCommand.Create(Me)
.BindCommand ViewModel, Me.CancelButton, CancelCommand.Create(Me)
.BindCommand ViewModel, Me.BrowseButton, ViewModel.SomeCommand
'...
End With
End Sub
Private Sub BindViewModelProperties()
With Bindings
.BindPropertyPath ViewModel, "SourcePath", Me.PathBox, _
Validator:=New RequiredStringValidator, _
ErrorFormat:=AggregateErrorFormatter.Create(ViewModel, _
ValidationErrorFormatter.Create(Me.PathBox).WithErrorBackgroundColor.WithErrorBorderColor, _
ValidationErrorFormatter.Create(Me.InvalidPathIcon).WithTargetOnlyVisibleOnError("SourcePath"), _
ValidationErrorFormatter.Create(Me.ValidationMessage1).WithTargetOnlyVisibleOnError("SourcePath"))
.BindPropertyPath ViewModel, "Instructions", Me.InstructionsLabel
.BindPropertyPath ViewModel, "SomeOption", Me.OptionButton1
.BindPropertyPath ViewModel, "SomeOtherOption", Me.OptionButton2
.BindPropertyPath ViewModel, "SomeOptionName", Me.OptionButton1, "Caption", OneTimeBinding
.BindPropertyPath ViewModel, "SomeOtherOptionName", Me.OptionButton2, "Caption", OneTimeBinding
'...
End With
End Sub
Private Sub InitializeBindings()
If ViewModel Is Nothing Then Exit Sub
BindViewModelProperties
BindViewModelCommands
Bindings.ApplyBindings ViewModel
End Sub
Private Sub OnCancel()
this.IsCancelled = True
Me.Hide
End Sub
Private Property Get ICancellable_IsCancelled() As Boolean
ICancellable_IsCancelled = this.IsCancelled
End Property
Private Sub ICancellable_OnCancel()
OnCancel
End Sub
Private Sub IView_Hide()
Me.Hide
End Sub
Private Sub IView_Show()
Me.Show vbModal
End Sub
Private Function IView_ShowDialog() As Boolean
Me.Show vbModal
IView_ShowDialog = Not this.IsCancelled
End Function
Private Property Get IView_ViewModel() As Object
Set IView_ViewModel = this.ViewModel
End Property
Surely some tweaks will be made over the next couple of weeks as I put the UI design pattern to a more extensive workout with the Rubberduck website content maintenance app – but having used MVVM in C#/WPF for many years, I already know that this is how I want to be coding VBA user interfaces going forward.
I really love how the language has had the ability to make this pattern work, all along.
Here we are again, some 580+ commits and 1000+ modified files later, with 10 contributors involved (with particular thanks to @MDoerner and @BZngr, and honorable mentions to @IvenBach and @testingoutgith1) in over 60 pull requests since the last release: time to look back at what was done and call it version 2.5.1! If you’ve been keeping up with pre-release builds, none of this is going to be news to you, but with over 9.1K downloads of v2.5.0 a lot of you seem to prefer to upgrade less often but more significantly, so here’s a timely recap.
But first, let’s get the known problems out of the way.
Known Issues
Making a VBIDE add-in means we can’t know or assume what our host application is going to be, and different hosts sometimes wire things up differently – and this can spell trouble under certain circumstances. Making a VBIDE add-in in .NET has even further implications: while it’s how we can extend a 64-bit VBE, it’s also causing various type cast errors/exceptions when other add-ins are loaded.
Possible Crash
The Visual Basic Editor has a peculiar way of loading its add-ins: Rubberduck’s (and any other VBIDE add-in’s) entry point is invoked by the VBE before the VBE has completely finished constructing itself – accessing the object model too early can throw COM exceptions that take down Rubberduck as it initializes.
Normally Rubberduck initializes itself, then proceeds to parse the project (if it’s an empty project then the bulk of that is Rubberduck loading everything defined in VBA7.DLL and the type library for the host application’s object model) – normally if the VBE isn’t ready for this yet, we bail out and don’t access any objects and the “Refresh” button says “Pending” instead of “Ready”, and by the time you manually run that command the VBE has finished initializing and the only annoyance is that the initial parse isn’t automatic.
But in certain host applications (Microsoft Access being a known one, but I’ve seen it happen in Excel as well, although not with a recent build), sometimes the VBE actually isn’t ready to take member calls against its own object model, and the result is a COM exception that is either caught and then Rubberduck says it can’t initialize, or thrown several layers deeper, uncaught, and then everything goes up in flames.
Loading Rubberduck manually from the VBE’s Add-Ins Manager is sure annoying, but is really the only 100% sure-shot way to load any VBIDE add-in with a properly initialized VBE, regardless of the host application. Note that the installer registers Rubberduck as a VBE add-in with the LoadBehavior flag set to load at startup. If Rubberduck blows up at startup or fails to initialize, consider editing this configuration to make it load manually (exception details should normally be logged for the first start-up).
Heavy on Memory (RAM)
Rubberduck has always used a lot of memory to keep all the code metadata handy and cache a lot of things to improve processing performance. Working on a large legacy project that generates lots of inspection results can grind the main thread of the host process to a halt as the toolwindow renders the many objects (whether the toolwindow is displayed or not).
Unless you are discovering Rubberduck with a new, empty VBA project, consider first reviewing the settings – can’t hurt to review them either way:
Disable “run inspections automatically on successful parse”, so that they only run if you explicitly refresh them from the Inspection Results toolwindow;
Set inspection severity to “Do not Show” for inspections that could produce thousands upon thousands of results, like “use meaningful names” if you’re into Hungarian Notation for example, or “use of bang operator” if that’s the only way you’re ever accessing recordset fields in Access;
Other general performance tips:
Rubberduck parses per-module, so when you leave a module after modifying it, trigger a parse – by the time you’re in the other module and have scrolled to where you want to be and are in that mindset, the modified module will have processed.
Reduce coupling: the more modules are inter-dependent, the more modifying a module requires re-resolving identifier references in the dependent modules.
Avoid complex grammar: bang operators, among other code constructs, are somewhat ambiguously defined and ultimately parse in two passes, with the first one failing. The standard member call syntax parses faster, in a single parser pass.
Undesirable Interactions
If you are using the free but rather old 32-bit MZ-Tools 3.x productivity add-in, this section shouldn’t be a concern. However MZ-Tools 8.x was rewritten from the ground up, ported from VB6 into .NET-land, and while its author Carlos Quintero took extraordinary steps to isolate MZ-Tools from other in-process .NET add-ins and has issued recommendations for Rubberduck to do the same, …there is still a chance the two add-ins bump into each other; if MZ wins, RD is essentially bricked.
MZ-Tools normally runs inside its own .NET AppDomain, except when hosted in AutoDesk products (Inventor, AutoCAD), which implement VBE initialization in a way that breaks MZ-Tools’ startup mechanics – up until recently it was assumed this collision only happens in AutoDesk hosts, but a recent support ticket involving Microsoft Access was filed and implicates interactions with MZ-Tools.
This issue manifests itself with InvalidCastException being thrown at various points, often during initialization, or later during parse: the exception message involves attempting to cast COM objects like Microsoft.Vbe.interop._VBProject into types such as VBClassicExtensibility.VBProjectClass, where VBClassicExtensibility is defined by MZ-Tools, not Rubberduck.
One thing that can be attempted to mitigate this problem, would be to set MZ-Tools to not load on start-up, and manually load it after Rubberduck has initialized… but sadly this cross-add-in confused COM marshaling is simply not supposed to happen given MZ-Tools’ AppDomain mechanics, and we don’t really have any solutions for this – same as we don’t really have any solution for cases where COM registrations are broken (e.g. when multiple Microsoft Office product versions are running side-by-side but were not installed in chronological order – that’s an officially unsupported scenario, per Microsoft).
As a result, using Rubberduck together with other .NET-based add-ins cannot be considered a completely fail-safe scenario, and we have to treat this as a “known issue” here, and the work-around sucks and boils down to “drop other add-ins, or drop Rubberduck”. This is actually probably true at various degrees of all .NET-based VBIDE add-ins.
On the bright side, we have taken several steps in this release cycle to prepare the ground not only to get Rubberduck to build correctly in the latest & greatest Visual Studio 2019, but also to get most of our build process ready for .NET Core – so when .NET Core 5 is released in a few weeks, we can try to get Rubberduck to run on the shiny new Core framework, which theoretically makes AppDomain completely moot, and so we have very little incentive to work on getting Rubberduck to load its own AppDomain the way MZ-Tools does: if we can make Rubberduck build and run on .NET Core 5, then this problem should simply disappear… in theory.
Enhancements & New Features
This release does not introduce any new top-level Rubberduck features, but makes a number of very useful user-facing additions nonetheless, on top of the many under-the-hood enhancements made this cycle.
Surfacing Annotations
One of the most useful and powerful features of Rubberduck, annotations are special comments that use a particular but relatively simple syntax – these are all grammatically valid:
'@AnnotationName("text")
'@AnnotationName "text"
'@AnnotationName("text", 123) : there can be comments here
'@AnnotationName "text", 123
'@AnnotationName Identifier1, Identifier2, ...IdentifierN
While the syntax itself is reasonably simple to use, the problem was that unless you knew every supported annotation, well then the @AnnotationName part kind of had to be a guess.
Rubberduck uses these annotations for various purposes, from identifying Rubberduck test modules to keeping hidden module/member attributes in sync with these comments (this includes the ability to document and literally map Excel hotkeys using VBA comments). You can read everything we’ve documented about them on the project’s website.
In Rubberduck 2.5.1.x builds, we finally get new commands in the code pane and Code Explorer context menus, that bring up a dialog that gives us all the options to easily and safely annotate everything that can be annotated, using the correct syntax and arguments every time:
Select the annotation to add, and supply the argument values. Magic!
Adding an @Ignore annotation to ignore a specific inspection, can now be done without needing to know the exact name we decided to call that inspection class in Rubberduck’s source code!
Encapsulate Field Enhancements
This particular refactoring has seen a terrific enhancement that makes it very easy to cleanly and quickly turn a set of public fields into Property Get/Let members, with a Private Type TClassName and a module-scope Private this As TClassName instance variable – and all properties automatically reading/writing from it. You can see this feature in action in the previous article.
The new Wrap fields in Private Type functionality leverages the very useful Private Type pattern.
Unit Testing
The Test Explorer now makes it easier to ignore one or more specific selected tests, or all tests under a given category/group, by exposing the context menu commands that add or remove the @TestIgnore annotation as appropriate; having this command in the Test Explorer makes it possible to annotate a test method while a completely different and unrelated module is maximized in the code editor.
Hmm, …these icons are out of control, aren’t they… expect that to change soon-ish…
Running tests while results are regrouped by outcome is still a known issue (tests run painfully slow because the UI thread is busy re-sorting and re-rendering the list every time a test finishes running), but everything works much more smoothly when the tests are regrouped any other way.
Code Inspections
Ok the logic for that is currently broken on the website (working on that… somewhat) so this is much harder than it will be in the future when I’ll just look at the [New] tab on the inspections page of the website and every inspection that is in [next] but not in [main] will be listed right there. From skimming through every pull request merged since the last release:
Function return value is always discarded inspection is the old “return value not used” logic targeting the function itself, when none of the call sites capture the function’s return value.
Implicitly typed const inspection was added to flag Const declarations without an As clause to specify an explicit type.
Assignment not used inspection now correctly handles an assignment that is overridden in the next statement but first read in the RHS expression of the assignment.
Not user-facing but critically important nonetheless, is all the behind-the-scenes work done to simplify inspecting VBA code as much as possible. This cycle saw a tremendous amount of technical debt paid in the code inspections department, that pave the way for future enhancements like, say, having the ability to run inspections per-module; as the number of implemented inspections continues to grow, the ability to scope inspections in a more granular way is going to be very useful for our plan to eventually report inspection results in a custom code pane, with colored squiggly lines (that’s v3.x stuff, though).
Applying Quick-Fixes
The Code Inspections toolwindow has been updated with a context menu that makes it much simpler to apply a quick-fix to one or more inspection results; all available quick-fixes appear in the context menu, each with various options to apply to the selection. This menu is also shown by clicking the “Fix” drop-down menu from the toolwindow’s toolbar.
The weird “fix all occurrences in procedure/module/project” link buttons in the bottom panel are now gone, completely replaced with a more flexible menu system.
Code Explorer Enhancements
The Code Explorer toolwindow context menu now includes a move to folder command to easily organize your project components, and there’s a new setting to enable drag and drop in Code Explorer (disabled by default):
A new setting enables dragging & dropping capabilities in the Code Explorer toolwindow.
With that setting enabled, you can now move a code file to an existing folder, simply by dragging it from its location and dropping it onto a folder node! The setting was made to require being explicitly enabled, to avoid discovering that feature by accidentally dragging a code file somewhere (that did happen in beta/testing).
Website Integration
You may have noticed rubberduckbva.com is “under reconstruction”. Before that, the site’s content was mostly static, with only the inspections list assembled from content parsed from the Rubberduck.CodeAnalysis.xml, downloaded periodically off GitHub through unauthenticated REST API requests. That worked relatively well until a spike in traffic occurred following the release of Excel Insights, a book collectively authored by 24 Microsoft MVP Award recipients, including myself: suddenly a bug in the caching mechanism became very apparent when the site’s home page started getting served as a wonderful HTTP 500 error page.
Since the website hosting came with a SQL Server database that I wasn’t using, I decided to start using it and make the site pull the content from there rather than directly off the GitHub API. I wrote a small console application, got myself a private API key to make properly authenticated REST API requests, and now there’s a scheduled task running on a virtual machine in my garage, that runs this application every 30 minutes to update the installer asset download counts and verify whether the XML documentation assets are up-to-date for the latest pre-release build, and then proceeds to parse the XML docs and generate/update the database records: the website simply pulls the data from the database at every request, and now the website couldn’t bust GitHub’s REST API limits even if it tried.
Documenting Rubberduck is challenging: there are a lot of features, and there isn’t really any user guide that’s constantly being kept up-to-date. The wiki on the repository is terribly outdated in several parts, and the feature announcements on this blog are nice when you’re following the project along its journey, but in a dream world using Rubberduck would be content found on the website, and contributing to Rubberduck would be content found in the repository’s wiki.
Parsing the xml-docs into website content is a step in that direction. Nobody wants to maintain documentation, but xml-doc comments are part of the source code, and we even put source code analyzers in place that will break the build if we try to introduce an inspection, quick-fix, or annotation, without properly documenting it with xml-docs.
Every single inspection, quick-fix, and annotation has thorough documentation, including code examples that may span multiple modules. But best of all, every single page generated from source code includes an “edit this page” link that points to a GitHub page where you can literally edit the xml documentation for the inspection you were looking at (and review its source code if you like – it’s the same file!) – and just like that, all you need to contribute to Rubberduck (yes, single-character typo fixes and additional useful code examples are welcome!) is a GitHub login!
Every page generated from xml documentation includes an “Edit this page” link at the bottom.
Because of how the request routing on the website was setup, it was easy to make Rubberduck link in-app inspection results to this website content – you can now click a URL at the bottom of the inspection results toolwindow (this will likely change one way or another in the future) to bring up the details page with the xml documentation and code examples:
The URL at the bottom of the adjustable panel (it’s possible you need to scroll the content or make the bottom panel tall enough to show it) opens that URL in a new tab in whatever your default browser is.
The in-app content exists as localized resources, lovingly translated by our international contributors; the website content however, is only available in English, because we’re absolutely not going to start translating XML comments in the source code. But the processed content actually resides in a database, so it wouldn’t be impossible to eventually localize it at that level, as well – we’re just not there yet at all at the moment.
The website content is often different than the in-app content, and over time it should be expected to grow more and more in-depth, thorough and descriptive.
The revamped rubberduckvba.com domain will ultimately span 3 sites, including api.rubberduckvba.com, which will eventually expose REST endpoints for various purposes, including Rubberduck’s “check for newer version at startup” feature; for example something like api.rubberduckvba.com/indenter.json that might accept some VBA code in a JSON object in the request’s body, and respond with a JSON object in the response body containing the indented VBA code. Or api.rubberduckvba.com/inspect.json that might also accept some VBA code (presumably along with some metadata about the module type) in a JSON object in the request’s body, but could respond with a JSON object representing all inspection results for it. It’s still all just brewing ideas at this stage. The other subdomain, admin.rubberduckvba.com, is going to host a web-based, GitHub-authenticated version of the VBA program I’m going to present in my next article: a tool for managing and editing most of the website’s content.
Moving Forward
Rubberduck is becoming a pretty mature code base and has an ever-increasingly better abstracted internal framework/API to understand and manipulate VBA code. The project now builds with the latest version of Microsoft Visual Studio 2019, and we’re hoping COM Automation support in .NET Core 5 will allow us to build an increasing number of the project’s components with it; I’m thinking the “main” type library is better off under the old tech, but I’ll be more than happy to be proven wrong here!
A rough roadmap for v2.5.1.x might include…
More resolver capabilities unlocked by fully leveraging our internal ITypeLib API
Syntax-highlighted preview of the changes for all refactorings (and quick-fixes?)
Some Code Path Analysis API, to help implement the more complex inspection ideas
The Moq wrapper mocking framework
Block Completion, maybe
Anything else anyone feels like contributing to the project!
The goal for the rest of the 2.x cycle is to prepare everything that needs to happen in order to implement our own custom code editor window – giving us full, complete control over every single token and everything that can possibly happen in that custom code pane. We’re talking code folding, custom theming, that kind of thing.
‘Main’ vs ‘Master’ – Why it Matters
You may have noticed (or not) that the website is now labeling “main” the branch formerly known as “master”. As a French native, “master/slave” terminology in any non-actual master/slave context has always sounded a bit weird to me, but I’m a white man in North America (although not in the US) and I get the luxury to read these words and decide that they don’t affect me, and reflecting on the events of this summer has taught me that this is part of what white privilege is.
I don’t do political & editorial commenting, I prefer to leave that space to others – but I warmly recommend watching 13th on Netflix and, if you can handle it, When They See Us. Black lives matter, it’s simple – and no, it doesn’t say “but white lives don’t” anywhere between the lines.
So yes, we’re going to be taking steps to alter the language in Rubberduck a bit in this cycle. The “master” branch will be renamed to “main”, yes, but we’ll also come up with a better term for “white-listing” identifier names. It won’t stop racism, no, indeed. But it won’t hurt anyone, either.
Unless you’re hosted in Access, your VBA project doesn’t have access to a database engine. If you’re in Excel, it’s easy to treat the host workbook as a database and each worksheet as a table. While we can build an application that uses Excel worksheets to store data, we probably shouldn’t do that. The reasons are many, but primarily (pun …yeah, intended), we want to be able to establish bullet-proof referential integrity between records/tables; while Excel is great for many things, it’s useless for that: it’s the job of a relational database system (RDBMS), not that of any worksheet software, no matter how powerful. Power Query is very much worth looking into, but if you’re building a small CRUD (Create/Read/Update/Delete) application in VBA, you’ll want VBA code responsible for the data access – enter ADODB, …and every pitfall that comes with it.
Querying a database with ADODB is easy: just set up a connection, open it, then execute whatever SQL statement you need through the Connection, and you get the results in a Recordset object:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "ConnectionString"
Dim rs As ADODB.Recordset
Set rs = conn.Execute("SELECT Field1, Field2 FROM Table1")
'...
rs.Close
conn.Close
That is great for one-timer, ad-hoc queries: things quickly get messy when you start needing multiple queries, or when your SQL statement needs to be invoked repeatedly with different values:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "ConnectionString"
Dim i As Long
For i = 1 To 10
Dim rs As ADODB.Recordset
Set rs = conn.Execute("SELECT Field1, Field2 FROM Table1 WHERE Field3 = " & i)
'...
rs.Close
Next
conn.Close
This right here – WHERE SomeField = " & i, is making the database engine work harder than it needs to… and it’s costing server-side performance, because as far as the engine knows, it’s getting a different query every time – and thus computes the same execution plan over and over, every time… when it could just be reusing it. Databases are smart. Like, wicked smart… but yeah we still need to ask for the right thing!
Compare to something like this:
Const sql As String = "SELECT Field1, Field2 FROM Table1 WHERE Field3 = ?"
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "ConnectionString"
Dim i As Long
For i = 1 To 10
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = sql
cmd.Parameters.Append cmd.CreateParameter(Type:=adInteger, Value:= i)
Dim rs As ADODB.Recordset
Set rs = cmd.Execute
'...
rs.Close
Next
conn.Close
Oh my, so much more code, so little gain – right?
Using ADODB.Command when queries involve a WHERE (and/or VALUES) clause and user-provided (directly or not) values is not only more efficient (the cached execution plan is reused because the command string is identical every time), it’s also more secure. Concatenating user inputs into SQL command strings is a common rookie mistake, and it’s a practice that is way more widespread than it should be (regardless of the language, paradigm, or platform); your code becomes vulnerable to SQL Injection Attacks – something that may or may not be in your threat model, but that inevitably turns into… easily avoidable bugs: think of what might happen if a user entered O'Connor in that LastName field. If you’re thinking “oh that’s easy! I’ll just double-up single quotes, and fixed!“, then you’re playing a needlessly exhausting game of cat-and-mouse with the next thing that will break your clever escaping: the mouse wins.
Abstract thoughts
Much simpler to just use an ADODB.Command every time, and when you need it parameterized, to Append any number of ADODB.Parameter objects to its Parameters collection. Except, it does make a lot of code to write, every time.
What do we do when we see repetitive patterns in code? If you’re thinking “we put it in a function!” then you’re thinking abstraction and that’s exactly the right train of thought.
We’re just going to take this abstraction… and make it an object. Then think of what objects it needs in order to do its job, and abstract these objects behind interfaces too, and take these abstractions in as constructor parameters of our Create “static” factory method. Rinse & repeat until all dependencies are property-injected and all responsibilities are nicely encapsulated into their own classes. It was fun!
I wrote an original version of this functionality little while ago – you can find the original version on Code Review, and see how different/similar it is to this simplified/improved version in our Examples repositoryon GitHub.
The original was just an ADODB wrapper class though, couldn’t really be unit-tested, and was annoying to maintain because it felt very repetitive. This version is separating the type mappings from the parameter-providing logic, which makes configuring these mappings is done through an object that’s solely responsible for these mappings; it also separates the command from the connection, and abstracts away that connection enough to enable unit testing and cover quite a large part of the API – but most importantly, this version exposes adequate abstractions for the calling code to use and stub in its own unit tests.
VBA code written with this API (and the principles it demonstrates) can easily be fully testable, without ever actually hitting any database.
I can do this in the immediate pane:
?UnitOfWork.FromConnectionString("connection string").Command.GetSingleValue("SELECT Field1 FROM Table1 WHERE Id=?", 1)
I mean, it’s a contrived example, but with a valid connection string, query, and arguments, that’s all we need to get an actual parameterized ADODB command sending that 1 as an actual ADODB parameter, …and the following debug output:
Begin connect...
Connect completed. Status: 1
Begin transaction completed.
Begin execute...
Execute completed, -1 record(s) affected.
{whatever value was in Field1}
Rollback transaction completed.
Disconnect completed. Status: 1
I made DbConnection listen in on whatever events the ADODB connection is firing, pending the implementation of an adapter to expose some IDbConnectionEvents members – the idea is to end up with client code that can inject its own callbacks and do things like log such messages. In the meantime Debug.Print statements are producing this debug output, but that’s it’s an implementation detail: it doesn’t publicly expose any of these events. It couldn’t, either: the rest of the code needs to work with the IDbConnection interface, and interfaces unfortunately can’t expose events in VBA.
SecureADODB
Some might call it layered spaghetti. Others call it lasagna. I call it well-abstracted code that reads and maintains like a charm and provably works as intended. There is nothing, absolutely nothing wrong with having many class modules in a VBA project: the only problem is… well, the VBE itself:
Project Explorer is making OOP rather painful. In fact it makes any kind of modularization painful.
Code Explorer makes the VBE more OOP-friendly: now you can have folders regrouping modules by functionality rather than just by module type.
Nice, rich APIs involve many related objects, interfaces, methods – members that make up the object model the API’s client code will be working with. As long as we can keep all these classes organized, there’s no problem having many of them.
Before we look at the implementation, let’s review the interfaces and the overall structure.
Only two interfaces aren’t being stubbed for unit tests. IUnitOfWork because as the top-level object nothing in the object model consumes it. It is needed though, because client code can inject it as a dependency of some FooRepository class, and then tests can provide it with a StubUnitOfWork that implements IUnitOfWork.
The other “façade” interface is ITypeMap. This one isn’t really needed (neither is the predeclared instance of AdoTypeMappings or its Default factory method), something felt wrong with the client code without it. While the class is essentially just a dictionary / literally a map, there’s something rather elegant about depending on an ITypeMap rather than some Scripting.Dictionary.
The two dark blue interfaces are abstract factory interfaces, each with a “real” and a “stub” implementation for tests: these are very simple classes whose entire purpose is to create an object of a particular type.
If we consider IParameterProvider an implementation detail of IDbCommandBase, that leaves us with only the core stuff: IDbCommandBase, IDbCommand, and IDbConnection – everything else just revolves around these.
DbCommandBase
The old SqlCommand code had two sets of commands: “Execute” for methods you could pass a Connection to, and “QuickExecute” for methods that created a connection on-the-spot. I decided to split the two behaviors into two distinct implementation of the same interface, and that’s how I ended up with DefaultDbCommand and AutoDbCommand. As I was cleaning up the two new classes, I had to notice these two classes needed a number of common bits of functionality… as would any other implementation of IDbCommand.
In a language that supports inheritance, I would probably make the two classes inherit a third abstract “base” class where I’d implement the IDbCommand interface. In VBA, we can’t derive a class from another, or inherit members from another class: inheritance is flat-out unavailable. There’s an alternative though, and it’s arguably even better than inheritance: composition. We can put the common functionality in a third class, and then have the two implementations take an instance of that “base” class as we would any other dependency – effectively achieving what we wanted out of inheritance, but through composition.
Code is said to be “decoupled” when none of its concrete components are inter-dependent, as is apparent with the solid black “depends on” arrows here. Decoupled components can easily be swapped for other implementations, like …test stubs.
What’s wrong with inheritance?
Don’t get me wrong, inheritance is very cool: with an abstract class you can have templated methods, where a method in the base class (typically a method that implements some interface member) invokes an abstract or virtual method (typically with protected scope) that the inherited class must override and provide an implementation for. Rubberduck uses this pattern in quite a few places (inspections, notably). Without inheritance, it’s just not something that’s possible.
Inheritance is described as a “is a” relationship, while composition is more of a “has a” relationship. This is important, because when the only consideration weighting in favor of inheritance is the need for two classes to share some functionality, it’s exactly why inheritance should not be used.
Decoupling FTW
The “base” class appeared as a need to have a place for IDbCommand implementations to access shared functionality. I wanted to return disconnected recordsets, and retrieving the value of the first field of the first record of a recordset isn’t something that’s glaringly implementation-specific. The other piece of functionality I needed, was a function that creates the ADODB.Command object and adds the parameters.
Because I wanted this class to create the ADODB.Command, I needed it to be able to turn a Variant into an ADODB.Parameter through some mapping, and since I didn’t want my class to be necessarily coupled with that mapping, or anything remotely related to configuring ADODB parameters… I’m property-injecting an IParameterProvider dependency:
Public Function Create(ByVal provider As IParameterProvider) As IDbCommandBase
Errors.GuardNonDefaultInstance Me, DbCommandBase
Errors.GuardNullReference provider
Dim result As DbCommandBase
Set result = New DbCommandBase
Set result.ParameterProvider = provider
Set Create = result
End Function
Validating the command string / arguments
Since the commands are given an SQL command string to execute, and a ParamArray array of arguments that should have the same number of items as there are ? ordinal parameters in the SQL command string, we have an opportunity to catch a missing or extraneous argument before we even send the command string to the database server. And because this validation logic would have to be the same regardless of what IDbCommand implementation we’re looking at, DbCommandBase makes the best place to put it.
This implementation is probably too naive for a number of edge cases, but sufficient for most: we’re simply counting the number of ? characters in the sql string, and comparing that with the number of elements in the args array. We need to handle errors here, because if the args array is empty, evaluating UBound(args) and/or LBound(args) will throw a “subscript out of range” run-time error 9.
Public Function ValidateOrdinalArguments(ByVal sql As String, ByRef args() As Variant) As Boolean
On Error GoTo CleanFail
Dim result As Boolean
Dim expected As Long
expected = Len(sql) - Len(Replace(sql, "?", vbNullString))
Dim actual As Long
On Error GoTo CleanFail 'if there are no args, LBound/UBound are both out of bounds
actual = UBound(args) + (1 - LBound(args))
CleanExit:
result = (expected = actual)
ValidateOrdinalArguments = result
Exit Function
CleanFail:
actual = 0
Resume CleanExit
End Function
Getting a disconnected Recordset
If we created a database connection, issued a command against it, and received the recordset from ADODB.Command.Execute, and then we close the connection and return that recordset, then the calling code can’t use the data anymore: a connected recordset only works if the calling code owns the connection. So we need a way to issue a disconnected recordset, while still using an ADODB.Command. The way to do this, is to pass the command as the Source argument to Recordset.Open, and to use a static, client-side cursor:
Private Function GetDisconnectedRecordset(ByVal cmd As ADODB.Command) As ADODB.Recordset
Errors.GuardNullReference cmd
Errors.GuardNullReference cmd.ActiveConnection
Dim result As ADODB.Recordset
Set result = New ADODB.Recordset
result.CursorLocation = adUseClient
result.Open Source:=cmd, CursorType:=adOpenStatic
Set result.ActiveConnection = Nothing
Set GetDisconnectedRecordset = result
End Function
Getting a single value result
With functions to validate the parameters, create commands and get a disconnected recordset, we have everything we need for IDbCommand implementations to do their job, but if we leave it like this, we’ll end up with all implementations copying the logic of IDbCommand.GetSingleValue: best have that logic in DbCommandBase and avoid as much repetition as possible.
Private Function GetSingleValue(ByVal db As IDbConnection, ByVal sql As String, ByRef args() As Variant) As Variant
Errors.GuardEmptyString sql
Dim cmd As ADODB.Command
Set cmd = CreateCommand(db, adCmdText, sql, args)
Dim results As ADODB.Recordset
Set results = GetDisconnectedRecordset(cmd)
GetSingleValue = results.Fields.Item(0).value
End Function
Creating the command
A few things can go wrong when creating the ADODB.Command object: we need an ADODB.Connection that’s open, and the parameters must be valid. Since we’re not executing the command just yet, we don’t have to worry about everything that could go wrong actually executing the command string and processing the parameters on the server. So the strategy here is to guard against invalid inputs as much as possible, and then to handle errors when we add the parameters, and return the Command object with whatever parameters were successfully added. We don’t need to try salvaging the rest of the parameters if one blows up, since that failing parameter will fail command execution anyway, but there isn’t much we can do about it, other than perhaps throw an error and have the caller not even try to run the command – but here I decided that the server-side errors would be more useful than any custom “invalid parameter” error.
Note that the ADODB.Command object is actually created by the method-injected IDbConnection dependency. This creates a seam between the class and ADODB, despite the inherent coupling with the ADODB.Command type: it makes the command’s ActiveConnection an implementation detail of IDbConnection.CreateCommand, and that’s all I needed to make this method work with a stub connection that isn’t actually connecting to anything:
Private Function CreateCommand(ByVal db As IDbConnection, ByVal commandType As ADODB.CommandTypeEnum, ByVal sql As String, ByRef args() As Variant) As ADODB.Command
Errors.GuardNullReference db
Errors.GuardEmptyString sql
Errors.GuardExpression db.State <> adStateOpen, message:="Connection is not open."
Errors.GuardExpression Not ValidateOrdinalArguments(sql, args), message:="Arguments supplied are inconsistent with the provided command string parameters."
Dim cmd As ADODB.Command
Set cmd = db.CreateCommand(commandType, sql)
On Error GoTo CleanFail
Dim arg As ADODB.Parameter
For Each arg In this.ParameterProvider.FromValues(args)
cmd.parameters.Append arg
Next
CleanExit:
Set CreateCommand = cmd
Exit Function
CleanFail:
Resume CleanExit
End Function
DbCommand
As mentioned before, there are two implementations for the IDbCommand interface: one that creates and owns its own IDbConnection, the other that takes it in as a dependency.
This abstraction represents an object that can take an SQL statement and parameters, and return the result(s) to its caller.
DefaultDbCommand receives its IDbConnection dependency through property injection in its Create factory method.
AutoDbCommand takes a connection string and an IDbConnectionFactory instead.
UnitOfWork uses a DefaultDbCommand because the unit of work needs to own the connection, but AutoDbCommand could be used instead of a unit of work, if we just need a quick SELECT and no transaction.
Abstract Factory
IDbConnectionFactory is an Abstract Factory here. This is needed, because unit tests need to be able to inject a stub factory that produces stub connections: an abstract factory is a factory interface that creates objects of a type that is also an abstraction – in this case, IDbConnectionFactory.Create returns an IDbConnection object. Implementing this factory class is exactly as simple as you’d think – here’s DbConnectionFactory:
'@Exposed
'@Folder("SecureADODB.DbConnection")
'@ModuleDescription("An implementation of an abstract factory that creates DbConnection objects.")
Option Explicit
Implements IDbConnectionFactory
Private Function IDbConnectionFactory_Create(ByVal connString As String) As IDbConnection
Set IDbConnectionFactory_Create = DbConnection.Create(connString)
End Function
And here’s StubDbConnectionFactory:
'@Folder("Tests.Stubs")
'@ModuleDescription("A stub acting as a IDbConnectionFactory implementation.")
Option Explicit
Implements IDbConnectionFactory
Private Type TInvokeState
CreateConnectionInvokes As Long
End Type
Private this As TInvokeState
Private Function IDbConnectionFactory_Create(ByVal connString As String) As IDbConnection
this.CreateConnectionInvokes = this.CreateConnectionInvokes + 1
Set IDbConnectionFactory_Create = New StubDbConnection
End Function
Public Property Get CreateConnectionInvokes() As Long
CreateConnectionInvokes = this.CreateConnectionInvokes
End Property
The test stub is more “complex” because it tracks method invocations, so that tests can know whether & how many times any given member was invoked during a test run.
The Abstract Factory pattern is very useful with Dependency Injection: it gives us an abstraction to inject when a class needs a dependency that just cannot be injected when the object is created – the alternative would be tight coupling: if we weren’t injecting a connection factory, then the command class would’ve had to be the one invoking DbConnection.Create – tightly coupling it with the DbConnection class and instantly making unit testing impossible. An abstract factory removes the coupling and allows unit tests to inject an alternative/stub implementation of the factory that creates StubDbConnection objects.
Wrapping it all up
AutoDbConnection can very well be consumed as-is by the client code:
Dim results As ADODB.Recordset
Set results = AutoDbConnection.Create(connString, New DbConnectionFactory, DbCommandBase.Create(AdoParameterProvider.Create(AdoTypeMappings.Default))).Execute(sql)
The only problem is that, well, the dependencies need to be resolved somehow, and that means the client code is now responsible for wiring everything up. While each component has a clear purpose, explicitly creating all these objects quickly gets old and redundant: we need an object that simplifies this – enter IUnitOfWork, and now we can use this much simpler code:
Dim results As ADODB.Recordset
Set results = UnitOfWork.FromConnectionString(connString).Command.Execute(sql)
Unit of Work is a design pattern that encapsulates a transaction: each individual operation can succeed or fail, and the unit of work either succeeds or fails as a whole. These notions are abstracted in the IUnitOfWork interface:
'@Folder("SecureADODB.UnitOfWork")
'@ModuleDescription("Represents an object encapsulating a database transaction.")
'@Interface
'@Exposed
Option Explicit
'@Description("Commits the transaction.")
Public Sub Commit()
End Sub
'@Description("Rolls back the transaction.")
Public Sub Rollback()
End Sub
'@Description("Creates a new command to execute as part of the transaction.")
Public Function Command() As IDbCommand
End Function
When a UnitOfWork is created, it initiates a database transaction. When it is destroyed before the transaction is committed, the transaction gets rolled back and from the database’s point of view, it’s like nothing happened.
Transaction?
If you’re unfamiliar with database transactions, there’s an easy example to illustrate what they do: imagine you have an Accounts table, and you’re processing a transfer – you need to UPDATE the record for the source account to deduct the transfer amount, then UPDATE the record for the destination account to add the transferred amount. In a happy world where everything goes well that would be the end of it… but the world is a cruel place, and assuming the 1st command goes through, nothing guarantees nothing will blow up when sending the 2nd command. Without transactions, the funds would simply vanish: they’re gone from the first account, and they were never added to the second account. With a transaction, we can rollback everything when the 2nd operation completes, no funds vanish and the data is exactly the way it was before the transaction started.
Again, the implementation is pretty straightforward – the only peculiarity is that the class has two factory methods – one named Create that takes all the dependencies in, and another named FromConnectionString that conveniently wires up a default set of dependencies (and then passes them to the Create method to avoid duplicating code).
'@Folder("SecureADODB.UnitOfWork")
'@ModuleDescription("An object that encapsulates a database transaction.")
'@PredeclaredId
'@Exposed
Option Explicit
Implements IUnitOfWork
Private Type TUnitOfWork
Committed As Boolean
RolledBack As Boolean
Connection As IDbConnection
CommandFactory As IDbCommandFactory
End Type
Private this As TUnitOfWork
'@Description("Creates a new unit of work using default configurations.")
'@Ignore ProcedureNotUsed
Public Function FromConnectionString(ByVal connString As String) As IUnitOfWork
Dim db As IDbConnection
Set db = DbConnection.Create(connString)
Dim provider As IParameterProvider
Set provider = AdoParameterProvider.Create(AdoTypeMappings.Default)
Dim baseCommand As IDbCommandBase
Set baseCommand = DbCommandBase.Create(provider)
Dim factory As IDbCommandFactory
Set factory = DefaultDbCommandFactory.Create(baseCommand)
Set FromConnectionString = UnitOfWork.Create(db, factory)
End Function
'@Inject: just an idea.. see #https://github.com/rubberduck-vba/Rubberduck/issues/5463
Public Function Create(ByVal db As IDbConnection, ByVal factory As IDbCommandFactory) As IUnitOfWork
Errors.GuardNonDefaultInstance Me, UnitOfWork
Errors.GuardNullReference factory
Errors.GuardNullReference db
Errors.GuardExpression db.State <> adStateOpen, message:="Connection should be open."
Dim result As UnitOfWork
Set result = New UnitOfWork
Set result.CommandFactory = factory
Set result.Connection = db
Set Create = result
End Function
'@Inject: this member should only be invoked by Me.Create, where Me is the class' default/predeclared instance.
'@Ignore ProcedureNotUsed: false positive with v2.5.0.5418
Friend Property Set Connection(ByVal value As IDbConnection)
Errors.GuardDoubleInitialization this.Connection
Errors.GuardNullReference value
Set this.Connection = value
this.Connection.BeginTransaction
End Property
'@Inject: this member should only be invoked by Me.Create, where Me is the class' default/predeclared instance.
'@Ignore ProcedureNotUsed: false positive with v2.5.0.5418
Friend Property Set CommandFactory(ByVal value As IDbCommandFactory)
Errors.GuardDoubleInitialization this.CommandFactory
Errors.GuardNullReference value
Set this.CommandFactory = value
End Property
Private Sub Class_Terminate()
On Error Resume Next
If Not this.Committed Then this.Connection.RollbackTransaction
On Error GoTo 0
End Sub
Private Sub IUnitOfWork_Commit()
Errors.GuardExpression this.Committed, message:="Transaction is already committed."
Errors.GuardExpression this.RolledBack, message:="Transaction was rolled back."
On Error Resume Next ' not all providers support transactions
this.Connection.CommitTransaction
this.Committed = True
On Error GoTo 0
End Sub
Private Function IUnitOfWork_Command() As IDbCommand
Set IUnitOfWork_Command = this.CommandFactory.Create(this.Connection)
End Function
Private Sub IUnitOfWork_Rollback()
Errors.GuardExpression this.Committed, message:="Transaction is already committed."
On Error Resume Next ' not all providers support transactions
this.Connection.RollbackTransaction
this.RolledBack = True
On Error GoTo 0
End Sub
Errors
If you paid close attention to the code listings so far, you likely already noticed the many Errors.GuardXxxxx member calls scattered throughout the code. There are probably as many ways to deal with custom errors as there are VBA classes out there, this is one way. Probably not the best way, but it feels “just right” for me in this case and I think I like it enough to keep using it until the problems it creates become clearer (there’s always something). Errors is a standard private module in the project, that defines custom error codes. Okay I was lazy and deemed SecureADODBCustomError all I needed, but it could also have been an Enum with descriptive names for each custom error code.
The module simply exposes a small number of very simple Sub procedures that make it easy for the rest of the code to raise meaningful custom errors:
'@Folder("SecureADODB")
'@ModuleDescription("Global procedures for throwing common errors.")
Option Explicit
Option Private Module
Public Const SecureADODBCustomError As Long = vbObjectError Or 32
'@Description("Re-raises the current error, if there is one.")
Public Sub RethrowOnError()
With VBA.Information.Err
If .Number <> 0 Then
Debug.Print "Error " & .Number, .Description
.Raise .Number
End If
End With
End Sub
'@Description("Raises a run-time error if the specified Boolean expression is True.")
Public Sub GuardExpression(ByVal throw As Boolean, _
Optional ByVal Source As String = "SecureADODB.Errors", _
Optional ByVal message As String = "Invalid procedure call or argument.")
If throw Then VBA.Information.Err.Raise SecureADODBCustomError, Source, message
End Sub
'@Description("Raises a run-time error if the specified instance isn't the default instance.")
Public Sub GuardNonDefaultInstance(ByVal instance As Object, ByVal defaultInstance As Object, _
Optional ByVal Source As String = "SecureADODB.Errors", _
Optional ByVal message As String = "Method should be invoked from the default/predeclared instance of this class.")
Debug.Assert TypeName(instance) = TypeName(defaultInstance)
GuardExpression Not instance Is defaultInstance, Source, message
End Sub
'@Description("Raises a run-time error if the specified object reference is already set.")
Public Sub GuardDoubleInitialization(ByVal instance As Object, _
Optional ByVal Source As String = "SecureADODB.Errors", _
Optional ByVal message As String = "Object is already initialized.")
GuardExpression Not instance Is Nothing, Source, message
End Sub
'@Description("Raises a run-time error if the specified object reference is Nothing.")
Public Sub GuardNullReference(ByVal instance As Object, _
Optional ByVal Source As String = "SecureADODB.Errors", _
Optional ByVal message As String = "Object reference cannot be Nothing.")
GuardExpression instance Is Nothing, Source, message
End Sub
'@Description("Raises a run-time error if the specified string is empty.")
Public Sub GuardEmptyString(ByVal value As String, _
Optional ByVal Source As String = "SecureADODB.Errors", _
Optional ByVal message As String = "String cannot be empty.")
GuardExpression value = vbNullString, Source, message
End Sub
Most of these procedures are invoked as the first executable statement in a given scope, to raise an error given invalid parameters or internal state, such as these:
Private Sub IUnitOfWork_Commit()
Errors.GuardExpression this.Committed, message:="Transaction is already committed."
Errors.GuardExpression this.RolledBack, message:="Transaction was rolled back."
On Error Resume Next ' not all providers support transactions
this.Connection.CommitTransaction
this.Committed = True
On Error GoTo 0
End Sub
Consistently raising such errors is the single best way to ensure our objects are always in a known and usable state, because we outright forbid them to be invalid. These validation clauses are called guard clauses, hence the GuardXxxxx procedure names.
A lot of the unit tests simply verify that, given the specified conditions, the expected error is raised:
'@TestMethod("Factory Guard")
Private Sub Create_ThrowsIfNotInvokedFromDefaultInstance()
On Error GoTo TestFail
With New AutoDbCommand
On Error GoTo CleanFail
Dim sut As IDbCommand
Set sut = .Create("connection string", New StubDbConnectionFactory, New StubDbCommandBase)
On Error GoTo 0
End With
CleanFail:
If Err.Number = ExpectedError Then Exit Sub
TestFail:
Assert.Fail "Expected error was not raised."
End Sub
If each guard clause has a unit test, then the tests are effectively documenting how the objects are meant to be used. With more specific custom errors, the tests would be more accurate, but there’s a point where you need to look at what you’ve got and say “I think I can work with that”, and move on.
Audience
Obviously, one doesn’t import 20 classes into their VBA project just to send one ADODB command to a database server. However if you’re maintaining a VB6 application that uses ADODB all over the place, leaks connections, leaves recordsets dangling, …then importing this API can really help tighten up the data access code in that legacy app. Or maybe you’re writing a complex data-driven system in VBA for Excel because that’s all you’ve got, and a UnitOfWork abstraction makes sense for you.
The goal here is mostly to 1) demonstrate proper usage of ADODB.Command for secure, parameterized queries, and 2) demonstrate that Classic VB (VB6/VBA) has always had everything everyone ever needed to write full-blown object-oriented code that leverages abstraction, encapsulation, and polymorphism – making it possible to write clean and fully unit-tested code.
…and of course, it makes a great practical application of the OOP concepts discussed in many other articles on this blog. Studying the code in this project gives you insight on…
Apparently this is this blog’s 100th article (!), and since Rubberduck is also about the future of Office automation in VBA, I wanted to write about what’s increasingly being considered a serious contender for an eventual replacement of Visual Basic for Applications. Just recently Mr.Excel (Bill Jelen) uploaded a video on YouTube dubbing it the “VBA killer”, and without being over-dramatic, I can’t help but to pragmatically agree with the sentiment… to an extent.
Forget VBA, think Win32 and COM: the Web has been “threatening” the future of Windows desktop applications of all kinds for about as long as VBA has been around. Windows desktop development went from COM-based to .NET, and now to cross-platform .NET Core, and there’s still COM interoperability built into .NET. It’s 2020 and Microsoft SQL Server runs perfectly fine on Linux, and you can use Microsoft Visual Studio on your Mac now, and a lot of what Microsoft does is open-sourced and accepts contributions, including .NET itself… and TypeScript is up there, too.
VBA isn’t going anywhere.
COM hasn’t gone anywhere either. If you used any Declare statements in VBA you probably know about user32.dll and kernel32.dll. The Win32 API is here to stay; COM is here to stay. My reading is that as long as the Windows plumbing exists to make it possible, VBA has no reason to go anywhere. The problem is that VBA and its COM-based Win32 infrastructure are essentially a dead end: it’s literally notgoing anywhere. The VBE has long been abandoned, and VBA as a language is stuck 20 years ago… but it’s likely going to stick around for a long time in desktop-land, even if (when?) the Excel COM type library stops getting new members – as the freezing of the GitHub repository holding the official VBA documentation suggests:
“This repo is no longer accepting PRs or new issues.”
Maybe (probably) I’m reading way too much into this, but to me that is a sign that we’ve reached a critical point in VBA’s history/lifetime. I do note that the repository wasn’t made read-only and that it’s still possible to submit a pull request, but the wording strongly suggests not to.
Meanwhile, the Office Extensibility team is hard at work getting the Excel Online automation capabilities of Office-JS on par with what can be achieved on Win32/desktop with VBA. As time marches forward, eventually we’ll reach a tipping point where Office-JS stabilizes while more and more enterprises embrace the Web as a platform: maybe I’m over-estimating how long that transition will take, but even well beyond that tipping point, COM and VBA will very likely still be around for a long, long time. It’s just that eventually the Excel team will have to stop updating (but not necessarily stop shipping) the COM type library, and focus on cross-platform extensibility.
Now, have you tried Excel Online? Personally, I don’t use it a lot (Rubberduck is Win32-only), but functions like XLOOKUP and SORT (and dynamic arrays in general) are a massive game-changer, and I will neither confirm nor deny that there are even more amazing capabilities to come. Things like this should make anyone seriously think twice before opting for a plain old perpetual desktop license: Excel 2016 isn’t going to get XLOOKUP anymore than Excel 2010 ever will…
This week I decided I was tired of seeing proof-of-concept “hello world” code demonstrating what Office-JS can do, and went on to explore and scratch more than just the surface. I found a Tetris game and decided to port my OOP Battleship from VBA to TypeScript… a language I know next to nothing about (and, looking at that Tetris game code and comparing it to mine… it shows!).
Script Lab
If you’re a VBA enthusiast, the first thing you notice in Excel Online, is the absence of a Developer tab. To automate Excel on the Web, you need to add Script Lab, a free add-on that brings up a task pane titled “Code”, that is very simple to use and that looks like this:
The default snippet merely sets up a “Run” UI button and wires it up to invoke a run async function that… does nothing but bring up a little banner at the top of the task pane that says “Your code goes here”.
As VBA developers, we’re used to having an actual IDE with an edit-and-continue debugger, dividing our projects into modules, and dragging and dropping controls onto a UserForm visual designer. So, your first impression of Script Lab might very well be that it’s even less of a code editor than the VBE is – especially with Rubberduck! You have to walk into it with an open mind, and with an open heart you just might discover a new friend, like I did.
Paradigm Shift
I’ve written code for a long time, but I’m not a web developer. HTML, JavaScript and CSS have scared me ever since they came into existence: too many things to think about, too many browsers, too many little things that break here but work there. I’ve been telling myself “I should try to do this” for years now, and I have to say that the project in the screenshot below is really my first [somewhat] serious attempt at anything web, …if we exclude what little ASP.NET/MVC I wrote for the rubberduckvba.com website (I’m more of a backend guy okay!).
So here’s the paradigm: that task pane is your playground, your sandbox – you have full control over everything that happens in there, the only limit is really just how bad you can be at CSS and HTML:
It’s not playable yet. I’ll definitely share it when it is …after a code review and a refactoring!
You can pop the code editor panel out into a separate browser window, which I warmly recommend doing – the code window docked on one side, the worksheet on the other. Another thing you’ll want to do is tweak your user settings to set editor.wordwrap: 'off', because for some reason the default setting is to word-wrap long lines of code, …which makes an interesting [CSS] tab when you have base-64 encoded .png image resources.
You’ll definitely want to pop the code editor into its own separate browser window.
There are a couple minor annoyances with the editor itself. Working with a single-file script for any decent-sized project, means you’re going to be scrolling up and down a lot. Hard to reliably reproduce, but I’m finding the editor tends to frequently (but thankfully, harmlessly) crash given a syntax error, like if you deleted a semicolon or something. Navigating between tabs loses your caret position, which means more scrolling. Could be just my machine (or my way-too-large-for-its-own-good script), but I’ve also experienced frequent and significant slow-downs and delays when typing.
Not having edit-and-continue debugging capabilities is a major paradigm shift as well, but then Script Lab isn’t meant to be a full-blown Integrated Development Environment… and the code that runs isn’t the code you’re editing; TypeScript compiles down to pure JavaScript, and mapping files need to get involved to help a TypeScript editor map the compiled JavaScript to the source TypeScript instructions.
On the bright side, like in Visual Studio { scopes } can be folded /collapsed, which does help reduce the amount of scrolling around and is a very useful and welcome editor feature. Also I couldn’t help but notice with utter glee that the editor auto-completes parentheses, braces, brackets, single and double quotes, …but while it does highlight matching parenthesis, unlike Rubberduck’s self-closing pair feature, backspacing onto a ( will not delete the matching closing ) character. One nice thing it does that Rubberduck autocompletion doesn’t, is that it wraps the selection: you can select an expression, type ( and instead of overwriting your selection with that character, it “wraps” the selected expression and you end up with (your selection).
As a programming language, TypeScript feels very much like the single best way to approach JavaScript: it supports strong types like a statically-typed language, and dynamic types, …like JavaScript (think Variant in VBA, but one to which you can tack-on any member you like at run-time). Coming from C# I’m finding myself surprisingly capable in this language that supports inherently object-oriented structures like classes and interfaces, and where even string literals have a ton of useful members (built-in support for regular expressions! lookbehinds in regex patterns!). Learning how string interpolation works will quickly make VBA concatenations feel clunky. Array methods will quickly become second-nature and you’ll realize just how much looping we do in VBA just because the types we’re dealing with have so little functionality.
But the most significant thing has to be how functions are now first-class citizens that can be passed around as parameters like any other object, just like we do in C# with delegates and lambda expressions. For example, in the constructor of my Ship class, I’m populating a Map<GridCoord, boolean> to hold the ship’s internal hit-state:
this.state = new Map<GridCoord, boolean>(
new Array(this.size).fill(false).map((value: boolean, index: number): [GridCoord, boolean] => {
let p = orientation === ShipOrientation.Horizontal
? position.offset(index - 1, 0)
: position.offset(0, index - 1);
return [p, false];
}
);
We’re creating a ship of a particular size and orientation, and the state means to hold the hit-state (true: hit) of each individual grid coordinate occupied by the ship. new Array(this.size).fill(false) creates an array of the appropriate length, filled with false Boolean values; but I wanted to map the array indices to actual grid coordinates to make my life easier, so I simply use .map((value, index):[GridCoord, boolean] => {...}) to do exactly that!
Reads like character soup? Don’t worry, that code is more verbose than it needs to be, and the lambda syntax is confusing to everyone that never worked with it. In a nutshell, (i) => {...} represents a function that takes an i parameter. There is no As keyword to specify data types in TypeScript, instead we would declare a numeric variable with e.g. var i: number. That means (value, index):[GridCoord, boolean] => {...} represents a function that takes a value and an index parameter (their values are provided by the map method), and returns a tuple (the square-bracketed part; can be thought of as some kind of anonymous type that’s defined on-the-spot with unnamed but typed members) made of a GridCoord and a boolean value. Therefore, the body of that function works out what GridCoord/boolean value to yield for each item of the Array(this.size) array.
Ternary (i.e. 3-operands) operators are another nice thing VBA doesn’t have. foo = bar ? a : b; assigns a to foo if bar evaluates to true, and assigns b otherwise. The closest we have in VBA is the IIf function, but because the provided true-value and false-value arguments are arguments, they both need to be evaluated before the function is even invoked.
I could go on and on about every little language feature TypeScript has that VBA doesn’t, but the truth is, there’s simply no possible comparison to be made: as a language (I’m not talking about the capabilities of the Excel object model here), VBA loses on every single aspect. And while VBA is essentially constrained to the VBE, TypeScript is in no way constrained to Script Lab. In fact if I wanted to make an actual serious Office-JS project, I’d likely be using VSCode, which I admittedly have yet to use for anything, but I’ve heard only good things about this lightweight IDE… and if I didn’t like it then I could just stick to good old Visual Studio.
VBA will very likely remain the uncontested King of Office automation on desktop for a very long time still: programming in TypeScript is a lot of fun to me, but I’m not Joe-in-Accounting – I write code (C#, T-SQL, VBA, …) for a living, and I doubt Script Lab, HTML, CSS, JavaScript and Chrome developer tools appeal as much to someone that isn’t enthusiastic about not just automating Office, not just VBA, but about programming in general. And for that, and that alone, I posit that VBA will continue to rule as King of Win32 Office automation for many years to come, and Rubberduck will be there to keep adding modern-IDE functionalities to the Visual Basic Editor.
Today I learned that VB.NET does in fact support Default properties. For years I was under the impression that dismissing the Set keyword meant default members couldn’t possibly exist in .NET, and I was wrong: dismissing the Set keyword meant that parameterlessdefault members couldn’t exist in .NET, but VB.NET can still implicitly invoke a Public Property Get Item(index) default member, just like its VB6 ancestor.
Rewind to their inception, and default members/properties have all the looks of a language feature that’s considered a nice convenient way to type code faster (in 20/20 hindsight, that was at the cost of readability). That’s why and how Debug.Print Application can compile, run, and output Microsoft Excel in the debug pane; it’s why and how an ADODB.Connection object and its ConnectionString properties can be impossible to tell apart… as a convenience; how a Range “is” its value(s), a TextBox “is” its text, or an OptionButton “is” True or False.
Default properties can result in a small reduction in source code-characters, but they can make your code more difficult to read. If the calling code is not familiar with your class […], when it makes a reference to the class […] name it cannot be certain whether that reference accesses the class […] itself, or a default property. This can lead to compiler errors or subtle run-time logic errors. […] Because of these disadvantages, you should consider not defining default properties. For code readability, you should also consider always referring to all properties explicitly, even default properties.
I cannot think of a single valid reason for any of these considerations to not be applicable to modern VBA, or even VB6 code. VB.NET removed the need for a disambiguating Set keyword by making a parameterless default member throw a compiler error. For contrast consider this code, and imagine the Set keyword doesn’t exist:
Dim things(9)
things(0) = New Thing
If the Thing class defines a parameterless default member, then who can tell what’s at index 0 of the things array? A Thing object reference? A SomethingElse object reference? The String representation of a Thing instance? 42?
Default members are hopefully not side-effecting magic invisible stardust code that is by definition invoked implicitly, by code that says one thing and does another, and requires looking up the documentation or the object browser definition of a type to remember what member we’re actually invoking – and even then, it can be obscured; the Excel type library is a prime example, with a hidden _Default property being the (drumroll) default property of the Range class, for example. Lastly, an implicit default member call is not 100% equivalent to an explicit one, and that tiny little difference can go as far as instantly crashing Excel.
Sounds terrible. Why would Rubberduck have a @DefaultMember annotation then?
With Rubberduck’s annotation and inspection/quick-fix system, you can easily define default members for your class modules; simply decorate the procedure with a '@DefaultMember annotation, synchronize member attributes, and done.
It’s not because you can, that you should. If you’re like me and someone gave you a knife, you’d probably at least try not to cut yourself. If you’re writing a custom collection class and you want it to be usable with the classic things(i) syntax rather than an explicit things.Item(i) member call, Rubberduck’s job is to help you do exactly that without needing to remove/export the code file, tweak it manually in Notepad++, then re-import it back into the project – that’s why the @DefaultMember annotation exists: because for the rare cases where you do want a default member, your ducky doesn’t let you down.
Currently, Rubberduck won’t complain if you make a parameterless procedure a default member. There’s an inspection idea that’s up-for-grabs to flag them though, if you’re looking for a fun contribution to an open-source project!
As was shared a week or two ago on social media, Rubberduck contributor and supporter Andrew “ThunderFrame” Jackson passed away recently – but his love for VBA, his awesomely twisted ways of breaking it, his insights, the 464 issues (but mostly ideas, with 215 still open as of this writing) and 30 pull requests he contributed to Rubberduck, have shaped a large part of what this project is all about, and for this release we wanted to honor him with a special little something in Rubberduck, starting with the splash screen.
Last December we lost a very valued contributor and friend. Next release the Rubberduck splash screen will be in his memory: that devil ducky was his online avatar.
— Rubberduck VBIDE add-in (@rubberduckvba) March 14, 2019
Andrew joined the project very early on. He gave us the signature spinning duckies and the SVG icon of the project; he once implemented a very creative way to make unit testing work in Outlook (and I know a certain duck that had to eat their hat because of it!), before the feature was made host-agnostic. He gave us the weirdest, most completely evil-but-still-legal VBA code we could possibly test Rubberduck’s parser/resolver with – and we’re very proud to have a ThunderCode-proof parser now!
What’s New?
This isn’t an exhaustive list. See the release notes for more information.
¡Rubberduck ahora habla español!
This release introduces Spanish language support. German, French, and Czech translations have also been updated.
Rubberduck doesn’t speak your language yet? Nothing would make us happier than helping you help us translate Rubberduck! See contributing.md for all the details, and don’t hesitate to ask any questions you have – go on, fork us!
The project’s many resource files are easily handled with the ResX Manager add-in for Visual Studio.
UI Enhancements
The Test Explorer has had a rather impressive facelift, Inspection Results are now much easier to review, navigate and filter. There is a known issue about the GroupingGrid control expanding/collapsing all groupings together, but we weren’t going to hold back the release for this – we will definitely address it in a near-future release though.
Toggle buttons in the toolbar now allow filtering inspection results by severity, and grouping by inspection type, by module, by individual inspection, or by severity. Similar toggle buttons in the Test Explorer allow grouping tests by outcome, module, or category. Tests can be categorized by specifying a category name string as an argument to the @TestMethod annotation.
Parser performance has improved, especially for the most common usages of “bang” (foo!bar) notation, which remain a difficult language construct to handle. But they’re also late-bound, implicit, default member calls that would probably be better off made explicit and early-bound.
Self-Closing Pair completion works rather nicely now, with only two known, low-priority edge cases that don’t behave quite as nicely as they should.
Easter Is Coming
And with Easter comes… White WalkersEaster Eggs, so all I’m going to say, is that they’ll be flagging ThunderCode – the kind of code our friend loved to test & push the limits of Rubberduck’s parser with. If your code trips a ThunderCode inspection, …nah, it can’t happen.
Woopsie, might happen after all. We’ll eventually figure out a way to hide them from the settings!
Also it’s apparently not impossible that there’s no way noother Easter Eggs werenever notadded to Rubberduck. For the record I don’t know if this means what I think I mean it to say, and that’s perfect.
What’s Next?
Some very important changes have been waiting for this release and will be merged in the next few weeks – these changes won’t necessarily be visible from a user standpoint, but they will greatly enhance our internal API – refactorings, COM object management, and we’ll be leveraging more of the TypeLibs API, which in turn should end up translating into greatly enhanced user experience and feature set.
Next release will include a few new inspections, including one that locates obsolete While...Wend loops, and suggests to rewrite them as Do While...Loop blocks, which can be exited with an Exit Do statement, whereas While loops can only be prematurely exited (without throwing an error) by an inelegant GoTo jump.
We really want to tighten our release cycle, so we’ll be shooting for the end of April for what should be version 2.4.2.
Unlike quite a number of Rubberduck releases, this time we’re not boasting a thousand commits though: we’re looking at well under 300 changes, but if the last you’ve seen of Rubberduck was 2.3.0 or prior, …trying this release you’ll quickly realize why we originally wanted to release it around Christmas.
So, here’s your belated Christmas gift from the Rubberduck dev team!
VBE Project References: CURED!
You may have seen the Introducing the Reference Explorer announcement post last autumn – well, the new feature is now field-tested, works beautifully, instinctively, and is ready for prime time. It’s a beauty!
The add/remove references dialog has seen a number of enhancements since its pre-release: thanks everyone for your constructive feedback!
Quickly locate any type library by name and/or description.
Pin your favorite references, and Rubberduck will keep them handy for all your VBA/VB6 projects.
You’ll never want to use the vanilla-VBE references dialog again!
If you’ve been following the Rubberduck project for quite some time, you may remember something about using annotations together with inspections and quick-fixes to document the presence of module & member attributes. You may also remember when & why the idea was dropped. Keeping in tradition with including new inspections every release… Surprise, it’s coming back!
German, French, and Czech translations have been updated, a number of bugs were fixed in a few inspections, the Code Explorer has seen a number of subtle enhancements, and WPF binding leaks are all but gone.
Code Explorer Enhancements
Adding the Reference Explorer made a perfect opportunity to revisit the Code Explorer toolwindow – our signature navigation feature. Behold, the new Code Explorer:
The new ‘Sync with code pane’ toolbar button (the left/right arrows icon) selects the treeview node closest to the current code pane selection.
There’s a new ‘Library References’ node that shows your project’s library dependencies …whether they’re in use or not:
Find all references can now be used to locate all uses of a given type library – including the built-in standard libraries! Note that rendering lots of search results in a toolwindow will require confirmation if there are too many results to display.
The project reference nodes get new icons:
Classes with a VB_PredeclaredID attribute set to True now have their own icon too (and their names now say (Predeclared) explicitly), and class modules marked with an @Interface annotation now appear with an “interface” icon, like IGameStrategy here:
Annotations & Attributes
They’re back, and this time it does work, and it’s another game changer: Rubberduck users no longer need to export any code file to modify module & member attributes!
Module & Member Annotations
At module level, the @ModuleDescription annotation can be given a string argument that controls the value of the module’s VB_Description attribute; the @Exposed annotation controls the value of the VB_Exposed attribute; the presence of a @PredeclaredId annotation signals a VB_PredeclaredId attribute with a value of True.
At member level, @Description annotations can be given a string argument that controls the value of the member’s VB_Description attribute.
Through inspections, Rubberduck is now able to warn about attributes that don’t have a corresponding annotation, and annotations that don’t have a corresponding attributes. Look for inspection results under the “Rubberduck Opportunities” category.
v2.4.x
The months to come will see further enhancements in several areas; there are several pull requests lined up already – stay tuned, and keep up with the pre-release builds by watching releases on GitHub!
You’re writing a rather large VBA/VB6 project, and you’re starting to have a sizable amount of passing unit tests. Did you know you can copy the test results to the clipboard with a single click?
…and then paste them onto a new worksheet and turn it into a data table:
If you’re not sure what to do next, you can even let Excel give you ideas – you’ll find the Recommended Charts button under the Insert Ribbon tab:
With the count of method by component chart, we can see what test modules have more test methods; the sum of duration by component chart can show us which test modules take the longer to execute – or we could average it across test categories, or archive test results and later aggregate them… and then use this data to performance-profile problematic test scenarios.
Similarly, the “Copy to Clipboard” button from the Code Explorer can be used to export a table into Excel, and using the recommended pivot tables feature, we can get a detailed breakdown of the project – for example count of names by declaration type creates a pivot table that lists all Rubberduck declaration types, so you can easily know how many line labels your project has, or how many Declare Function imports are used:
With a little bit of filtering and creativity, we can regroup all Constant, Function, PropertyGet and Variable declarations by return type, and easily identify, say, everything that returns a Variant:
The possibilities are practically endless: the data could be timestamped and exported to some Access or SQL Server database, to feed some dashboard or report showing how a project grows over time.
How would you analyze your VBA projects? What code metrics would you like to be able to review and pivot like this? Share your ideas, or implement them, and send a pull request our way!