Posts

The Reusable Progress Indicator

So you’ve written a beautiful piece of code, a macro that does everything it needs to do… the only thing is that, well, it takes a while to complete. Oh, it’s as efficient as it gets, you’ve put it up for peer review on Code Review Stack Exchange, and the reviewers helped you optimize it. You need a way to report progress to your users.

There are several possible solutions.

Updating Application.StatusBar

If the macro is written in such a way that the user could very well continue using Excel while the code is running, then why disturb their workflow – simply updating the application’s status bar is definitely the best way to do it.

You could use a small procedure to do it:

Public Sub UpdateStatus(Optional ByVal msg As String = vbNullString)
 
    Dim isUpdating As Boolean
    isUpdating = Application.ScreenUpdating
 
    'we need ScreenUpdating toggled on to do this:
    If Not isUpdating Then Application.ScreenUpdating = True
 
    'if msg is empty, status goes to "Ready"
    Application.StatusBar = msg
 
    'make sure the update gets displayed (we might be in a tight loop)
    DoEvents
 
    'if ScreenUpdating was off, toggle it back off:
    Application.ScreenUpdating = isUpdating
 
End Sub

It’s critical to understand that the user can change the ActiveSheet at any time, so if your long-running macro involves code that implicitly (or explicitly) refers to the active worksheet, you’ll run into problems. Rubberduck has an inspection that specifically locates these implicit references though, so you’ll do fine.

Modeless Progress Indicator

A commonly blogged-about solution is to display a modeless UserForm and update it from the worker code. I dislike this solution, for several reasons:

  • The user is free to interact with the workbook and change the ActiveSheet at any time, but the progress is reported in an invasive dialog that the user needs to drag around to move out of the way as they navigate the worksheets.
  • It pollutes the worker code with form member calls; the worker code decides when to display and when to hide and destroy the form.
  • It feels like a work-around: we’d like a modal UserForm, but we don’t know how to make that work nicely.

“Smart UI” Modal Progress Indicator

If we only care to make it work yesterday, a “Smart UI” works: we get a modal dialog, so the user can’t use the workbook while we’re modifying it. What’s the problem then?

The form is running the show – the “worker” code needs to be in the code-behind, or invoked from it. That is the problem: if you want to reuse that code, in another project, you need to carefully scrap the worker code. If you want to reuse that code in the same project, you’re out of luck – either you duplicate the “indicator” code and reimplement the other “worker” code in another form’s code-behind, or the form now has “modes” and some conditional logic determines which worker code will get to run: you can imagine how well that scales if you have a project that needs a progress indicator for 20 features.

“Smart UI” can’t be good either. So, what’s the real solution then?

A Reusable Progress Indicator

We want a modal indicator (so that the user can’t interfere with our modifications), but one that doesn’t run the show: we want the UserForm to be responsible for nothing more than keeping its indicator representative of the current progress.

This solution is based on a piece of code I posted on Code Review back in 2015; you can find the original post here. This version is better though, be it only because of how it deals with cancellation.

The solution is implemented across two components: a form, and a class module.

ProgressView

First, a UserForm, obviously.

ProgressView

Nothing really fancy here. The form is named ProgressView. There’s a ProgressLabel, a 228×24 DecorativeFrame, and inside that Frame control, a ProgressBar label using the Highlight color from the System palette. Here’s the complete code-behind:

Option Explicit
Private Const PROGRESSBAR_MAXWIDTH As Integer = 224
Public Event Activated()
Public Event Cancelled()

Private Sub UserForm_Activate()
    ProgressBar.Width = 0
    RaiseEvent Activated
End Sub

Public Sub Update(ByVal percentValue As Single, Optional ByVal labelValue As String, Optional ByVal captionValue As String)
    If labelValue <> vbNullString Then ProgressLabel.Caption = labelValue 
    If captionValue <> vbNullString Then Me.Caption = captionValue
    ProgressBar.Width = percentValue * PROGRESSBAR_MAXWIDTH
    DoEvents 
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        RaiseEvent Cancelled
    End If
End Sub

Clearly this isn’t a Smart UI: the form doesn’t even have a concept of “worker code”, it’s blissfully unaware of what it’s being used for. In fact, on its own, it’s pretty useless. Modally showing the default instance of this form leaves you with only the VBE’s “Stop” button to close it, because its QueryClose handler is actively preventing the user from “x-ing out” of it. Obviously that form is rather useless on its own – it’s not responsible for anything beyond updating itself and notifying the ProgressIndicator when it’s ready to start reporting progress – or when the user means to cancel the long-running operation.

ProgressIndicator

This is the class that the client code will be using. A PredeclaredId attribute gives it a default instance, which is used to expose a factory method.

Here’s the full code – walkthrough follows:

Option Explicit

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Const DEFAULT_CAPTION As String = "Progress"
Private Const DEFAULT_LABEL As String = "Please wait..."

