Introducing Rubberduck 2.5.2

Version 2.5.1 was released August 22, 2020. Since then, the installer was downloaded over 11,600 times; we are now 420 commits and 650 modified files later, and the time has come to deliver all that work into a convenient little installer package and move on to the next dev/release cycle.

What’s New?

If you’ve kept up with latest pre-release builds (especially in the last few weeks), nothing much. If you’ve been patiently waiting for the next release, you’re in for a treat!

The first thing you’ll probably notice is the shiny new splash screen design:

It’s the same old yellow splash made with Paint.NET, with a tiled reflection distortion effect against the background, a semi-transparent white bottom panel, and a finer font. Do you like it?

Fixed Bugs

50-some issues labelled “bug” were closed between 2020-08-22 and mid-April 2021, many of them thanks to flicking the switch on leveraging our internal ITypeLib API for user code – thanks to earlier invaluable contributions from the amazing Wayne Phillips (vbWatchdog, twinBASIC), Rubberduck is now able to tap into the actual in-memory COM type library compiled from the VBA code and, eventually, fill the remaining the gaps in Rubberduck’s understanding of the code: Rubberduck now understands enough to be able to tell that ThisWorkbook has a _Workbook subtype, and that Sheet1 has a _Worksheet subtype, …and that’s enough to identify the ThisWorkbook module at long last, and as a result Rubberduck’s ImplicitActiveSheetReference and ImplicitActiveWorkbookReference inspections get to work exactly as intended, and the door is now opened for so many interesting things…

New Inspections

A Rubberduck release wouldn’t be a Rubberduck release without at least a handful of new inspections. The IllegalAnnotation inspection is being replaced by InvalidAnnotation, UnrecognizedAnnotation, and together with the new AnnotationInIncompatibleComponentType inspection they allow Rubberduck to better convey exactly what’s wrong with a given “illegal” annotation comment.

Annotation in Incompatible Component Type

Some annotations cannot be used in certain types of modules. For example, attribute-related annotations cannot be used in document modules (because Rubberduck cannot import back the modified modules), and a @TestModule annotation is only meaningful in a standard module.

Note that the @Description, @ModuleDescription and @VariableDescription annotations do work in document modules now, because Rubberduck is now reading docstrings off annotations rather than hidden attributes.

Implicit Containing Workbook Reference

Code in the ThisWorkbook module (Excel) referring to members of the Workbook class, have an implicit Me qualifier. This makes an unqualified Worksheets(1) retrieval in ThisWorkbook refer to ThisWorkbook.Worksheets(1), but an identical statement in any other module would be (implicitly) referring to ActiveWorkbook. By qualifying such member calls with Me, the intent is clarified.

Implicit Containing Worksheet Reference

Code in a worksheet module (Excel) referring to members of the Worksheet class, have an implicit Me qualifier. This makes an unqualified Range member call in the Sheet2 module refer to Sheet2, but an identical statement in any other module would be (implicitly) referring to ActiveSheet. By qualifying such member calls with Me, the intent is clarified.

Invalid Annotation

Flags unbound annotations; that is, annotation comments that were correctly parsed as Rubberduck annotations but that could not be associated with a target element. This would happen when a module annotation is used in local scope, or a member annotation at module level. This inspection only flags annotation comments that parsed as Rubberduck annotations.

Misleading ByRef Parameter

The RHS/Value parameter of a Property Let procedure is always passed by value. As such, an explicit ByRef modifier on such a parameter definition is misleading. From MS-VBAL (VBA language specifications) section 5.3.1.7 Property Declarations:

§ If the <value-param> of a <property-LHS-declaration> does not have a <parameter-mechanism> element or has a <parameter-mechanism> consisting of the keyword ByRef, it has the same meaning as if it instead had a <parameter-mechanism> element consisting of the keyword ByVal.
§ The <value-param> of a <property-LHS-declaration> always has the runtime semantics of a ByVal parameter.

Unrecognized Annotation

This inspection flags comments that parsed like a Rubberduck annotation, but aren’t recognized or supported. It picks up typos in Rubberduck annotations, and annotation-like comments that aren’t Rubberduck annotations but parse as such. Splicing this specific scenario from other invalid annotations is particularly useful when you want to mute inspection results for non-Rubberduck annotations while still validating the supported ones.


New Quick Fixes

This release also introduces a handful of new quick-fixes:

AnnotateEntryPoint

This fix is now available for ProcedureNotUsed inspection results in standard and document modules; it simply annotates a member with the new @EntryPoint annotation which specifically instructs ProcedureNotUsed to ignore that member. Use this quick-fix for UDFs and macro procedures that are attached to document objects and don’t need an Excel hotkey. If your project is hosted in an Excel workbook, macros annotated with @ExcelHotkey are also considered as entry points now.

