Posts

OOP Design Patterns: The Builder

The Builder Pattern is rarely something you need. Often a Factory Method does the job just fine, as far as creating object instances goes. But sometimes, creating an object in a valid state would require a Create method with many parameters, and that gets annoying.

There’s something rather elegant about chained member calls that build an object. The methods of a FooBuilder class return Me, so the calling code can chain the member calls and build the object in a single, expressive statement:

    Set pizza = builder _
        .OfSize(Medium) _
        .CrustType = Classic _
        .WithPepperoni _
        .WithCheese(Mozza) _
        .WithPeppers _
        .WithMushrooms _
        .Build

The Build method returns the product, i.e. the resulting object.

So a basic (and rather flawed) builder class might look like this:

Private result As Pizza

Private Sub Class_Initialize()
Set result = New Pizza
End Sub

Public Function OfSize(ByVal sz As PizzaSize) As PizzaBuilder
If result.Size = Unspecified Then
result.Size = sz
Else
Err.Raise 5, TypeName(Me), "Size was already specified"
End If
Set OfSize = Me
End Function

Public Function WithPepperoni() As PizzaBuilder
result.Toppings.Add(Pepperoni)
Set WithPepperoni = Me
End Function

'...

Public Function Build() As IPizza
Set Build = result
End Function

Every “builder method” is a Function that returns Me, and may or may not include a bit of logic to keep the result valid. Then the Build function returns the encapsulated and incrementally initialized result object.

If the return type of the Build function is an interface (that the result object implements), then the calling code can treat all pizzas equally (assuming, say, ClassicCrustPizzaPanPizza, ThinCrustPizza are different acceptable implementations of the IPizza interface… this is where the pizza example really crumbles), and the interface can very well not expose any Property Let members.

Considerations

The builder pattern is fun and very good to know, but it’s very rarely something that’s needed. But for these times when you do need it, there are a number of things to keep in mind:

  • No temporal coupling: the order in which the calling code calls the builder methods should make no difference.
  • Builder methods may not be invoked: if a pizza without a Size isn’t a valid Pizza object, then there shouldn’t be a builder method for it; either provide sensible defaults, or make a parameterized factory that creates the builder with all the non-optional values initialized.
  • Repeated invocations: the calling code might, intentionally or not, invoke a builder method more than once. This should be handled gracefully.
  • Readability: if the fluent API of a builder isn’t making the code any easier to read, then it’s probably not worth it.

You’ll think of using a builder pattern when a factory method starts having so many parameters that the call sites are getting hard to follow: a builder can make these call sites easier to read/digest.

This SoftwareEngineering.SE answer describes the actual GoF Builder Pattern (see Design Patterns: Elements of Reusable Object-Oriented Software), which takes it a notch further and makes the builder itself abstract, using a much better example than pizza. I warmly encourage you to read it; even though the code isn’t VBA, the principles are the very same regardless.

Advertisements

Autocomplete Enhancements

I got nerd-sniped. A Rubberduck user put up a feature request on the project’s repository, and I thought “we need this, yesterday”… so I did it, and the result crushed all the expectations I had – the prerelease build is here!

There are a few quirks – but rule of thumb, it’s fairly stable and works pretty well. Did you see it in action?

This feature rather impressively enhances the coding experience in the VBE – be it only with how it honors your Rubberduck/Smart Indenter settings to literally auto-indent code blocks as you type them.

Writing auto-completing VBA code, especially with auto-completing double quotes and parentheses, gives an entirely fresh new feel to the good old VBE… I’m sure you’re going to love it.

And in case you don’t, you could always cherry-pick which auto-completions you want to use, and which ones you want to disable:

AutoCompleteSettings.png


Inline Completion