Private Const ERR_NOT_INITIALIZED As String = "ProgressIndicator is not initialized."
Private Const ERR_PROC_NOT_FOUND As String = "Specified macro or object member was not found."
Private Const ERR_INVALID_OPERATION As String = "Worker procedure cannot be cancelled by assigning to this property."
Private Const VBERR_MEMBER_NOT_FOUND As Long = 438

Public Enum ProgressIndicatorError
    Error_NotInitialized = vbObjectError + 1001
    Error_ProcedureNotFound
    Error_InvalidOperation
End Enum

Private Type TProgressIndicator
    procedure As String
    instance As Object
    sleepDelay As Long
    canCancel As Boolean
    cancelling As Boolean
    currentProgressValue As Double
End Type

Private this As TProgressIndicator
Private WithEvents view As ProgressView

Private Sub Class_Initialize()
    Set view = New ProgressView
    view.Caption = DEFAULT_CAPTION
    view.ProgressLabel = DEFAULT_LABEL
End Sub

Private Sub Class_Terminate()
    Set view = Nothing
    Set this.instance = Nothing
End Sub

Private Function QualifyMacroName(ByVal book As Workbook, ByVal procedure As String) As String
    QualifyMacroName = "'" & book.FullName & "'!" & procedure
End Function

Public Function Create(ByVal procedure As String, Optional instance As Object = Nothing, Optional ByVal initialLabelValue As String, Optional ByVal initialCaptionValue As String, Optional ByVal completedSleepMilliseconds As Long = 1000, Optional canCancel As Boolean = False) As ProgressIndicator
 
    Dim result As ProgressIndicator
    Set result = New ProgressIndicator
 
    result.Cancellable = canCancel
    result.SleepMilliseconds = completedSleepMilliseconds
 
    If Not instance Is Nothing Then
        Set result.OwnerInstance = instance
    ElseIf InStr(procedure, "'!") = 0 Then
        procedure = QualifyMacroName(Application.ActiveWorkbook, procedure)
    End If
 
    result.ProcedureName = procedure
 
    If initialLabelValue <> vbNullString Then result.ProgressView.ProgressLabel = initialLabelValue
    If initialCaptionValue <> vbNullString Then result.ProgressView.Caption = initialCaptionValue

    Set Create = result
 
End Function

Friend Property Get ProgressView() As ProgressView
    Set ProgressView = view
End Property

Friend Property Get ProcedureName() As String
    ProcedureName = this.procedure
End Property

Friend Property Let ProcedureName(ByVal value As String)
    this.procedure = value
End Property

Friend Property Get OwnerInstance() As Object
    Set OwnerInstance = this.instance
End Property

Friend Property Set OwnerInstance(ByVal value As Object)
    Set this.instance = value
End Property

Friend Property Get SleepMilliseconds() As Long
    SleepMilliseconds = this.sleepDelay
End Property

Friend Property Let SleepMilliseconds(ByVal value As Long)
    this.sleepDelay = value
End Property

Public Property Get CurrentProgress() As Double
    CurrentProgress = this.currentProgressValue
End Property

Public Property Get Cancellable() As Boolean
    Cancellable = this.canCancel
End Property

Friend Property Let Cancellable(ByVal value As Boolean)
    this.canCancel = value
End Property

Public Property Get IsCancelRequested() As Boolean
    IsCancelRequested = this.cancelling
End Property

Public Sub AbortCancellation()
    Debug.Assert this.cancelling
    this.cancelling = False
End Sub

Public Sub Execute()
    view.Show vbModal
End Sub

Public Sub Update(ByVal percentValue As Double, Optional ByVal labelValue As String, Optional ByVal captionValue As String)

    On Error GoTo CleanFail
    ThrowIfNotInitialized

    ValidatePercentValue percentValue
    this.currentProgressValue = percentValue
 
    view.Update this.currentProgressValue, labelValue

CleanExit:
    If percentValue = 1 Then Sleep 1000 ' pause on completion
    Exit Sub

CleanFail:
    MsgBox Err.Number & vbTab & Err.Description, vbCritical, "Error"
    Resume CleanExit
End Sub

Public Sub UpdatePercent(ByVal percentValue As Double, Optional ByVal captionValue As String)
    ValidatePercentValue percentValue
    Update percentValue, Format$(percentValue, "0.0% Completed")
End Sub

Private Sub ValidatePercentValue(ByRef percentValue As Double)
    If percentValue > 1 Then percentValue = percentValue / 100
End Sub

Private Sub ThrowIfNotInitialized()
    If this.procedure = vbNullString Then
        Err.Raise ProgressIndicatorError.Error_NotInitialized, TypeName(Me), ERR_NOT_INITIALIZED
    End If
End Sub

Private Sub view_Activated()

    On Error GoTo CleanFail
    ThrowIfNotInitialized

    If Not this.instance Is Nothing Then
        ExecuteInstanceMethod
    Else
        ExecuteMacro
    End If