DeclareAsExplicitType

VariableTypeNotDeclared inspection results could always be “fixed” by making the declared type an explicit Variant; this new quick-fix makes Rubberduck infer the declared type from usage where possible, which is objectively awesome.

QualifyWithMe

This new quick-fix is available for the new implicit containing workbook/worksheet reference inspections, making the reference to the containing module explicit.

Introduce Get Accessor

The Write-Only Property inspection gets a new quick-fix with this release; this iteration does not try to infer the backing field, so further manual edits are needed, but it’s a start.


New UI Language: Italian

Thanks to a timely contribution by @PhilCattivocaratere, we are thrilled to announce that this release introduces Italian as a UI language:

Every single UI string in Rubberduck comes from a localized resource file. Translating all the resources for a new language can take 3-5 hours, and then it’s only a matter of keeping the translations up-to-date by creating a small pull request when new resource strings are added for new features.

In a nutshell

Here’s a quick summary of the most significant pull requests and commits merged this cycle:

  • Encapsulate Field enhancements
  • We are now leveraging our internal ITypeLib API
  • We are now building Rubberduck with the latest version of Visual Studio 2019
  • Precompiler directives now parse correctly with line continuations
  • Internal CodeBuilder API honors indenter settings when generating code
  • Fixed a number of issues with name conflict validation
  • Test methods now support a @TestIgnore annotation to ignore a test
  • Specific projects can now be ignored by the parser
  • Users no longer need to accept the GPLv3 as if it were an End User License Agreement (EULA)
  • Custom templates extensions is changing from .rdt to .template
  • Implicit Variant inspection quick-fix will now infer the best type from usage instead of just making the variable an explicit Variant
  • For...Next loop variables no longer trigger a variable not used inspection
  • Implicit Public Member inspection will now flag Enum types and Type structures
  • Branch “master” was renamed to “main”
  • New Property Group indenter settings
  • Arrays declared with ReDim now correctly resolve the declared type
  • @Description, @VariableDescription, and @ModuleDescription can now be used in document modules (cannot be synchronized)
    • Documentation strings are now read from annotations when missing from attributes
  • Start menu link to website now uses https
  • Fixed context menu positioning
  • New @EntryPoint annotation marks a standard or document module member as invoked from outside the code; as such the Procedure Not Used inspection will no longer flags members annotated with @EntryPoint or @ExcelHotkey (Excel only).
  • Several other opportunistic fixes left & right, improved overall stability.
  • Shiny new splash screen; debug builds now indicate “debug” instead of a meaningless local build number (build version# is controlled by the AppVeyor CI build server; local builds are all .0).
  • Expand/collapse all in Code Explorer
  • Rubberduck CommandBar label will now show the corresponding parameter declaration for a selected argument, and Find all References will now include arguments at call sites for parameter declarations (previous versions would only count named arguments).
  • Find Symbol navigation tool works again.
  • Find all References search results will now highlight the target reference in its context.
  • Added Italian UI resources.

Possible (Silent) Crash on Exit

I haven’t personally experienced it in a long time in Excel, but Rubberduck may run into issues tearing down, sometimes causing an AccessViolationException when it unloads, which can either crash the host process or leave it hanging as a ghosted process that will interfere with reloading: verify that the host process (e.g. ACCESS.EXE) has shut down completely using Task Manager when you close everything, and make sure to kill any such ghosted processes before loading Rubberduck in a new process.

Sounds familiar? If you’ve been following the project all along, you probably remember similar behavior in earlier releases – at one point during this development cycle we thought the problem was finally under control, but the cure was worse than the disease and there was a chance that the host document / project gets completely corrupted and impossible to open in the VBE: because we think it’s much better to sometimes crash on teardown than to corrupt our users’ host documents forever, we have reverted that “fix” and will have to come up with something else.


What’s Next?

Lots of good stuff, including a new peek definition command to the code pane, Code Explorer, and the VBE’s own Project Explorer‘s context menus – the feature was developed too late to make the cut for this release, but will be available in 2.5.2.x pre-release builds very soon:

Peek Definition commands pop a panel that shows you the syntax-highlighted source code for a type or member. The pop-up panel can then be dragged around to keep it in sight while editing.

In the Unit Testing department, a mocking framework is about to debut as an experimental feature with a number of technical limitations.

I’m going to be turning my attention towards code path analysis this cycle; this internal API is needed to implement the more advanced inspection ideas, and an Extract Method refactoring needs it too.

To be continued…

Making MVVM Work in VBA Part 3: Bindings

Bindings are what makes Model-View-ViewModel feel a little bit like magic. Consider this example code, which creates the dynamic controls for an example UI and showcases how much of a game-changer having a MVVM framework could be for VBA code:

Private Sub InitializeView()
    
    Dim Layout As IContainerLayout
    Set Layout = ContainerLayout.Create(Me.Controls, TopToBottom)
    
    With DynamicControls.Create(This.Context, Layout)
        
        With .LabelFor("All controls on this form are created at run-time.")
            .Font.Bold = True
        End With
        
        .TextBoxFor BindingPath.Create(This.ViewModel, "StringProperty"), _
                    Validator:=New RequiredStringValidator, _
                    TitleSource:="Some String:" '<~ also accepts an IBindingPath

        .TextBoxFor BindingPath.Create(This.ViewModel, "CurrencyProperty"), _
                    FormatString:="{0:C2}", _
                    TitleSource:="Some Amount:" '<~ also accepts an IBindingPath
        
        .CommandButtonFor CancelCommand.Create(Me), This.ViewModel, "Close"
        
    End With
End Sub

This VBA code generates a dynamic UI layout with controls automatically bound to ViewModel properties, and buttons automatically bound to commands. In a project that leverages the MVVM infrastructure code, that’s the only code needed to produce this:

The RequiredStringValidator makes it impossible to leave the ‘StringProperty’ TextBox empty; valid values are automatically applied to the corresponding ViewModel property.

There’s a lot to be written about this DynamicControls API, but that part isn’t very polished yet, and the secret sauce is that it builds (mostly anyway) on top of Property Bindings: they are what makes this sorcery possible… even with a classic drag-and-drop designer UI.

I just couldn’t resist having [at least basic, bare-bones but still extensible] support for a working .LabelFor / .TextBoxFor syntax in VBA code, for the MSForms UI library! I’ll save that for another day though, the layout stuff isn’t where it needs to be yet.

I’m missing about a million unit tests so there’s a good chance something somewhere isn’t working the way it should, but what’s there should be close enough to be worth talking about, and what matters the most is that the code is testable.

Let’s dissect how property bindings work. This time I did not push code to the examples repository, because this is an actual project in its own right, with its own need for examples. I have uploaded everything to https://github.com/rubberduck-vba/MVVM.


Property Bindings

In the context of this MVVM infrastructure code, a property binding is an object responsible for binding a source property path to a target property path; the source points to a ViewModel property, and the target to a property of a UI element (control).

ViewModel?

A ViewModel can be any object that implements the INotifyPropertyChanged interface, as long as that class has:

  • Public properties for everything the View needs to bind to.
    • Property Let procedures should invoke OnPropertyChanged, but only when the property value actually changed: avoid signaling a changed property when its current value was merely overwritten with the same.
    • Property Get procedures are required for all binding modes; Property Let procedures are only needed for TwoWay and OneWayToSource bindings.
  • ICommand public properties can be exposed to avoid coupling the view with any particular specific command (other than AcceptCommand and/or CancelCommand).

Note that a View could use multiple ViewModels as needed; ViewModel classes should never know anything about any View.

INotifyPropertyChanged

This interface is central in the event propagation mechanics: in design patterns terms, a class that implement it is the subject in an Observer Pattern where the registered handlers are the observers. The reason a ViewModel needs to implement this interface, is because creating a property binding registers the binding as an observer – and it handles the ViewModel telling it about a property change by applying the binding(s) for that property.

The ExampleViewModel class illustrates how to properly implement this interface:

Public Property Get SomeProperty() As String
    SomeProperty = This.SomeProperty
End Property

Public Property Let SomeProperty(ByVal RHS As String)
    If This.SomeProperty <> RHS Then
        This.SomeProperty = RHS
        OnPropertyChanged "SomeProperty"
    End If
End Property

Private Sub OnPropertyChanged(ByVal PropertyName As String)
    This.Notifier.OnPropertyChanged Me, PropertyName
End Sub

Private Sub Class_Initialize()
    Set This.Notifier = New PropertyChangeNotifierBase
    '...
End Sub

The OnPropertyChanged method is only invoked when the RHS assigned value is different than the current value, and we don’t need to worry about tracking/adding observers or invoking them, because everything we need is already encapsulated in the PropertyChangeNotifierBase class, so we implement the interface by simply passing the parameters over to this “notifier” instance:

Private Sub INotifyPropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
    This.Notifier.OnPropertyChanged Source, PropertyName
End Sub

Private Sub INotifyPropertyChanged_RegisterHandler(ByVal Handler As IHandlePropertyChanged)
    This.Notifier.RegisterHandler Handler
End Sub

Now we know the interfaces involved in keeping source and target in sync, let’s look at everything else – starting with the binding paths.

IBindingPath

The documentation calls it “An object that can resolve a string property path to a value”, and that’s really all it does. The properties may need some explaining though:

  • Context refers to the base object for resolving the path, i.e. your ViewModel (for the source), or a UI control (for the target).
  • Path refers to the property path string; usually just a ViewModel or control property name, but this string is resolved recursively so you could bind to “ViewModel.SomeObject.SomeProperty” if you needed to.
  • Object is the object that owns the property we’re referring to. If the path is just a property name, then this is the same reference as the Context.
  • PropertyName is the resolved property name. In the example path above, that would be “SomeProperty”.

The interface also exposes Resolve, as well as TryReadPropertyValue, TryWritePropertyValue, and ToString methods; these members are invoked by the MVVM infrastructure internals.

IBindingPath is implemented by the BindingPath class, which exposes a Create factory method that property-injects the Context and Path values and invokes the Resolve method before returning the created object, so creating a binding path really just looks like this:

Dim Path As IBindingPath
Set Path = BindingPath.Create(ViewModel, "PropertyName")

And with that we’re ready to create an IPropertyBinding.

PropertyBindingBase

The IPropertyBinding interface is mostly only useful internally. There’s little of interest here that isn’t more appropriately covered by looking at the factory method for the PropertyBindingBase class. You ready? It’s quite a mouthful…

Public Function Create(ByVal BindingManager As IBindingManager, ByVal CommandManager As ICommandManager, ByVal TargetContext As Object, ByVal SourceContext As Object, ByVal SourcePropertyPath As String, _
Optional ByVal TargetPropertyPath As String = DefaultTargetControlProperty, _
Optional ByVal Mode As BindingMode = BindingMode.TwoWayBinding, _
Optional ByVal UpdateSource As BindingUpdateSourceTrigger = BindingUpdateSourceTrigger.OnExit, _
Optional ByVal Converter As IValueConverter, _
Optional ByVal StringFormat As IStringFormatter, _
Optional ByVal Validator As IValueValidator, _
Optional ByVal ValidationAdorner As IDynamicAdorner, _
Optional ByVal CancelExitOnValidationError As Boolean = True) As IPropertyBinding

The factory method creates the IBindingPath objects from the given context and property path strings, which makes it simpler for the calling code. Note that the target property path is Optional, how is that possible?

If you’ve spent a bit of time with the MVVM prototype code, you probably noticed the PropertyBinding class was pretty much out of control, and extending it to support more target types would only make it worse. So what I did is, I pulled the common code into a new PropertyBindingBase class, then moved the control-specific code into its own specialized control-specific property binding implementation, and now there’s a strategy pattern that’s responsible for fetching the correct implementation – so that’s how binding a TextBox target creates a TextBoxPropertyBinding, and how binding a Label target creates a OneWayPropertyBinding. Each control-specific binding class can handle that control’s events and implement control-specific behavior accordingly.

IBindingManager

The binding manager is the object that knows about all the property bindings; each property binding needs a reference to the binding manager that owns it, in order to invoke data validation and trigger command bindings to evaluate whether commands can be executed. This object is automatically created when you create an AppContext instance, but the AppContext can be injected with any IBindingManager implementation as needed.

ICommandManager

This “manager” guy knows about all the command bindings, which isn’t something I’ve talked about much yet. Next article about the Command Pattern will dive into more details; this object is automatically created when you create an AppContext instance, but the AppContext can be inject with any ICommandManager implementation as needed.

TargetContext

Typically, that’s just a reference to the target MSForms control. Technically, it could really be any object that has any number of public properties.

SourceContext

Typically, that’s just a reference to the source ViewModel object. Technically, it could really be any object that has any number of public properties [and ideally, that implements INotifyPropertyChanged to support 2-way bindings].

SourcePropertyPath

The last required parameter is a string representing a path (relative to the SourceContext) to the property that holds the value we want the binding target to use; see IBindingPath.

TargetPropertyPath

Each binding type has a default target property that is automatically inferred from the type of target (and in some cases, from the data type of the source property). For example, binding to a TextBox control automatically wires up the control’s Text property, such that providing a TargetPropertyPath is only needed when binding to another target property.

Mode

This enum value determines whether the binding synchronizes the target, the source, or both. Note that binding modes OneWayBinding and TwoWayBinding both require the source object to implement INotifyPropertyChanged.

UpdateSource

This enum value determines when the binding gets to update its source. When the Mode is OneWayBinding or OneTimeBinding, value UpdateSourceTrigger.Never is used automatically.

OnKeyPress gets to validate each individual keypress. Useful for TextBox bindings that need a key validator.

OnExit and OnPropertyChanged are still somewhat ambiguously defined, but OnExit gets to keep the target control focused if there’s a validation error, and OnPropertyChanged is [currently] firing for every keypress in a TextBox, after the control’s Text property udpates. Disambiguating the terms would mean breaking with MSForms’ meaning of “updated”… which may actually be a good thing: OnPropertyChanged would basically fire on exit but without a possible navigation-blocking validation error, and then OnKeyPress mode would still need to behave like OnPropertyChanged as far as updating the source goes.

Converter

Each property binding can use an IValueConverter to “convert” a value midway between the source and the target (or between the target and the source). For example we can bind a CheckBox control to a Boolean property, but if we need the checkbox checked when the property value is False, we can use an InverseBooleanConverter to convert True to False as the binding is applied.

StringFormat

The IAppContext.StringFormatterFactory property (can be property-injected from AppContext.Create) determines which formatter class type is going to be injected here. Supply a VBStringFormatterFactory to use VB6/VBA Strings.Format format string syntax, or supply a StringFormatterFactory (or just leave the defaults alone) to use the much more flexible .NET String.Format syntax.

Validator

When a binding is given an IValueValidator, it gets to start issuing validation errors, which propagate to the ViewModel and can be used to pop a warning banner with the validation error message. Note: the AcceptCommand class’ implementation of ICommand.CanExecute makes it return False when the ViewModel has validation errors.

ValidationAdorner

Having validation errors is good, letting the user know about them is even better. Supply an IDynamicAdorner implementation by invoking ValidationErrorAdorner.Create and use the default error formatters or supply custom ones.


Order of Operations

Several objects get involved whenever something happens in a UI control. Let’s look at what happens when we type something in a TextBox with a standard two-way property binding to some ViewModel property.

Control Events

If the TextBox control has a Change event handler in the form’s code-behind (it really shouldn’t though, if we actually follow MVVM), that code probably gets to run first. The IPropertyBinding implementation would be a TextBoxPropertyBinding object, which handles MouseUp and KeyPress, but these handlers don’t trigger anything. What actually triggers the propagation of the new TextBox.Text value to the ViewModel property, is the BeforeUpdate and Exit events, both of which are initially handled in the ControlEventsPunk class, an object that leverages the Win32 IConnectionPoint API to hook up event handlers for the MSForms.Controls interface of our TextBox control (we covered that in the previous post).

So the first thing to run is the ControlEventsPunk.OnBeforeUpdate callback, which promptly iterates all registered handlers (“observers”) and invokes their HandleBeforeUpdate method.

So the ControlEventsPunk.OnBeforeUpdate callback propagates the event to the TextBoxPropertyBinding, and the IHandleControlEvents_HandleBeforeUpdate procedure runs as a result… which proceeds to forward it to the PropertyBindingBase class with a call to OnBindingUpdateSourceOpportunity, a method with the responsibility of conditionally invoking the ApplyToSource method.

ApplyToSource

The method’s job is to read the value from the binding target, and then write that value to the binding source. If the binding’s Mode is OneTimeBinding or OneWayBinding, we can already bail out because these modes only ever write to the binding target.

The first thing that needs to happen is a call to Resolve against the target (an IBindingPath). Typically the Target path would resolve IBindingPath.Object to a MSForms UI control, and IBindingPath.PropertyName accordingly resolves to Text for a TextBoxPropertyBinding, or Value for a CheckBoxPropertyBinding, or Caption for a CaptionPropertyBinding given a Frame or Label target – unless a TargetPropertyPath string was provided, in which case all bets are off and we might be binding the ForeColor or Font.Bold properties of a Label-like control, or what’s stopping us from binding its Width property (time to revisit that progress indicator, I guess).

And that’s just the tip of the iceberg, because the binding can use an IValueConverter implementation, such that you could conceivably implement, say, a converter that takes some Enum constant and maps each value to some Picture icon, and then use that converter in the binding of a ViewModel property of that Enum type to some MSForms.Image control’s Picture property… but I digress. Converters can also do boring things, like making sure the input value 2 becomes 0.02 before it gets written to that PercentValue ViewModel property, and then string formats can make sure that looks like 2.0% before it gets written to TextBox.Text, but we’ll get to that.

So the next thing that needs to happen is a call to IBindingPath.TryReadPropertyValue, and if we can’t do that we need to bail out, but this time ApplyResult.BindingSkipped won’t be good enough, so we explicitly return a BindingFailed result.

Once we know what value is currently in TextBox.Text (when the source update trigger is OnKeyPress, we have the KeyAscii value for it), we need to invoke IValueConverter.ConvertBack if a converter was specified for the binding; if the conversion fails, we return ApplyResult.BindingConversionError.

If conversion succeeded, we’re ready to validate the converted value (or the original one, if there is no converter). If the update trigger is OnKeyPress, then the validator operates on the single keypress – otherwise we validate the entire value. Things get interesting when there’s a validation error now: we’re returning ApplyResult.BindingValidationError, but then if there’s a ValidationAdorner, its Show method gets invoked and the validation error for that property is propagated to an IValidationManager object.

If validation passes, we try to read the source property value. If we can’t read it, we bail with a BindingFailed result. Otherwise we compare the source value with the target value, and if they are different then we proceed to clear any validation errors for that property, and then we finally write the new value to the source property; if that final step succeeds, we return ApplyResult.BindingSuccess result.

ApplyToTarget

When it’s a ViewModel property that changes, a similar sequence of events unfolds: the Property Let procedure invokes INotifyPropertyChanged.NotifyPropertyChanged, the property binding was registered as an observer, so IHandlePropertyChanged_HandlePropertyChanged is invoked; whenever the provided Source is the binding source and the provided PropertyName is the source property name, ApplyToTarget gets invoked.

When the binding mode is OneWayToSource or OneTimeBinding, we know we can immediately bail out, because these states don’t write to the binding target. Now, it’s entirely possible that we still need to supply a TextBox with a Text value even if we can’t yet resolve the binding Source (e.g. IBindingPath.Object resolves to Nothing). In such cases, we attempt to get a sensible default target value depending on the name of the target property:

  • “Text” and “Caption” target properties default to vbNullString;
  • “Enabled” and “Visible” target properties default to False;
  • “Value” property defaults to False when the target is a CheckBox or OptionButton.

If the source object is Nothing and we don’t have a fallback default, we bail out. Otherwise we try to read the source (ViewModel) value, then we validate it, then we convert it, then we read the target property value, compare with the source, and overwrite it if they’re different… but just before doing that, we run it through an IStringFormatter if we have one.


StringFormat

An MVVM application might need to use, say, a Date value somewhere. The application needs the data type to be Date, such that the program doesn’t need to worry about a malformed or ambiguous date string and works with the actual underlying Date value. Such an application would define a ViewModel class with a Date property (say, OrderDate), and then there can be a TextBox control showing that Date value to the user.

If we don’t do anything, the content of that TextBox control would be whatever VBA decides a Date value needs to look like when represented as a String, and that would be the (sorry, American readers) utterly nonsensical en-US format (mm-dd-yyyy). If your application’s users are happy with such a format, more power to them – but I like my date strings unambiguous and boringly standard, so I’d want the TextBox content to say “yyyy-mm-dd” instead. By providing a FormatString argument to the property binding, we can make it do exactly that. Or we can just as easily make it say “Thursday, October 22 2020” if we wanted to, and with a StringToDateConverter we could round-trip that value back to a proper Date.

Or maybe our ViewModel has a Currency property because our application needs to get some dollar amount, and having that displayed in a TextBox control as 1234567.89 is bare-bones good enough, but we could provide a FormatString argument to the property binding and have our ViewModel’s Currency property hold the numeric value 1234567.89 all while the bound TextBox control says $1,234,567.89.

Without MVVM property bindings doing this for us, implementing this kind of functionality is such a pain in the neck that it’s hardly ever done at all! Nobody wants to deal with parsing dates and formatted currency amounts off a TextBox control, and for a reason: when TextBox.Text is the value you’re working with, you are working with a String and you do need to parse its content.

With MVVM, we’re completely elsewhere: the TextBox.Text is just a receptacle for displaying whatever the real underlying value is (i.e. the ViewModel property), and is completely separated from it – and this tremendously simplifies everything.

The MVVM infrastructure code comes with two implementations for the IStringFormatter interface:

So in order to make a Date ViewModel property look like YYYY-MM-DD we could:

  • Use a VBStringFormatter with a “YYYY-MM-DD” format string (case insensitive)
  • Use a StringFormatter with a “yyyy-MM-dd” format string (note: lowercase-“m” refers to the “minute” part of the datetime here – use uppercase-“M” for the month!)

And in order to make a Currency ViewModel property look like money we could:

  • Use a VBStringFormatter with a “Currency” (or a culture-invariant “$#,##0.00”) format string
  • Use a StringFormatter with a “{0:C2}” format string (if we want 2 decimals)

Creating an IStringFormatter every time we want to use one would be annoying, so the binding manager uses the abstract factory from the IAppContext to spawn it automatically. A nice side-effect of this, is that the string formatters for the bindings of a given context are guaranteed to all use the same syntax. So if we wanted to use VB format strings, we would create the app context like this:

Dim Context As IAppContext
Set Context = AppContext.Create(FormatterFactory:=New VBStringFormatterFactory)

Note that if you use a format string that results in a TextBox.Text value that can’t be automatically (and correctly) parsed back into the data type of the bound ViewModel property (if that isn’t a String), updating the binding source will likely fail with a conversion error: you will need to implement an IValueConverter and inject it into the binding along with the format string in order to correctly convert the formatted string back to a value that is usable by the binding; a StringToDateConverter class exists in the MVVM infrastructure code to work with Date properties and standard format strings, but the implementation may need adjustments to handle formats that don’t automatically round-trip back to a Date value.


Validation

Another key aspect of property bindings, is that they simplify validating user inputs. If a program needs to work with some numeric value provided by the user and the user interface doesn’t validate its inputs, there’s a type mismatch error written in the sky there, or worse. As a general rule of thumb, it’s a good idea for code to assume that a value coming from the user is anything but what the program needs to work as expected.

The best way to handle an error is always to avoid throwing that error in the first place, and validating user inputs contributes to exactly this.

If you need the digits of a phone number and present the user with a control that only accepts a certain number of digit characters and then uses a format string to prettify the value on exit, you ensure that your PhoneNumber string value only ever contains the meaningful characters, leaving the “what a phone number looks like” concern separate from the “phone number” data itself, which means every phone number in your list/table ultimately gets to look exactly the same, as opposed to the free-form nightmares I presume we’re all well too familiar with.

The MVVM infrastructure addresses validation concerns through the IValidationManager interface. The object that implements this interface is responsible for managing validation errors across binding sources (ViewModels) in the context of an IApplicationContext.

ValidationManager

The role of the validation manager is to encapsulate the validation state and expose methods to add and clear validation errors; the IsValid indexed property returns a Boolean given a context (the ViewModel) and an optional property name: in order to know whether the entire context is valid, omit the PropertyName argument.

OnValidationError and ClearValidationError respectively add and remove a validation error for a particular source property, and the validation manager keeps validation errors in a dictionary keyed with the ViewModel object (a string representation of its object pointer), such that each ViewModel can be deemed “valid” or “invalid” individually/separately.

The “manager” class isn’t responsible for doing anything with a validation error: it just holds the state, so that other components can query it and retrieve the IValidationError for SomeViewModel.SomeProperty.

An IValidationError is a simple object that gives us a Message (from the IValueValidator that caused the binding to fail validation) and the IPropertyBinding that couldn’t be applied.


So, that dynamic UI stuff?

It works good-enough to make a good-enough screenshot, but the IContainerLayout stuff needs more thinking-through and more fiddling to get everything just right. See, as of this writing the layout API stacks controls horizontally or vertically, and well, that’s about it.

I want a docking panel, a layout container that can resize its children as needed and that’s a truly fascinating topic… For now there’s an IDynamicControlBuilder interface that looks like this:

'@Folder MVVM.Infrastructure.Abstract
'@ModuleDescription "Builds dynamic MSForms UI components from a binding source."
'@Interface
'@Exposed
Option Explicit

'@Description "Creates a multiline MSForms.TextBox control for the spercified String property binding path."
Public Function TextAreaFor(ByVal SourceValue As IBindingPath, Optional ByVal Converter As IValueConverter, Optional ByVal Validator As IValueValidator, Optional ByVal ErrorAdorner As IDynamicAdorner, Optional ByVal TitleSource As Variant) As MSForms.TextBox
End Function

'@Description "Creates a MSForms.TextBox control for the specified String property binding path."
Public Function TextBoxFor(ByVal SourceValue As IBindingPath, Optional ByVal FormatString As String, Optional ByVal Converter As IValueConverter, Optional ByVal Validator As IValueValidator, Optional ByVal ErrorAdorner As IDynamicAdorner, Optional ByVal TitleSource As Variant) As MSForms.TextBox
End Function

'@Description "Creates a MSForms.Label control for the specified Caption string or String property binding path."
Public Function LabelFor(ByVal SourceCaption As Variant, Optional ByVal FormatString As String, Optional ByVal Converter As IValueConverter) As MSForms.Label
End Function

'@Description "Creates a MSForms.ComboBox control for the specified Value property binding path; SourceItems should be an array property."
Public Function ComboBoxFor(ByVal SourceValue As IBindingPath, ByVal SourceItems As IBindingPath, Optional ByVal FormatString As String, Optional ByVal Converter As IValueConverter, Optional ByVal Validator As IValueValidator, Optional ByVal ErrorAdorner As IDynamicAdorner, Optional ByVal TitleSource As Variant) As MSForms.ComboBox
End Function

'@Description "Creates a MSForms.ListBox control for the specified Value property binding path; SourceItems should be an array property."
Public Function ListBoxFor(ByVal SourceValue As IBindingPath, ByVal SourceItems As IBindingPath, Optional ByVal TitleSource As Variant) As MSForms.ListBox
End Function

'@Description "Creates a MSForms.OptionButton control for the specified Value (Boolean) property binding path; uses the specified Caption string or String property binding path for the control's Caption."
Public Function OptionButtonFor(ByVal SourceValue As IBindingPath, ByVal SourceCaption As Variant) As MSForms.OptionButton
End Function

'@Description "Creates a MSForms.CheckBoxButton control for the specified Value (Boolean) property binding path; uses the specified Caption string or String property binding path for the control's Caption."
Public Function CheckBoxFor(ByVal SourceValue As IBindingPath, ByVal SourceCaption As Variant) As MSForms.CheckBox
End Function

'@Description "Creates a MSForms.CommandButton control for the specified ICommand, using the specified ViewModel context and Caption string or String property binding path."
Public Function CommandButtonFor(ByVal Command As ICommand, ByVal BindingContext As Object, ByVal SourceCaption As Variant) As MSForms.CommandButton
End Function

…and I haven’t even tested all of it yet, and small little things that actually matter, like OptionButton groups, aren’t being considered. I still need to think of how this API can get where it wants to be, but I really like where it’s going.


Thoughts?

To be honest, I’m having a blast with this, and writing actual working MVVM code in VBA is completely surreal, in a very awesome way.

I think it’s in itself a nice deep-dive into OOP+VBA – whether the MVVM architecture it enables ends up being the backbone of any production app or not.

What do you think?

Making MVVM Work in VBA Part 2: Event Propagation

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:

EventVB_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.

Making MVVM Work in VBA Part 1 – Testing

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 require OOP! 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 @TestInitialize before 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.

Model, View, ViewModel

100% VBA, 100% OOP

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 the examples 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!

A screenshot of a carefully-crafted dialog form for managing content served by rubberduckvba.com. A modal prompts the user for SQL Server credentials, all commands but the "reload" button are disabled.
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:

<TextBox Text="{Binding SomeProperty, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" />

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 propertyof a property – as long as the string ultimately resolves to a non-object member.

.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"))

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.

ValueBehavior
TwoWayBindingBinding will update the source when the target changes, and will update the target when the source changes.
OneWayBindingBinding will update the target when the source changes.
OneWayToSourceBinding will update the source when the target changes.
OneTimeBindingBinding will only update the target once.
The BindingMode enum values
ValueBehavior
OnPropertyChangedBinding will update when the bound property value changes.
OnKeyPressBinding will update the source at each keypress. Only available for TextBox controls. Data validation may prevent the keypress from reaching the UI element.
OnExitBinding 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 UpdateSourceTrigger enum 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
MethodPurpose
CreateFactory method, ensures every instance is created with a target UI element.
WithErrorBackgroundColorMakes 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.
WithErrorBorderColorMakes 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”.
WithErrorForeColorMakes 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.
WithErrorFontBoldMakes 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.
WithTargetOnlyVisibleOnErrorMakes 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:

.BindPropertyPath ViewModel, "NetAmount", Me.NetAmountBox, StringFormat:="USD$ {0:C2}"

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.

To be continued…

Rubberduck 2.5.1

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:

a window describing the member to annotate and the annotation to add
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 not used inspection now more clearly targets call sites, and now ignores non-user code.
  • 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.

Peace!

Secure ADODB

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.

In this article we will explore a heavily object-oriented solution to querying a database securely with the ADODB library.


Securely?

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 repository on 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…

  • OOP foundations: abstraction, encapsulation, polymorphism.
  • SOLID principles: single responsibility, dependency inversion, etc.
  • DI techniques: property injection, abstract factory.
  • Unit testing: what to test, how to test, stubbing dependencies, etc.
  • Using custom errors, guard clauses, input validation.
  • Leveraging Rubberduck annotations, minimizing inspection results.

Office-JS & Script Lab

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 not going 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.

The King is dead, long live the King!

To be continued…

Modern VBA Best Practices: Default Members

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 parameterless default 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.

These are the modern-day considerations for VB.NET default properties (emphasis mine, .NET-specifics removed):

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!

Rubberduck 2.4.1: ThunderFrame Edition

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.

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 Walkers Easter 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 no other Easter Eggs were never not added 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.