These work with the current line (regardless of whether you’re typing code or a comment, or whether you’re inside a string literal), by automatically inserting a “closing” token as soon as you type an “opening” token – and immediately puts the caret between the two. These include (pipe character | depicts caret position):

  • String literals: " -> "|"
  • Parentheses: ( -> (|)
  • Square brackets: [ -> [|]
  • Curly braces: { -> {|}

Block Completion

These work with the previous line, immediately after committing it: on top of the previous line’s indentation, a standard indent width (per indenter settings) is automatically added, and the caret is positioned exactly where you want it to be. These include (for now):

  • Do -> Do [Until|While]...Loop
  • Enum -> Enum...End Enum
  • For -> For [Each]...Next
  • If...Then -> If...Then...End If
  • #If...Then -> #If...Then...#End If
  • Select Case -> Select Case...End Select
  • Type -> Type...End Type
  • While -> While...Wend
  • With...End With

On top of these standard blocks, On Error Resume Next automatically completes to ...On Error GoTo 0.


Quirks & Edge Cases

It’s possible that parenthesis completion interferes with e.g. Sub() statements (an additional opening parenthesis is sometimes added). This has been experienced and reproduced, but not consistently. If you use the feature and can reliably reproduce this glitch, please open an issue and share the repro steps with us!

On Error Resume Next will indent its body, but there currently isn’t any indenter setting for this: we need to add an indenter option to allow configuring whether this “block” should be indented or not.

Deleting or back-spacing auto-completed code may trigger the auto-complete again, once.

Line numbers are ignored, and an opening token found on the last line of a line-continuated comment will trigger a block auto-complete.

Lastly, care was taken to avoid completing already-completed blocks, however if you try hard enough to break it, you’ll be able to generate non-compilable code. Auto-completion cannot leverage the parser and only has a very limited string view of the current/committed line of code. The nice flipside of this limitation, is very nice performance and no delays in your typing.

None of these issues outweight the awesomeness of it, so all auto-completions are enabled by default.

‘Apply’ logic for UserForm dialog

A recent comment on UserForm1.Show asked about how to extend that logic to a dialog that would have an “Apply” button. This article walks you through the process – and this time, there’s a download link!

The dialog is a simple UserForm with two textboxes and 3 buttons:

ExampleDialog

The Model for this dialog is a simple class exposing properties that the two textboxes manipulate – I’ve named the class ExampleModel:

Option Explicit

Private Type TModel
    field1 As String
    field2 As String
End Type

Private this As TModel

Public Property Get field1() As String
    field1 = this.field1
End Property

Public Property Let field1(ByVal value As String)
    this.field1 = value
End Property

Public Property Get field2() As String
    field2 = this.field2
End Property

Public Property Let field2(ByVal value As String)
    this.field2 = value
End Property

I also defined a simple IDialogView interface, which can be implemented by any other dialog, since it passes the model as an Object (i.e. it’s not tightly coupled with the ExampleModel class in any way); the contract is simply “here’s your model, now show me a dialog and tell me if I can proceed to consume the model” – in other words, the caller provides an instance of the model, and the implementation returns True unless the user cancelled the form.

Option Explicit

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

The form’s code-behind therefore needs to implement the IDialogView interface, and somehow store a reference to the ExampleModel. And since we have cancellation logic but we’re not exposing it (we don’t need to – the IDialogView.ShowDialog interface handles that concern, by returning False if the dialog is cancelled), the IsCancelled flag is just internal state.

As far as the “apply” logic is concerned, the thing to note here is the Public Event ApplyChanges event, which we raise when the user clicks the “apply” button:

Option Explicit

Public Event ApplyChanges(ByVal viewModel As ExampleModel)

Private Type TView
    IsCancelled As Boolean
    Model As ExampleModel
End Type
Private this As TView

Implements IDialogView

Private Sub AcceptButton_Click()
    Me.Hide
End Sub

Private Sub ApplyButton_Click()
    RaiseEvent ApplyChanges(this.Model)
End Sub

Private Sub CancelButton_Click()
    OnCancel
End Sub

Private Sub Field1Box_Change()
    this.Model.field1 = Field1Box.value
End Sub

Private Sub Field2Box_Change()
    this.Model.field2 = Field2Box.value
End Sub

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

Private Function IDialogView_ShowDialog(ByVal viewModel As Object) As Boolean
    Set this.Model = viewModel
    Me.Show vbModal
    IDialogView_ShowDialog = Not this.IsCancelled
End Function

Private Sub UserForm_Activate()
    Field1Box.value = this.Model.field1
    Field2Box.value = this.Model.field2
End Sub

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

The Presenter class does all the fun stuff. Here I’ve decided to allow the model’s data to be optionally supplied as parameters to the Show method; the form handles its Activate event to make sure the form controls reflect the model’s initial values when the form is displayed:

Option Explicit
Private WithEvents view As ExampleDialog

Private Property Get Dialog() As IDialogView
    Set Dialog = view
End Property

Public Sub Show(Optional ByVal field1 As String, Optional ByVal field2 As String)

    Set view = New ExampleDialog

    Dim viewModel As ExampleModel
    Set viewModel = New ExampleModel
    viewModel.field1 = field1
    viewModel.field2 = field2

    If Dialog.ShowDialog(viewModel) Then ApplyChanges viewModel
    Set view = Nothing

End Sub

Private Sub view_ApplyChanges(ByVal viewModel As ExampleModel)
    ApplyChanges viewModel
End Sub

Private Sub ApplyChanges(ByVal viewModel As ExampleModel)
    Sheet1.Range("A1").value = viewModel.field1
    Sheet1.Range("A2").value = viewModel.field2
End Sub

So we have a Private WithEvents field that gets assigned in the Show method, and we handle the form’s ApplyChanges event by invoking the ApplyChanges logic, which, for the sake of this example, takes the two fields and writes them to A1 and A2 on Sheet1; if you’ve read There is no worksheet then you know how you can introduce an interface there to decouple the worksheet from the presenter, and then it doesn’t matter if you’re writing to a worksheet, a text file, or a database: the presenter doesn’t need to know all the details.

The calling code in Module1 might look like this:

Option Explicit

Public Sub ExampleMacro()
    With New ExamplePresenter
        .Show "test"
    End With
End Sub

One problem here, is that the View implementation is coupled with the presenter (i.e. the presenter is creating the view): we need the concrete UserForm type in order for VBA to see the events; without further abstraction, we can’t quite pass a IDialogView implementation to the presenter logic without popping up the actual dialog. Pieter Geerkens has a nice answer on Stack Overflow that describes how an Adapter Pattern can be used to solve this problem by introducing more interfaces, but covering this design pattern will be the subject of another article.

Private this As TSomething

A post on Code Review recently caught my attention (emphasis mine):

If you are setting up a class, don’t encapsulate a Type inside of it – you are only repeating what a class does! I am not sure where this anti-pattern comes from.

The author of these words didn’t use the term “anti-pattern” in the same way I would have… They didn’t mean it as the toxic coding practices I use it for (I know, I asked!). But they aren’t seeing the benefits of it, and ultimately consider it clutter… and that’s where we disagree, regardless of whether “anti-pattern” is incendiary wording or not.

If you’ve been reading this blog for some time, you’ve probably noticed this rather consistent (VBA code written before 2015 doesn’t count!) pattern in my writing of class modules: whenever I need a class, I start by declaring a Private Type for its private instance fields, always named after the class module itself and prefixed with an admittedly rather “Hungarian” T prefix; then the only actual private field in the class is a Private this variable, like this:

Option Explicit
Private Type TPerson
FirstName As String
LastName As String
End Type
Private this As TPerson

Public Property Get FirstName() As String
FirstName = this.FirstName
End Property

Public Property Let FirstName(ByVal value As String)
this.FirstName = value
End Property

Public Property Get LastName() As String
LastName = this.LastName
End Property

Public Property Let LastName(ByVal value As String)
this.LastName = value
End Property

The same class module would “normally” look something like this:

Option Explicit
Private mFirstName As String
Private mLastName As String

Public Property Get FirstName() As String
FirstName = mFirstName
End Property

Public Property Let FirstName(ByVal pFirstName As String)
mFirstName = pFirstName
End Property

Public Property Get LastName() As String
LastName = mLastName
End Property

Public Property Let LastName(ByVal pLastName As String)
mLastName = pLastName
End Property

Yes, it’s less code. So what’s my problem with it?

Several things.

  • Properties and their respective backing field don’t (can’t) use the same identifier.
  • That m prefix is pure clutter that’s only there to say “hey look, this is a private field /module variable!” – in other words, it’s Systems Hungarian notation and does nothing other than increase the cognitive load. Even worse with an underscore, which wrecks the consistent camelCase/PascalCase conventions of literally everything written in any VB dialect.
  • It’s not true that using such Hungarian prefixes helps with autocompletion and IntelliSense. If the class has 5 properties that happen to start with a M, then your 5 backing fields are intertwined with 10 public members (so, drowned, really) that also start with an M.
  • Mutator parameters aren’t consistent either. That p prefix is just as annoying, and I’ll go as far as to say that this m-for-member and p-for-parameter convention is exactly what’s behind the fact that many VBA programmers have never dared implementing a class module “because it’s too confusing” and hard to follow.
  • The locals debugging toolwindow becomes cluttered with all the private fields duplicating the Property Get membersvalues.
mFields-locals
The Locals toolwindow, showing fields and properties as members of Me.

With my “anti-pattern”, there’s a little bit more code, yes. But:

  • Properties and their respective backing field consistently use the same identifier. IntelliSense / autocomplete for my fields consistently only ever includes the backing fields, and all I had to do was to type this..
  • No need for any Hungarian prefix anywhere. I use T for the type declaration (I also use I for interfaces, like in .NET and most C-based languages), because I find that using the class identifier (which would be perfectly legal) would be potentially confusing in Private this As Class1, since in any other context (outside the class module itself) the identifier Class1 in an As clause would be referring to the Class1 class.
  • Parameter names are always explicitly passed ByVal and named value. Yes, this makes Range.Value show up as Range.value, but VBA being case-insensitive, it makes no difference whatsoever. I could have used any other identifier, but value is what VB.NET and C# use; besides RHS isn’t quite as sexy, if more semantically correct. But naming parameters after the property member is an objectively horrible idea; all you see is a soup of mFoo, pFoo and Foo with assignment operators in between.
  • The locals debugging toolwindow now nicely regroups all the fields under this, so the object’s state is much easier to browse and understand at a glance.
  • If you ever need to serialize an object’s state to a binary file, then all you need to do is to Put #fileHandle this and you’re done. The inverse process is just as simple: no need to enumerate the properties one by one, convert them, or manipulate them in any way.
TPerson-locals
The Locals toolwindow, showing properties as members of Me, and a collapsed this member encapsulating the otherwise redundant fields.

I’d love to hear exactly what’s wrong with this “anti-pattern” of mine – I’ve grown pretty fond of it in the past couple years, and until someone can show me how and why I’m actively hurting something somewhere with it, I’ll keep using it in my own code, and posting Code Review and Stack Overflow answers featuring it.. and my blog posts will keep using it too.

One concern raised, was that a UDT doesn’t play well with collections. But this UDT isn’t going to end up in a collection anytime soon – and even if the class instance went into a collection, the encapsulated UDT couldn’t care less: all it does is regrouping the class’ internal state. Code outside the class doesn’t know about it, and couldn’t if it wanted.

You might be worried that a UDT incurs additional overhead… but it doesn’t: it simply provides a convenient structure to organize the private fields of a class. Two Long private fields allocate 4 bytes each and total 8 bytes; a UDT with two Long members allocates a total of 8 bytes, as Len(this) shows. What’s an easy way to know how much space the instance fields of a class take up?

Rubberduck has an encapsulate field refactoring that makes a public field private, renames it, and introduces Property Get and appropriate Property Let/Set mutators for it.

For a while I’ve been considering implementing a feature that builds on this Private Type [anti?] pattern, but held back because I didn’t want Rubberduck to enforce my coding style… although… I would love to be able to just declare my private type and my this private field, parse, and then right-click the UDT field and have Rubberduck generate all the Property Get/Let/Set boilerplate for me.

Would that make it more compelling?

Factories: Parameterized Object Initialization

Creating objects is something we do all the time. When we Set foo = New Something, we create a new instance of the Something class and assign that object reference to the foo variable, which would have been declared locally with Dim foo As Something.

With New

Often, you wish to instantiate Something with initial values for its properties – might look like this:

Dim foo As Something
Set foo = New Something
With foo
    .Bar = 42
    .Ducky = "Quack"
    '...
End With

Or, you could be fancy and make Something have a Self property that returns, well, the instance itself, like this:

Public Property Get Self() As Something
    Set Self = Me
End Property

But why would we do that? Because then we can leverage the rather elegant With New syntax:

Dim foo As Something
With New Something
    .Bar = 42
    .Ducky = "Quack"
    '...
    Set foo = .Self
End With

The benefits are perhaps more apparent with a factory method:

Public Function NewSomething(ByVal initialBar As Long, ByVal initialDucky As String) As Something
    With New Something
        .Bar = initialBar
        .Ducky = initialDucky
        Set NewSomething = .Self
    End With
End Function

See, no local variable is needed here, the With block holds the object reference. If we weren’t passing that reference down the call stack by returning it to the caller, the End With would have terminated that object. Not everybody knows that a With block can own an object reference like this, using With New. Without the Self property, a local variable would be needed in order to be able to assign the return value, because a With block doesn’t provide a handle to the object reference it’s holding.

Now the calling code can do this:

Dim foo As Something
Set foo = Factories.NewSomething(42, "Quack")

Here the NewSomething function is located in a standard module (.bas) named Factories. The code would have also been legal without qualifying NewSomething with the module name, but if someone is maintaining that code without Rubberduck to tell them by merely clicking on the identifier, meh, too bad for them they’ll have to Shift+F2 (go to definition) on NewSomething and waste time and break their momentum navigating to the Factories module it’s defined in – or worse, looking it up in the Object Browser (F2).

Where to put it?

In other languages, objects can be created with a constructor. In VBA you can’t have that, so you use a factory method instead. Factories manufacture objects, they create things.

In my opinion, the single best place to put a factory method isn’t in a standard/procedural module though – it’s on the class itself. I want my calling code to look something like this:

Dim foo As Something
Set foo = Something.Create(42, "Quack")

Last thing I want is some “factory module” that exposes a method for creating instances of every class in my project. But how can we do this? The Create method can’t be invoked without an instance of the Something class, right? But what’s happening here, is that the instance is being automatically created by VBA; that instance is named after the class itself, and there’s a VB_Attribute in the class header that you need to tweak to activate it:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Something"      '#FunFact controlled by the "Name" property of the class module
Attribute VB_GlobalNameSpace = False '#FunFact VBA ignores this attribute
Attribute VB_Creatable = False       '#FunFact VBA ignores this attribute
Attribute VB_PredeclaredId = True    '<~ HERE!
Attribute VB_Exposed = False         '#FunFact controlled by the "Instancing" property of the class module

The attribute is VB_PredeclaredId, which is False by default. At a low level, each object instance has an ID; by toggling this attribute value, you tell VBA to pre-declare that ID… and that’s how you get what’s essentially a global-scope free-for-all instance of your object.

That can be a good thing… but as is often the case with forms (which also have a predeclared ID), storing state in that instance leads to needless bugs and complications.

Interfaces

The real problem is that we really have two interfaces here, and one of them (the factory) shouldn’t be able to access instance data… but it needs to be able to access the properties of the object it’s creating!

If only there was a way for a VBA class to present one interface to the outside world, and another to the Create factory method!

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

Public Property Get Bar() As Long
End Property

Public Property Get Ducky() As String
End Property

This would be some ISomething class: an interface that the Something class will implement.

The Something class would look like this- Notice that it only exposes Property Get accessors, and that the Create method returns the object through the ISomething interface:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Something"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private Type TSomething
    Bar As Long
    Ducky As String
End Type

Private this As TSomething
Implements ISomething

Public Function Create(ByVal initialBar As Long, ByVal initialDucky As String) As ISomething
    With New Something
        .Bar = initialBar
        .Ducky = initialDucky
        Set Create = .Self
    End With
End Function

Public Property Get Self() As ISomething
    Set Self = Me
End Property

Public Property Get Bar() As Long
    Bar = this.Bar
End Property

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

Public Property Get Ducky() As String
    Ducky = this.Ducky
End Property

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

Private Property Get ISomething_Bar() As Long
    ISomething_Bar = Bar
End Property

Private Property Get ISomething_Ducky() As String
    ISomething_Ducky = Ducky
End Property

The Friend properties would only be accessible within that project; if that’s not a concern then they could also be Public, doesn’t really matter – the calling code only really cares about the ISomething interface:

With Something.Create(42, "Quack")
    Debug.Print .Bar 'prints 42
    .Bar = 42 'illegal, member not on interface
End With

Here the calling scope is still tightly coupled with the Something class though. But if we had a factory interface…

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

Public Function Create(ByVal initialBar As Long, ByVal initialDuck As String) As ISomething
End Function

…and made Something implement that interface…

Implements ISomething
Implements ISomethingFactory

Public Function Create(ByVal initialBar As Long, ByVal initialDucky As String) As ISomething
    With New Something
        .Bar = initialBar
        .Ducky = initialDucky
        Set Create = .Self
    End With
End Function

Private Function ISomethingFactory_Create(ByVal initialBar As Long, ByVal initialDucky As String) As ISomething
    Set ISomethingFactory_Create = Create(initialBar, initialDucky)
End Function

…now we basically have an abstract factory that we can pass around to everything that needs to create an instance of Something or, even cooler, of anything that implements the ISomething interface:

Option Explicit

Public Sub Main()
    Dim factory As ISomethingFactory
    Set factory = Something.Self
    With MyMacro.Create(factory)
        .Run
    End With
End Sub

Of course this is a contrived example. Imagine Something is rather some SqlDataService encapsulating some ADODB data access, and suddenly it’s possible to execute MyMacro.Run without hitting a database at all, by implementing the ISomething and ISomethingFactory interfaces in some FakeDataService class that unit tests can use to test-drive the logic without ever needing to hit a database.

A factory is a creational pattern that allows us to parameterize the creation of an object, and even abstract away the very concept of creating an instance of an object, so much that the concrete implementation we’re actually coding against, has no importance anymore – all that matters is the interface we’re using.

Using interfaces, we can segregate parts of our API into different “views” of the same object and, benefiting from coding conventions, achieve get-only properties that can only be assigned when the object is initialized by a factory method.

If you really want to work with a specific implementation, you can always couple your code with a specific Something – but if you stick to coding against interfaces, you’ll find that writing unit tests to validate your logic without testing your database connections, the SQL queries, the presence of the data in the database, the network connectivity, and all the other things that can go wrong, that you have no control over, and that you don’t need to cover in a unit test, …will be much easier.

The whole setup likely isn’t a necessity everywhere, but abstract factories, factory methods, and interfaces, remain useful tools that are good to have in one’s arsenal… and Rubberduck will eventually provide tooling to generate all that boilerplate code.

Sounds like fun? Help us do it!

Coming soon, in Rubberduck 2.2

The last “green” release was a couple of months ago already – time to take a step back, look at all we’ve done, and call it a “minor” update.

What’s up duck?

Functionality-wise, not much. Bug fixes, yes; this means fewer inspection false positives, fewer caching accidents, overall more stable usage. But this time some serious progress was also made in the COM & RCW management area, and Rubberduck 2.2 no longer crashes on exit, or leave a dangling host process, or brick the VBE on reload. Some components are still stubbornly refusing to properly release, so unload+reload is still a not-recommended thing to do, but doing so no longer causes access violations. Which is neat, because this particular problem had been plaguing Rubberduck since the early days of 2.0.

Source Control Disintegration

If you haven’t been following the project since v2.1 was released, you may be disappointed to learn that we are officially dropping the source control integration feature. Not saying it’ll never resurface, but the feature was never really stable, and rather than drain our limited resources on a nice but non-essential feature, we focused on the “core” stuff for now. So instead of keeping the half-baked, half-broken thing in place, we removed it – entirely, so there’s 0 chance any part of it interferes with anything else (there were hooks in place, handling parser state changes and some VBE events).

The “Export Project” functionality remains though, so you can still use your favorite source control provider (Git, SVN, Mercurial, etc.) – Rubberduck just isn’t providing a UI to wrap that provider’s functionality anymore.

Shiny & New

We have new inspections! Rubberduck can now tell you when a Case block is semantically unreachable. Or when For loops specify a redundant Step 1, or if you prefer having an explicit Step clause everywhere, it can tell you about that too. Another inspection warns about error-handling suppression (On Error Resume Next) that is never restored (On Error GoTo 0). If you’re unfortunate enough to encounter the thoroughly evil Def[Type] statements, you’ll be relieved to know that Rubberduck will now warn you about implicitly typed identifiers.

Code Metrics is an entirely new tool, that evaluates cyclomatic complexity and nesting levels of each method and module. The feature clearly needs some UI work (wink wink, nudge nudge, C#/WPF reader), and enhancement ideas are always welcome.

The unit test execution engine no longer invokes the host application. There’s a bit of black magic going on here, but to keep it simple, the unit testing feature now works in every single VBE host application.

But the most spectacular changes aren’t really tangible, user-facing things. We’ve streamlined settings, upgrated our grammars from Antlr4.3 to Antlr4.6 – which fixed a number of parser issues, including significant performance improvements when parsing long Boolean expressions; the IInspection interface was fine-tuned again, COM object references were removed in a number of critical places. If you have a fork of the project, you already know that we’ve split Rubberduck.dll into Rubberduck.Core.dll and Rubberduck.Main.dll, with the entry point and IoC configuration in ‘Main’.

Oh, I lied. One of the most spectacular changes is a tangible, user-facing thing. It’s just not exactly in the main code base, is all. Poor installer, always gets left behind.

Administrative Privileges no longer needed!

Since a couple of pre-release builds, the Rubberduck installer supports per-user installs that no longer require admin privs. This means Rubberduck can now be installed on a locked-down workstation, without requiring IT intervention! This revamped installer also detects and properly uninstalls a previous Rubberduck install (admin elevation would be required to uninstall a per-machine installation of a previous build though), so manually uninstalling through the control panel before upgrading, is no longer recommended/needed. Doesn’t hurt, but shouldn’t change anything, really.

The “installating / instructions” and “contributing / initial setup” wiki pages have been updated accordingly on GitHub.

This new installer no longer assumes Microsoft Office is present, and registers for both 32 and 64-bit host applications.


That’s it? What happened to the rest of 2.1.x?

I did say “minor update”, yeah? The previously announced roadmap for 2.1.x was too ambitious, and not much of it is shipping in this release. In fact, that roadmap should have said “2.x”… versioning is hard, okay? If we stuck to 2.1.x, then a v2.2 would have been moot, since by then we would have had much of 3.0 in place.

Anyway, 2.2 is a terrific improvement over 2.1, on many levels – and that can only mean one thing: that the current development cycle will inevitably lead to even more awesomeness!

RD2018

VBA Trap: Default Members

The key to writing clear, unambiguous code, is rather simple:

Do what you say; say what you do.

VBA has a number of features that make it easy to not even realize you’re writing code that doesn’t do what it says it does.

One of the reasons for that, is the existence of default members – under the guise of what appears to be simpler code, member calls are made implicitly.

If you know what’s going on, you’re probably fine. If you’re learning, or you’re just unfamiliar with the API you’re using, there’s a trap before your feet, and both run-time and compile-time errors waiting to happen.

Example

Consider this seemingly simple code:

myCollection.Add ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, 1)

It’s adding a Range object, using the String representation of Range.[_Default] as a key. That’s two very different things, done by two bits of identical code. Clearly that snippet does more than just what it claims to be doing.


Discovering Default Members

One of the first classes you might encounter, might be the Collection class. Bring up the Object Browser (F2) and find it in the VBA type library: you’ll notice a little blue dot next to the Item function’s icon:

Collection.Item

Whenever you encounter that blue dot in a of members, you’ve found the default member of the class you’re looking at.

That’s why the Object Browser is your friend – even though it can list hidden members (toggled via the Object Browser‘s context menu), IntelliSense /autocomplete doesn’t tell you as much:

IntelliSense-Collection.Item

Rubberduck’s context-sensitive toolbar has an opportunity to display that information, however that wouldn’t help discovering default members:

rubberduck-collection-item.png

Until Rubberduck reinvents VBA IntelliSense, the Object Browser is all you’ve got.


What’s a Default Member anyway?

Any class can have a default member, and only one single member can be the default.

When a class has a default member, you can legally omit that member when working with an instance of that class.

In other words, myCollection.Item(1) is exactly the same as myCollection(1), except the latter is implicitly invoking the Item function, while the former is explicit about it.


Can my classes have a default member?

You too can make your own classes have a default member, by specifying a UserMemId attribute value of 0​ for that member.

Unfortunately only the Description attribute can be given a value (in the Object Browser, locate and right-click the member, select properties) without removing/exporting the module, editing the exported .cls file, and re-importing the class module into the VBA project.

An Item property that looks like this in the VBE:

Public Property Get Item(ByVal index As Long) As Variant
End Property

Might look like this once exported:

Public Property Get Item(ByVal index As Long) As Variant
Attribute Item.VB_Description = "Gets or sets the element at the specified index."
Attribute Item.VB_UserMemId = 0
End Property

It’s that VB_UserMemId member attribute that makes Item the default member of the class. The VB_Description member attribute determines the docstring that the Object Browser displays in its bottom panel, and that Rubberduck displays in its context-sensitive toolbar.

DANGER!
Rubberduck’s module rewriters work off the code in the code pane, as it appears in the VBE. If Rubberduck makes a change (e.g. a refactoring, or an inspection quick-fix) in a class module that contains member attributes, they will be lost.

This can cause compilation errors… if your code has implicit default member calls.

Whatever you do, don’t make a default member that returns an instance of the class it’s defined in. Unless you want to crash your host application as soon as the VBE tries to figure out what’s going on.


What’s Confusing About it?

There’s an open issue detailing the challenges implicit default members pose. If you’re familiar with Excel.Range, you know how it’s pretty much impossible to tell exactly what’s going on when you invoke the Cells member (see Stack Overflow).

You may have encountered MSForms.ReturnBoolean before:

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Not IsNumeric(Chr(KeyAscii)) Then KeyAscii = 0
End Sub

The reason you can assign KeyAscii = 0 and have any effect with that assignment (noticed it’s passed ByVal), is because MSForms.ReturnInteger is a class that has, you guessed it, a default member – compare with the equivalent explicit code:

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Not IsNumeric(Chr(KeyAscii.Value)) Then KeyAscii.Value = 0
End Sub

And now everything makes better sense. Let’s look at common Excel VBA code:

Dim foo As Range
foo = Range("B12") ' default member Let = default member Get / error 91
Set foo = Range("B12") ' sets the object reference '...

If foo is a Range object that is already assigned with a valid object reference, it assigns foo.Value with whatever Range("B12").Value returns. If foo happened to be Nothing at that point, run-time error 91 would be raised. If we added the Set keyword to the assignment, we would now be assigning the actual object reference itself. Wait, there’s more.

Dim foo As Variant
Set foo = Range("B12") ' foo becomes Variant/Range
foo = Range("B12") ' Variant subtype is only known at run-time '...

If foo is a Variant, it assigns Range("B12").Value (given multiple cells e.g. Range("A1:B12").Value, foo becomes a 2D Variant array holding the values of every cell in the specified range), but if we add Set in front of the instruction, foo will happily hold a reference to the Range object itself. But what if foo has an explicit value type?

Dim foo As String
Set foo = Range("B12") ' object required
foo = Range("B12") ' default member Get and implicit type conversion '...

If foo is a String and the cell contains a #VALUE! error, a run-time error is raised because an error value can’t be coerced into a String …or any other type, for that matter. Since String isn’t an object type, sticking a Set in front of the assignment would give us an “object required” compile error.

Add to that, that Range is either a member of a global-scope object representing whichever worksheet is the ActiveSheet if the code is written in a standard module, or a member of the worksheet itself if the code is written in a worksheet module, and it becomes clear that this seemingly simple code is riddled with assumptions – and assumptions are usually nothing but bugs waiting to surface.

See, “simple” code really isn’t all that simple after all. Compare to a less naive / more defensive approach:

Dim foo As Variant foo = ActiveSheet.Range("B12").Value
If Not IsError(foo) Then
    Dim bar As String
    bar = CStr(foo) '...
End If

Now prepending a Set keyword to the foo assignment no longer makes any sense, since we know the intent is to get the .Value off the ActiveSheet. We’re reading the cell value into an explicit Variant and explicitly ensuring the Variant subtype isn’t Variant/Error before we go and explicitly convert the value into a String.

Write code that speaks for itself:

  • Avoid implicit default member calls
  • Avoid implicit global qualifiers (e.g. [ActiveSheet.]Range)
  • Avoid implicit type conversions from Variant subtypes

Bang (!) Operator

When the default member is a collection class with a String indexer, VBA allows you to use the Bang Operator ! to… implicitly access that indexer and completely obscure away the default member accesses:

Debug.Print myRecordset.Fields.Item("Field1").Value 'explicit
Debug.Print myRecordset!Field1 'all-implicit

Here we’re looking at ADODB.Recordset.Fields being the default member of ADODB.Recordset; that’s a collection class with an indexer that can take a String representing the field name. And since ADODB.Field has a default property, that too can be eliminated, making it easy to… completely lose track of what’s really going on.


Can Rubberduck help / Can I help Rubberduck?

As of this writing, in theory Rubberduck has all the information it needs to issue inspection results as appropriate… assuming everything is early-bound (i.e. not written against Variant or Object, which means the types involved are only known to VBA at run-time).

In fact, there’s already an Excel-specific inspection addressing implicit ActiveSheet references, that would fire a result given an unqualified Range (or Cells, Rows, Columns, or Names) member call.

ImplicitActiveSheetReference

The inspection is currently firing a result even when the code is written in a worksheet module, making it a half-lie: without Me. qualifying the call, Range("A1") in a worksheet module is actually implicitly referring to that worksheet…and changing the code to explicitly refer to ActiveSheet would actually change the behavior of the code. That’s actually a simple bug fix that makes a good first issue for a first-time contributor! Are you this lucky person?

The reason it hasn’t been fixed yet, is because knowing whether a given “document” module is a Workbook or a Worksheet instance, is a rather complex problem that has only been solved recently.

On the other hand, an inspection to flag implicit default member calls has yet to be implemented. That’s a rather tricky one, because we need to actually evaluate the expressions involved, resolve them to a type, and determine if that type has a default member. Sounds easy? Take a stab at it!

Let-assignments involving implicit type conversions are also something we need to look into. Help us do it! This inspection also implies resolving the type of the RHS expression.

The reason these inspections haven’t been implemented yet, is because there is essentially no expression-evaluation API in place; we need to leverage our existing resolver code and expose a nice entry point to use from within an inspection. If you’re curious about Rubberduck’s internals and/or would love to learn some serious C#, don’t hesitate to create an issue on our repository to ask anything about our code base; our team is more than happy to guide new contributors in every area!

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!

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!