CleanExit:
    view.Hide
    Exit Sub

CleanFail:
    MsgBox Err.Number & vbTab & Err.Description, vbCritical, "Error"
    Resume CleanExit
End Sub

Private Sub ExecuteMacro()
    On Error GoTo CleanFail
    Application.Run this.procedure, Me

CleanExit:
    Exit Sub

CleanFail:
    If Err.Number = VBERR_MEMBER_NOT_FOUND Then
        Err.Raise ProgressIndicatorError.Error_ProcedureNotFound, TypeName(Me), ERR_PROC_NOT_FOUND
    Else
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End If
    Resume CleanExit
End Sub

Private Sub ExecuteInstanceMethod()
    On Error GoTo CleanFail
 
    Dim parameter As ProgressIndicator
    Set parameter = Me 'Me cannot be passed to CallByName directly

    CallByName this.instance, this.procedure, VbMethod, parameter

CleanExit:
    Exit Sub

CleanFail:
    If Err.Number = VBERR_MEMBER_NOT_FOUND Then
        Err.Raise ProgressIndicatorError.Error_ProcedureNotFound, TypeName(Me), ERR_PROC_NOT_FOUND
    Else
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End If
    Resume CleanExit
End Sub

Private Sub view_Cancelled()
    If Not this.canCancel Then Exit Sub
    this.cancelling = True
End Sub

The Create method is intended to be invoked from the default instance, which means if you’re copy-pasting this code into the VBE, it won’t work. Instead, paste this header into notepad first:

VERSION 1.0 CLASS
BEGIN
 MultiUse = -1 'True
END
Attribute VB_Name = "ProgressIndicator"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True

Then paste the actual code underneath, save as ProgressIndicator.cls, and import the class module into the VBE. Note the VB_Exposed attribute: this makes the class usable in other VBA projects, so you could have this progress indicator solution in, say, an Excel add-in, and have “client” VBA projects that reference it. Friend members won’t be accessible from external code.

Here I’m Newing up the ProgressView directly in the Class_Initialize handler: this makes it tightly coupled with the ProgressIndicator. A better solution might have been to inject some IProgressView interface through the Create method, but then this would have required gymnastics to correctly expose the Activated and Cancelled view events, because events can’t simply be exposed as interface members – I’ll cover that in a future article, but the benefit of that would be loose coupling and enhanced testability: one could inject some MockProgressView implementation (just some class / not a form!), and just like that, the worker code could be unit tested without bringing up any form – but then again, that’s a bit beyond the scope of this article, and I’m drifting.

So the Create method takes the name of a procedure​, and uses it to set the ProcedureName property: this procedure name can be any Public Sub that takes a ProgressIndicator parameter. If it’s in a standard module, nothing else is needed. If it’s in a class module, the instance parameter needs to be specified so that we can later invoke the worker code off an instance of that class. The other parameters optionally configure the initial caption and label on the form (that’s not exactly how I’d write it today, but give me a break, that code is from 2015). If the worker code supports cancellation, the canCancelparameter should be supplied.

The next interesting member is the Execute method, which displays the modal form. Doing that soon triggers the Activated event, which we handle by first validating that we have a procedure to invoke, and then we either ExecuteInstanceMethod (given an instance), or ExecuteMacro​ – then we Hide the view and we’re done.

ExecuteMacro uses Application.Run to invoke the procedure; ExecuteInstanceMethod uses CallByName to invoke the member on the instance. In both cases, Me is passed to the invoked procedure as a parameter, and this is where the fun part begins.

The worker code is responsible for doing the work, and uses its ProgressIndicator parameter to Update the progress indicator as it goes, and periodically check if the user wants to cancel; the AbortCancellation method can be used to, well, cancel the cancellation, if that’s needed.

Client & Worker Code

The client code is responsible for registering the worker procedure, and executing it through the ProgressIndicator instance, for example like this:

Public Sub DoSomething()
    With ProgressIndicator.Create("DoWork", canCancel:=True)
        .Execute
    End With
End Sub

The above code registers the DoWork worker procedure, and executes it. DoWork could be any Public Sub in a standard module (.bas), taking a ProgressIndicator parameter:

Public Sub DoWork(ByVal progress As ProgressIndicator)
    Dim i As Long
    For i = 1 To 10000
        If ShouldCancel(progress) Then
            'here more complex worker code could rollback & cleanup
            Exit Sub
        End If
        ActiveSheet.Cells(1, 1) = i
        progress.Update i / 10000
    Next
End Sub

Private Function ShouldCancel(ByVal progress As ProgressIndicator) As Boolean
    If progress.IsCancelRequested Then 
        If MsgBox("Cancel this operation?", vbYesNo) = vbYes Then
            ShouldCancel = True
        Else
            progress.AbortCancellation
        End If
    End If
End Function

The Create method can also register a method defined in a class module, given an instance of that class – again as long as it’s a Public Sub taking a ProgressIndicator parameter:

Public Sub DoSomething()
    Dim foo As SomeClass
    Set foo = New SomeClass
    With ProgressIndicator.Create("DoWork", foo)
        .Execute
    End With
End Sub

Considerations

In order to use this ProgressIndicator solution as an Excel add-in, I would recommend renaming the VBA project (say, ReusableProgress), otherwise referencing a project named “VBAProject” from a project named “VBAProject” will surely get confusing 🙂

Note that this solution could easily be adapted to work in any VBA host application, by removing the “standard module” support and only invoking the worker code in a class module, with CallByName.

Conclusion

By using a reusable progress indicator like this, you never need to reimplement it ever again: you do it once, and then you can use it in 200 places across 100 projects if you like: not a single line of code in the ProgressIndicator or ProgressView classes needs to change – all you need to write is your worker code, and all the worker code needs to worry about is, well, its job.

Don’t hesitate to comment and suggest further improvements, suggestions are welcome – questions, too.

Downloads

I’ve bundled the code in this article into a Microsoft Excel add-in that I uploaded to dropbox (Progress.xlam).

Enjoy!

Advertisements

About attributes

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

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

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

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

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

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

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

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

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

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

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

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

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

CodeExplorerDocstrings

The Solution

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

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

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

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

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

There is no worksheet.

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

You’re free to use them, wisely.

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

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


 

Abstracting Worksheets

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

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

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

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

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

Diagram

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

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

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

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

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

Sounds familiar?

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

Not anymore.

Name things.

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

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

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

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

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

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

Public Property Get AccountNumber() As String
End Property

Public Property Get OrderNumber() As String
End Property

'...

Public Function CreateOrderHeaderEntity() As ISageEntity
End Function

Public Function CreateOrderDetailEntities() As VBA.Collection
End Function

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

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

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

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

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


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

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

    With New AccountsPresenter
        .Present proxy
    End With
End Sub

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

See, there is no worksheet!

UserForm1.Show

I’ve seen these tutorials. You’ve probably seen them too. They all go “see how easy it is?!” when they end with a glorious UserForm1.Show without explaining anything about what it means for your code and your understanding of programming concepts, to use a form’s default instance like this. Most don’t even venture into explaining anything about that default instance – and off you go, see you on Stack Overflow.

Because if you don’t know what you’re doing, all you’ve learned is how to write code that, in the name of “hey look it’s so easy”, abstracts away crucially important concepts that will, sooner or later, come back to bite you in the …rear end.

What’s that default instance anyway?

A UserForm is essentially a class module with a designer and a VB_PredeclaredId attribute. That PredeclaredId means VBA is automatically creating a global-scope instance of the class, named after that class. If the default instance is ever unloaded or set to Nothing, its internal state gets reset, and automatically reinitialized as soon as the default instance is invoked again. You can Set UserForm1 = Nothing all you want, you can never verify whether UserForm1 Is Nothing, because that expression will always evaluate to False. A default instance is nice for, say, exposing a factory method. But please, please don’t Show the default instance.


Doing. It. Wrong.™

There are a number of red flags invariably raised in many UserForm tutorials:

  • Unload Me, or worse, Unload UserForm1, in the form’s code-behind. The former makes the form instance a self-destructing object, the latter destroys resets the default instance, and that’s not necessarily the executing instance – and that leads to all kinds of funky unexpected behavior, and embarrassing duplicate questions on Stack Overflow. Every day.
  • UserForm1.Show at the call site, where UserForm1 isn’t a local variable but the “hey look it’s free” default instance, which means you’re using an object without even realizing it (at least without New​-ing it up yourself) – and you’re storing state that belongs to a global instance, which means you’re using an object but without the benefits of object-oriented programming. It also means that…
  • The application logic is implemented in the form’s code-behind. In programming this [anti-]pattern has a name: the “smart UI”. If a dialog does anything beyond displaying and collecting data, it’s doing someone else’s job. That piece of logic is now coupled with the UI, and it’s impossible to write a unit test for it. It also means you can’t possibly reuse that form for something else in the same project (heck, or for something similar in another project) without making considerable changes to the form’s code-behind. A form that’s used in 20 places and runs the show for 20 functionalities, can’t possibly be anything other than a spaghetti mess.

So that’s what not to do. Flipside.


Doing it right.

What you want at the call site is to show an instance of the form, let the user do its thing, and when the dialog closes, the calling code pulls the data from the form’s state. This means you can’t afford a self-destructing form that wipes out its entire state before the [Ok] button’s Click handler even returns.

Hide it, don’t Unload it.

In .NET’s Windows Forms UI framework (WinForms / the .NET successor of MSForms), a form’s Show method is a function that returns a DialogResult enum value, a bit like a MsgBox does. Makes sense; that Show method tells its caller what the user meant to do with the form’s state: Ok being your green light to process it, Cancel meaning the user chose not to proceed – and your program is supposed to act accordingly.

You see Show-ing a dialog isn’t some fire-and-forget business: if the caller is going to be responsible for knowing what to do when the form is okayed or cancelled, then it’s going to need to know whether the form is okayed or cancelled.

And a form can’t tell its caller anything if clicking the [Ok] button nukes the form object.

The basic code-behind for a form with an [Ok] and a [Cancel] button could look like this:

Option Explicit
'@Folder("UI")
Private cancelled As Boolean

Public Property Get IsCancelled() As Boolean
    IsCancelled = cancelled
End Property

Private Sub OkButton_Click()
    Hide
End Sub

Private Sub CancelButton_Click()
    OnCancel
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        OnCancel
    End If
End Sub

Private Sub OnCancel()
    cancelled = True
    Hide
End Sub

Notice there are two ways to cancel the dialog: the [Cancel] button, and the [X] button, which would also nuke the object instance if Cancel = True wasn’t specified in the QueryClose handler. Handling QueryClose is fundamental – not doing it means even if you’re not Unload-ing it anywhere, [X]-ing out of the form will inevitably cause issues, because the calling code has all rights to not be expecting a self-destructing object – you need to have the form’s object reference around, for the caller to be able to verify if the form was cancelled when .Show returns.

The calling code looks like this:

With New UserForm1
    .Show
    If Not .IsCancelled Then
        '...
    End If
End With

Notice there’s no need to declare a local variable; the With New syntax yields the object reference to the With block, which properly destroys the object whenever the With block is exited – hence why GoTo-jumping out and then back into a With block is never a good idea; this can happen accidentally, with a Resume or Resume Next instruction in an error-handling subroutine.

The Model

A dialog displays and collects data. If the caller needs to know about a UserName and a Password, it doesn’t need to care about some userNameBox and passwordBox textbox controls: what it cares about, is the UserName and the Password that the user provided in these controls – the controls themselves, the ability to hide them, move them, resize them, change their font and border style, etc., is utterly irrelevant. The calling code doesn’t need controls, it needs a model that encapsulates the form’s data.

LoginForm

In its simplest form, the model can take the shape of a few Property Get members in the form’s code-behind:

Public Property Get UserName() As String
    UserName = userNameBox.Text
End Property

Public Property Get Password() As String
    Password = passwordBox.Text
End Property

Or better, it could be a full-fledged class, exposing Property Get and Property Let members for every property.

The calling code can now get the form’s data without needing to care about controls and knowing that the UserName was entered in a TextBox control, or knowing the Password without knowing that the PasswordChar for the passwordBox was set to *.

Except, it can – form controls are basically public instance fields on the form object: the caller can happily access them at will… and this makes the UserName and Password interesting properties kind of lost in a sea of MSForms boilerplate in IntelliSense. So you implement the model in its own class module instead, and use composition to encapsulate it:

Private viewModel As LoginDialogModel

Public Property Get Model() As LoginDialogModel
    Set Model = viewModel
End Property

Public Property Set Model(ByVal value As LoginDialogModel)
    Set viewModel = value
End Property

The model could be updated by the textboxes – it could even expose Boolean properties that can be used to enable/disable the [Ok] button, or show/hide a validation error icon:

Private Sub userNameBox_Change()
    viewModel.UserName = userNameBox.Text
    ValidateForm
End Sub

Private Sub passwordBox_Change()
    viewModel.Password = passwordBox.Text
    ValidateForm
End Sub

Private Sub ValidateForm()
    okButton.Enabled = viewModel.IsValidModel
    userNameValidationErrorIcon.Visible = viewModel.IsInvalidUserName
    passwordValidationErrorIcon.Visible = viewModel.IsInvalidPassword
End Sub

Now, a problem remains: the caller doesn’t want to see the form’s controls.

The View

So we have a model abstraction that the view can consume, but we don’t have an abstraction for the view. That should be simple enough – let’s add a new class module and define a general-purpose IView interface:

Option Explicit
'@Folder("Abstractions")
'@Interface

Public Function ShowDialog(ByVal viewModel As Object) As Boolean
End Function

Now the form can implement that interface – and because the interface is exposing that ShowDialog method, we don’t need a public IsCancelled property anymore. I’m introducing a Private Type at this point, because I like having only one private field:

Option Explicit
Implements IView
'@Folder("UI")

Private Type TView
    IsCancelled As Boolean
    Model As LoginDialogModel
End Type

Private this As TView

Private Sub OkButton_Click()
    Hide
End Sub

Private Sub CancelButton_Click()
    OnCancel
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        OnCancel
    End If
End Sub

Private Sub OnCancel()
    this.IsCancelled = True
    Hide
End Sub

Private Function IView_ShowDialog(ByVal viewModel As Object) As Boolean
    Set this.Model = viewModel
    Show
    IView_ShowDialog = Not cancelled
End Function

The interface can’t be general-purpose if the Model property is of a type more specific than Object, but it doesn’t matter: the code-behind gets IntelliSense and early-bound, compile-time validation of member calls against it because the Private viewModel field is an implementation detail, and this particular IView implementation is a “login dialog” with a LoginDialogModel; the interface doesn’t need to know, only the implementation.

The [Ok] button will only ever be enabled if the model is valid – that’s one less thing for the caller to worry about, and the logic addressing that concern is neatly encapsulated in the model class itself.

The calling code is supplying the model, so its type is known to the caller – in fact that Property Get member is just provided as a convenience, because it makes little sense to Set a property without being able to Get it later.

Speaking of the calling code, with the addition of a Self property to the model class (Set Self = Me), it could look like this now:

Public Sub Test()

    Dim view As IView
    Set view = New LoginForm

    With New LoginDialogModel

        If Not view.ShowDialog(.Self) Then Exit Sub

        'consume the model:
        Debug.Print .UserName, .Password

    End With 'model goes out of scope

End Sub 'view goes out of scope

If you read the previous article about writing unit-testable code, you’re now realizing (if you haven’t already) that this IView interface could be implemented by some MockLoginDialog class that implements ShowDialog by returning a test-configured value, and unit tests could be written against any code that consumes an IView rather than an actual LoginForm, so long as you’ve written it in such a way that it’s the calling code that’s responsible for knowing what specific IView implementation the code is going to be interacting with.

The model’s validation logic could be unit-tested, too:

Const value As String = "1234"
With New LoginDialogModel
    .Password = value
    Assert.IsTrue(.IsInvalidPassword, "'" & value & "' should be invalid.")
End With

With a Model and a View, you’re one step away from implementing the New-ing-up a Presenter class, an abstraction that completes the MVP pattern, a much more robust way to write UI-involving code than a Smart UI is.

How to unit test VBA code?

So Rubberduck lets you write unit tests for your VBA code. If you’re learning VBA, or if you’re a seasoned VBA developer but have never written a unit test before, this can sound like a crazy useless idea. I know, because before I started working on Rubberduck, that’s how I was seeing unit tests: annoying, redundant code that tells you nothing F5/debugging doesn’t already tell you.

Right? What’s the point anyway?

First, it changes how you think about code. Things like the Single Responsibility Principle start becoming freakishly important, and you begin to break that monolithic macro procedure into smaller, more focused chunks. Future you, or whoever inherits your code, will be extremely thankful for that.

But not all VBA code should be unit-tested. Let’s see why.

Know what NOT to test

All code has dependencies. Some of these dependencies we can’t really do without, and don’t really affect anything – global-scope functions in the VBA Standard Library, for example. Other dependencies affect global state, require user input (MsgBox, InputBox, forms, dialogs, etc.) or access external resources – like a database, the file system, …or a worksheet.

For the sake of this article, say you have a simple procedure like this:

Public Sub DoSomething()
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.ConnectionString = "{connection string}"
    conn.Open
    Dim rs As ADODB.Recordset
    Set rs = conn.Execute("SELECT * FROM dbo.SomeTable")
    Sheet1.Range("A1").CopyFromRecordset rs
    conn.Close
End Sub

The problematic dependencies are:

  • conn, an ADODB connection
  • rs, an ADODB recordset
  • Sheet1, an Excel worksheet

Is that procedure doomed and completely untestable? Well, as is, …pretty much: the only way to write a test for this procedure would be to actually run it, and verify that something was dumped into Sheet1. In fact, that’s pretty much automating F5-debug: it’s an integration test, not a unit test – it’s a test, but it’s validating that all components work together. It’s not useless, but that’s not a unit test.

Refactoring

The procedure needs to be parameterless, because it’s invoked from some button: so we have a major problem here – there’s no way to factor out the dependencies!

Or is there? What if we introduced a class, and moved the functionality into there?

Now we’d be looking at this:

Public Sub DoSomething()
    With New MyTestableMacro
        .Run
    End With
End Sub

At this point we tremendously increased the macro’s abstraction level and that’s awesome, but we didn’t really gain anything. Or did we? Now that we’ve decoupled the macro’s entry point from the implementation, we can pull out the dependencies and unit-test the MyTestableMacro class! But how do we do that?

Think in terms of concerns:

  • Pulling data from a database
  • Writing the data to a worksheet

Now think in terms of objects:

  • We need some data service responsible for pulling data from a database
  • We need some spreadsheet service responsible for writing data to a worksheet

The macro might look like this now:

Public Sub DoSomething()

    Dim dataService As MyDbDataService
    Set dataService = New MyDbDataService

    Dim spreadsheetService As Sheet1Service
    Set spreadsheetService = New Sheet1Service

    With New MyTestableMacro
        .Run dataService, spreadsheetService
    End With

End Sub

Now if we think of MyDbDataService as an interface, we could conceptualize it like this:

Option Explicit
'@Folder "Services.Abstract"
'@Interface IDataService

Public Function GetSomeTable() As Variant
End Function

And if we think of Sheet1Service as an interface, we could conceptualize it like this:

Option Explicit
'@Folder "Services.Abstract"
'@Interface IWorksheetService

Public Sub WriteAllData(ByRef data As Variant)
End Sub

Notice the interfaces don’t know or care about ADODB.Recordset: the last thing we want is to have that dependency in our way, so we’ll be passing a Variant array around instead of a recordset.

Now the Run method’s signature might look like this:

Public Sub Run(ByVal dataService As IDataService, ByVal wsService As IWorksheetService)

Notice it only knows about abstractions, not the concrete implementations. All that’s missing is to make MyDbDataService implement the IDataService interface, and Sheet1Service implement the IWorksheetService interface.

Option Explicit
Implements IDataService
'@Folder "Services.Concrete"

Private Function IDataService_GetSomeTable() As Variant
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.ConnectionString = "{connection string}"
    conn.Open
    Dim rs As ADODB.Recordset
    Set rs = conn.Execute("SELECT * FROM dbo.SomeTable")
    'dump the recordset onto a temp sheet:
    Dim tempSheet As Excel.Worksheet
    Set tempSheet = ThisWorkbook.Worksheets.Add
    tempSheet.Range("A1").CopyFromRecordset rs
    IDataService_GetSomeTable = tempSheet.UsedRange.Value '2D variant array
    conn.Close
    tempSheet.Delete
End Function

Stubbing the interfaces

So here’s where the magic begins: the macro will definitely be using the above implementation, but nothing forces a unit test to use it too. A unit test would be happy to use something like this:

Option Explicit
Implements IDataService
'@Folder "Services.Stubs"

Private Function IDataService_GetSomeTable() As Variant
    Dim result(1 To 50, 1 To 10) As Variant
    IDataService_GetSomeTable = result
End Function

Public Function GetSomeTable() As Variant
    GetSomeTable = IDataService_GetSomeTable
End Function

You could populate the array with some fake results, expose properties and methods to configure the stub in every way your tests require (depending on what logic needs to run against the data after it’s dumped onto the worksheet) – for this example though all we need is for the method to return a 2D variant array, and the above code satisfies that.

Then we need a stub for the IWorksheetService interface, too:

Option Explicit
Implements IWorksheetService
'@Folder "Services.Stubs"

Private written As Boolean
Private arrayPointer As Long

Private Sub IWorksheetService_WriteAllData(ByRef data As Variant)
    written = True
    arrayPointer = VarPtr(data)
End Function

Public Property Get DataWasWritten() As Boolean
    DataWasWritten = written
End Property

Public Property Get WrittenArrayPointer() As Long
    WrittenArrayPointer = arrayPointer
End Property

Writing the tests

That’s all our test needs for now. See where this is going? DoSomething is using concrete implementations of the service interfaces that actually do the work, and a unit test can look like this:

'@TestMethod
Public Sub GivenData_WritesToWorksheet()
    'Arrange
    Dim dataServiceStub As MyDataServiceStub
    Set dataServiceStub = New MyDataServiceStub
    Dim wsServiceStub As MyWorksheetServiceStub
    Set wsServiceStub = New MyWorksheetServiceStub

    'Act
    With New MyTestableMacro
        .Run dataServiceStub, wsServiceStub
    End With

    'Assert
    Assert.IsTrue wsServiceStub.DataWasWritten
End Sub

If MyTestableMacro.Run invokes IWorksheetService.WriteAllData, this test will pass.

One more:

'@TestMethod
Public Sub WorksheetServiceWorksOffDataFromDataService()
    'Arrange
    Dim dataServiceStub As MyDataServiceStub
    Set dataServiceStub = New MyDataServiceStub
    Dim expected As Long
    expected = VarPtr(dataServiceStub.GetSomeTable)

    Dim wsServiceStub As MyWorksheetServiceStub
    Set wsServiceStub = New MyWorksheetServiceStub

    'Act
    With New MyTestableMacro
        .Run dataServiceStub, wsServiceStub
    End With

    Dim actual As Long
    actual = wsServiceStub.WrittenArrayPointer

    'Assert
    Assert.AreEqual expected, actual
End Sub

If the worksheet service receives the exact same array that the data service returned, this test should pass.

That was a relatively trivial example – the overhead (5 classes, including 2 interfaces and 2 stub implementations) is probably not justified given the simplicity of the task at hand (pull data from a database, dump that data to a worksheet). But hopefully it illustrates a number of things:

  • How to pull dependencies out of the logic that needs to be tested.
  • How to abstract the dependencies as interfaces.
  • How to implement test stubs for these dependencies, and how stubs can expose members that aren’t on the interface, for the tests to consume.
  • How unit tests document what the code is supposed to be doing, through descriptive naming.
  • VBA code can be just as object-oriented as any other code, with full-blown polymorphism and dependency injection.

Next tutorial should be about MSForms.UserForm, how not to use it, and how to test code that needs to pop a dialog. I didn’t mention anything about Rubberduck’s Fakes framework here either, but know that if one of your dependencies is a MsgBox and you have different code paths depending on whether the user clicked [Ok] or [Cancel], you can use Rubberduck’s Fakes API to literally configure how the MsgBox statement is going to behave when it’s invoked by a Rubberduck test.

Rubberduck 2.1.x

The release was going to include a number of important fixes for the missing annotation/attribute inspection and quick-fix, but instead we disabled it, along with a few other buggy inspections, and pushed the release – 7 months after 2.0.13, the last release was now over 1,300 commits behind, and we were reaching a point where we knew a “green release” was imminent, but also a point where we were going to have to make some more changes to parts of the core – notably in order to implement the fixes for these broken annotation/attribute inspections.

So we shipped what we had, because we wouldn’t jeopardize the 2.1 release with parser logic changes at that point.

Crossroads

wooden_signpost_at_the_crossroads1
By Hillebrand Steve, U.S. Fish and Wildlife Service [Public domain], via Wikimedia Commons
So here we are, at the crossroads: with v2.1.0 released, things are going to snowball – there’s a lot on our plates, but we now have a solid base to build upon. Here’s what’s coming:

  • Castle Windsor IoC: hopefully-zero user-facing changes, we’re replacing good old Ninject with a new dependency injection framework in order to gain finer control over object destruction – we will end up correctly unloading!

That’s actually priority one: the port is currently under review on GitHub, and pays a fair amount of long-standing technical debt, especially with everything involving menus.

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

For example,  adding '@Description("This procedure does XYZ") on top of a procedure will tell Rubberduck that you mean that procedure to have a VB_Description attribute; when Rubberduck parses that module after you synchronize, it will be able to use that description in the context status bar, or as tooltips in the Code Explorer.

This is considered a serious issue, because it affects pretty much every single inspection. Luckily there’s a [rather annoying and not exactly acceptable] work-around (apply the fix bottom-to-top in a module), but still.

But there’s a Greater Picture, too.

The 2.1.x Cycle

At the end of this development cycle, Rubberduck will:

  • Work in the VB6 IDE;
  • Have formalized the notion of an experimental feature;
  • Have a working Extract Method refactoring;
  • Make you never want to use the VBE’s Project References dialog ever again;
  • Compute and report various code metrics, including cyclomatic complexity and nesting levels, and others (and yes, line count too);
  • Maybe analyze a number of execution paths and implement some of the coolest code inspections we could think of;
  • Be ready to get really, really serious about a tear-tab AvalonEdit code pane.

If all you’re seeing is Rubberduck’s version check, the next version you’ll be notified about will be 2.1.2, for which we’re shooting for 2017-11-13. If you want to try every build until then (or just a few), then you’ll want to keep an eye on our releases page!

Contributor Swag!

Rubberduck is free, and always will be.

But shipping “thank you” packages to the project’s contributors isn’t. I need your help!

You use Rubberduck? You like what we’re doing? Over the few years the project has been running we’ve received extremely motivating positive feedback, tons of feature requests, and over 420 virtual hugs GitHub stars. And we still get that warm fuzzy every time.

Without Rubberduck’s contributors, we wouldn’t be anywhere near where we are today. There wouldn’t be over 50 implemented code inspections, the parser/resolver core would still be terribly slow and approximate, the Smart Indenter port wouldn’t be completed, there would be nobody to work on and fix the source control integration feature; there wouldn’t be a German translation, a regular expression analyzer tool, and so on: the project’s contributors are the real wizards shaping a dream into a reality… and I want to thank them for that, on behalf of all our users, stargazers and followers.

I can’t do this alone, so I started a GoFundMe campaign to collect enough funds to get some Rubberduck swag made and shipped to the project’s contributors.

gofundme

During the first 24 hours of the campaign, $365 were donated by only 5 contributors – so I decided to match that amount (little bit more actually) and proceeded to order some of the stuff – as of now the campaign is only 30% raised though.

So what’s the actual swag?

The Rubberduck tee has the project’s name and website on the front, and the GitHub repository URL on the back:

The Rubberduck mug has the same assets, on white and black backgrounds. Both the tees and the mugs are ordered from SpreadShirt:

There’s more to the thank you packages, but I’d like to try to keep the rest a surprise… so I’m not saying anything else about it!

Nice! Can I haz a mug?

Simple: contribute to Rubberduck, and help us bring the VBE into this century! …actually, once I have the packages ready to ship (and the funds to ship them!), I’ll know how much overstock I’m left with (if any). Expect the biggest donators to get a little something too, and if there’s still some left I might run a little contest among Rubberduck users and supporters, so… stay tuned!