tutorials – Page 5 – Rubberduck News

Private this As TSomething

April 25, 2018April 25, 2018 Rubberduck VBA20 Comments

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 members‘ values.

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

April 24, 2018 Rubberduck VBA31 Comments

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!

VBA Trap: Default Members

March 15, 2018December 16, 2020 Rubberduck VBA3 Comments

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:

Whenever you encounter that blue dot in a list 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 (that’s toggled via a somewhat hidden command buried the Object Browser‘s context menu), IntelliSense /autocomplete doesn’t tell you as much:

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

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.

Whatever you do, DO NOT 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 (now closed) 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, 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.

This inspection used to fire a result even when the code was 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. Rubberduck has since been updated to understand these implications.

Another inspection flagging implicit default member calls has also been implemented with a quick-fix to expand the default member call, and bang operators can now be expanded to full notation (in the entire project at once if you like) with a single click, and inspections can flag bang notation, unbound bang notation, recursive bang notation,

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 – a capability we’re just barely starting to leverage.

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

January 12, 2018March 28, 2018 Rubberduck VBA20 Comments

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!

UserForm1.Show

October 25, 2017June 3, 2023 Rubberduck VBA47 Comments

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.

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?

October 19, 2017March 28, 2018 Rubberduck VBA21 Comments

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.

Bubbly Run-Time Errors

April 4, 2017 Rubberduck VBA7 Comments

300 feet below the surface, in a sunken wreck from another age, a rotting wooden deck silently collapses under the weight of heavy cast iron canons. As the sea floor becomes a thick cloud of millennial dust, the weaponry cracks a cask of over-aged priceless wine, and a tiny amount of air, trapped centuries ago, is freed. Under the tremendous, crushing pressure of the oceanic bottom, the bubbles are minuscule at first. As the ancestral oxygen makes its final journey from the bottom of the ocean up to the surface, the bubbles grow in size with the decreasing pressure – and when it finally reaches its destination to blend with the contemporary atmosphere, it erupts with a bubbly “plop” as it releases itself from the water that held it quietly imprisoned all these years.

Uh, so how does this relate to code in any way?

Bubbles want to explode: the same applies to most run-time errors.

When an error is raised 300 feet down the call stack, it bubbles up to its caller, then to the caller of that caller, and so on until it reaches the entry point – the surface – and blows everything up. When the error is unhandled at least.

And so they told you to handle errors. That every procedure must have an event handler.

Truth is, this is utter cargo-cultist BS. Not every procedure must handle every error. Say you have an object that’s responsible for setting up an ADODB Connection, parameterizing some SQL Command on the fly, and returning a Recordset. You could handle all errors inside that class, trap all the bubbles, and return Nothing instead of a result when something goes wrong. Neat huh? Yeah. Until the caller wants to know why their code isn’t working. That SqlCommand class cannot handle everything: errors need to bubble up to the calling code, for the calling code to handle.

The calling code might be another class module, with a function responsible for – I don’t know – pulling a list of products from a database and returning an array of strings that this function’s own caller uses to populate a ComboBox control, in a UserForm’s Initialize handler. So the data service class lets SqlCommand errors bubble up to its own caller; the UserForm’s Initialize handler receives the error, understands that it won’t be able to populate its ComboBox, and in response decides to go up in flames by bubbling up the error to its own caller – some parameterless procedure in a Macros module, that was called when the user clicked a nicely formatted shape on a dedicated worksheet.

That’s the entry point. That is where the bubbling stops. That procedure was responsible for bringing up a form for the user to enter some data, but something happened (the detailed information is in the Err object) and we can’t do that now – so we abort the form and display a nice user-friendly message in a MsgBox instead, and we can even send the actual error details into a new Outlook email to helpdesk@contoso.com.

Getting a grip on the handle

Most errors aren’t handled where they’re raised. Well, some are, obviously. But to say that every procedure should have its error handler is just as blatantly wrong as saying no procedure should ever have any error handler: “only a Sith deals in absolutes”.

So which errors should be killed on-the-spot, and which errors should be allowed to bubble up?

Avoidable errors

The vast majority of run-time errors occur due to lack of proper input validation code: we take a value and assume it’s of a type we’re expecting, or at least one we can work with. We assume its format, we assume its location, we assume …lots of things. The more assumptions code makes, the more error-prone it is. Problem is, we don’t always realize all the assumptions we make – and that’s when run-time errors come and bite us. These are completely avoidable errors: they shouldn’t be handled at all, for they are bugs. And we want bugs to blow things up. So if you have code making assumptions – for example a row number is never going to be zero – then you have bugs that are easy to fix (and that a good unit test coverage should prevent, BTW)… and it boils down, mostly, to proper input validation. Avoiding avoidable errors is the #1 best bug-preventing thing you can do.

Of course this supposes the assumptions we make are conscious ones – sometimes, code makes assumptions we don’t realize we’re making. For example, VBA code that implicitly refers to the active workshseet, often assumes that the active sheet is one specific sheet:

foo = Sheet1.Range(Cells(i, j), Cells(i, j)).Value

The above code assumes Sheet1 is active, because the two unqualified Cells calls implicitly refer to the active worksheet. Avoidable. If foo is declared as a String and Sheet1 is active, that same code will still blow up if the cell contains a #VALUE! error. Assumptions are very easy to make! Fortunately they’re also easy to avoid.

Errors you know how to handle

Sometimes you’ll run code that can raise an error even if you’ve validated all inputs – if the SQL server is down, trying to connect to it will blow up your code if you don’t handle that situation. Or the user might not be authorized to run the SQL command, or whatever. The decision on whether to handle in on-the-spot or bubbling it up to the caller, depends on how well you’ve split the responsibilities among your modules and procedures: a utility function usually has no business handling/swallowing its own errors. And unless you’re running the current [not yet released] 2.0.14.x Rubberduck build, your unit tests can’t mock up /fake a MsgBox call, so you have code paths that cannot be cleanly tested.

Looking at it from the client code’s perspective is how you’re going to know what kind of errors and “bad result” outputs you want to be dealing with. And if that client code is a unit test, then you’re probably doing the right thing, whatever that is.

Other times you’ll run into an error, but you know you can simply, gracefully and usefully recover from that error, and resume normal execution – these errors, if they can’t be avoided, should be the kind to handle on-the-spot.

Everything else

For everything else, you’ll want bubbles. Not all the way up though – you’ll want to catch them before they surface and pop in the user’s face! But if your code validates all inputs and makes little or no assumptions, and handles the specific errors you know could happen because roses are red and violets are blue… at the top of every call stack there should be a catch-all handler – an ultimate bubble catcher, that gracefully handles everything other code had to let through.

So…

Rubberduck is never going to tell you to sprinkle error-handling code everywhere. But I think we could have an inspection that warns you if you have a [possible] entry point that lets run-time errors bubble up unhandled.

What do you think? What else can Rubberduck do for you? Should Rubberduck treat any object-returning method as potentially returning Nothing, and suggest that you validate the method’s return value? You would right-click any Range.Find call, and if the returned reference is never compared against Nothing then Rubberduck could introduce an If block that does just that, making the rest of the code path safe to execute in the case of a failing call. Just thinking out loud here…

@Annotations: The Underducks

March 5, 2017June 28, 2018 Rubberduck VBA4 Comments

Some of Rubberduck’s coolest features are literally hidden – not intentionally… but exposing them in the UI just wasn’t a top priority, or proved to be quite complex to implement in a nice user-friendly way.

Sad, because it makes them look like ~~underdogs~~ underducks, when they really deserve to show up front & center.

@Folder

Since v2.0.12, adding a new test module to a VBA project makes it show up under a “Tests” folder in the Code Explorer:

folders

You might be thinking “oh cool, folders!” and then go and try to add one using the Add command, or right-click somewhere to find some “add folder” command, and eventually give up.

Folders aren’t real. VBA doesn’t support folders; the code files aren’t even code files, they’re embedded in a host document! So we can’t just “create a folder” in a VBA project, it has to be something else.

This is what an early-bound 2.0.12 test module’s declarations section looks like:

Option Explicit
Option Private Module
'@TestModule
'@Folder("Tests")

Private Assert As New Rubberduck.AssertClass

Notice the @Folder(“Tests”) comment. Folders don’t really exist, but by annotating code modules like this Rubberduck can make them seem real, at least in the Code Explorer.

You can control which module appears under which folder by modifying the annotation, using the dot (“.”) as a separator:

The Code Explorer‘s bottom panel shows the @Folder annotation that’s responsible for creating the selected folder, when a folder is selected (if no folder is specified, everything goes to a default “VBAProject” folder).

In this case:

'@Folder("Tests.Functionality2")

Or whatever you want to make it. When two or more modules have “Tests” as a “root folder”, Rubberduck knows to show these two modules under the same “Tests” folder.

This means large VBA projects with a ton of classes can now be organized in folders for easier browsing, like large VB.NET projects are organized in namespaces. Now VBA doesn’t support namespaces, the rules haven’t changed: you can’t have two same-name modules in the same VBA project regardless of which “folder” you’re putting them in. But it sure makes it much easier to organize things.

The reason we can’t have a simple “create folder” command, is ultimately because VBA doesn’t support folders: we can’t create an empty folder, a folder only exists because there’s a module that has an annotation that created it.

What if there’s more than one annotation?

Rubberduck will only ever use the first @Folder annotation it finds in a module; any subsequent @Folder annotation is ignored. So you can have this:

'@Folder("Tests")
'@Folder("Foo.Bar")

And Rubberduck won’t be confused; the Code Explorer will have that module under the “Tests” folder, and unless there’s another module somewhere that specifies “Foo.Bar”, there won’t be a “Foo.Bar” folder anywhere.

But because multiple @Folder annotations are potentially confusing for us mere mortals, we’ve implemented an inspection that warns you when a module has more than one single @Folder annotation specified:

Future versions will probably introduce a quick-fix for that inspection, so that extraneous annotations can be removed without even looking at the code module itself.

@IgnoreModule

Sometimes a single module can be responsible for a lot of inspection results, and that module can’t really be changed/fixed right now because, y’know, reasons – so you’d like to prevent Rubberduck inspections from looking at that module, so you can focus on inspection results from other modules without drowning them in noise from a module you’d like to ignore.

Since 2.0.12 you can now make code inspections completely ignore a specific module, with a single module-level annotation:

'@IgnoreModule

Now that’s great, but it’s also drastic: all inspections will ignore that module. If all you wanted was to shut off the use meaningful names inspection for that module without disabling the inspection itself, you can parameterize the annotation:

'@IgnoreModule UseMeaningfulName

And now only the use meaningful name inspection will be ignored in that module, without turning off the inspection itself.

So how do you know what inspection names to use? These names are the actual internal class names (minus the “Inspection” suffix) of each inspection in the Rubberduck code base itself, so they’re not exactly easy to get if you’re not looking at the Inspections namespace… fortunately the project’s website uses the Rubberduck build itself to create the Inspections/List page, and the inspection names appear in the bullet-list:

…of course, the website processes the names to insert spaces (based on the PascalCase casing – that’s why ByVal appears as “By Val”), so the actual usable @Ignore and @IgnoreModule annotation parameters are all in that list, except you need to remove the spaces when using them.

The @Ignore annotation uses the same mechanism, except it works at individual inspection result level; the Ignore Once quickfix that’s available for most inspections, automatically inserts @Ignore annotations, but there’s currently no way to automatically add an @IgnoreModule annotation – future versions will most definitely fix that though.

OOP VBA pt.2: Factories and Cheap Hotels

July 5, 2016July 5, 2016 Rubberduck VBA26 Comments

When writing OOP code in VBA, it’s important to keep a number of things in mind:

A class can be given a default instance, which makes all its public members usable without needing to create a new instance.
An interface can very well expose only public property get accessors, but no mutator.
A class Implements as many interfaces as needed.
Events cannot be exposed by an interface.

VB_Attributes

If you ever exported a class module and examined it in your favorite text editor, you probably noticed these:

Attribute VB_Name = "Class1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False

The VB_Name attribute determines the identifier the class will be referred to in code; VB_GlobalNameSpace makes its members global which is kinda anti-OOP.. VB_Creatable can only be False in VBA projects, and means that other VBA projects cannot directly create a new instance of that class. VB_Exposed determines whether other VBA projects can see this class or not.

The one we’re interested in, is VB_PredeclaredId. If you export a UserForm module, you’ll notice it’s predeclaredId attribute is True. This is what allows you to work against a form without creating an instance – you’re using the default instance when you do that.. and you shouldn’t.

Normally.

Finding the cheapest hotel

Here’s a little problem that I’m going to solve in VBA, with full-blown OOP:

A hotel chain operating in Goa wishes to offer room reservation services. They have three hotels in Goa: GreenValley, RedRiver and BlueHills. Each hotel has separate weekday and weekend (Saturday and Sunday) rates. There are special rates for rewards customer as a part of loyalty program.Each hotel has a rating assigned to it.

GreenValley with a rating of 3 has weekday rates as Rs1100 for regular customer and Rs800 for rewards customer. The weekend rates are 900 for regular customer and 800 for a rewards customer.

RedRiver with a rating of 4 has weekday rates as Rs1600 for regular customer and Rs1100 for rewards customer. The weekend rates are 600 for regular customer and 500 for a rewards customer.

BlueHills with a rating of 5 has weekday rates as Rs2200 for regular customer and Rs1000 for rewards customer. The weekend rates are 1500 for regular customer and 400 for a rewards customer.

IMPORTANT: Before you read any further

This exercise isn’t about solving the problem. The problem is rather easy to solve. It’s about managing changes, writing code that can survive changes. Specifications are never carved in stone, they change all the time. Today the hotel chain has 3 hotels, tomorrow they might have 3,500. Today the hotel chain has two types of customers. Tomorrow they might have three; eventually the chain acquires another chain in another country, and then the prices need to be converted between USD and EUR before they can be compared. The foreign hotels might have different statutory holidays, and it wouldn’t matter until the CEO decided that July 4th reservations would be 25% off, but only in the US hotels.

This solution isn’t the one OOP way to do things. It’s a solution; your mileage may vary. There are many, many ways to do this – but a monolithic block of procedural code wouldn’t survive very long with the hectic reality depicted above, would it? Or it would, but then bugs would start appearing, and more changes would have to be made, perhaps introducing new bugs, too. Sounds familiar? Keep reading.

Okay. Ready?

So, let’s say I want to store information about some pricing rule, based on some DateType and some CustomerType. I could describe this type as follows (the enums don’t belong to the interface, they’re just public types that were convenient to define there):

Option Explicit

Public Enum CustomerType
    Regular
    Premium
End Enum

Public Enum DateType
    WkDay
    WkEnd
End Enum

Public Property Get DateType() As DateType
End Property

Public Property Get CustomerType() As CustomerType
End Property

Public Function ToString() As String
End Function

Let’s call this interface IPricingRuleInfo.

In well-designed OOP, one doesn’t design an interface to change. This IPricingRuleInfo interface will change, as soon as the requirements change and we need to expose a new property. But we’re going to use VBA interfaces differently here… just bear with me.

What we’re going to do with this interface, is a façade that the program will be written against, while we hide the implementation details.

The implementation would look like this:

Option Explicit

Private Type TInfo
    DateType As DateType
    CustomerType As CustomerType
End Type
Private this As TInfo

Implements IPricingRuleInfo

Public Property Get CustomerType() As CustomerType
    CustomerType = this.CustomerType
End Property

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

Public Property Get DateType() As DateType
    DateType = this.DateType
End Property

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

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

Public Function Create(ByVal dtType As DateType, ByVal custType As CustomerType) As IPricingRuleInfo
    With New PricingRuleInfo
        .DateType = dtType
        .CustomerType = custType
        Set Create = .Self
    End With
End Function

Private Property Get IPricingRuleInfo_CustomerType() As CustomerType
    IPricingRuleInfo_CustomerType = this.CustomerType
End Property

Private Property Get IPricingRuleInfo_DateType() As DateType
    IPricingRuleInfo_DateType = this.DateType
End Property

Private Function IPricingRuleInfo_ToString() As String
    IPricingRuleInfo_ToString = CStr(this.CustomerType) & ";" & CStr(this.DateType)
End Function

Notice this Create method: that’s the Factory Method, intended to be used from the default instance. The properties are instance members that really belong to an instance of the class; the implementation also exposes Property Let accessors, so that Create can assign the proprerty values of the instance to create and return.

The Self getter is a little trick that enables this neat With New syntax.

The private type helps remove awkward prefixes by legalizing identical field and property names, and if the class’ state ever needs to be serialized, it’s child play.

Any code that works with a PricingRuleInfo instance will have access to its setters and default instance. But the client code wouldn’t do that: the client code works with the IPricingRuleInfo interface, and know nothing of a default instance, a factory method, or Property Let members: it only sees CustomerType and DateType read-only values, and a ToString method that concatenates them into a string.

And now we can have an IPricingRule interface like this:

Option Explicit

Public Property Get RuleInfo() As IPricingRuleInfo
End Property

Public Function Evaluate(ByVal info As IPricingRuleInfo) As Currency
End Function

And then we can have as many implementations as we like – here, a simple one called FixedAmountPricingRule, that takes an amount at creation, encapsulates it, and then uses it to return a fixed amount when evaluating the rule:

Option Explicit

Private Type TRule
    RuleInfo As IPricingRuleInfo
    Amount As Currency
End Type
Private this As TRule

Implements IPricingRule

Private Property Get IPricingRule_RuleInfo() As IPricingRuleInfo
    Set IPricingRule_RuleInfo = this.RuleInfo
End Property

Private Function IPricingRule_Evaluate(ByVal info As IPricingRuleInfo) As Currency
    IPricingRule_Evaluate = this.Amount
End Function

Public Property Get RuleInfo() As IPricingRuleInfo
    Set RuleInfo = this.RuleInfo
End Property

Public Property Set RuleInfo(ByVal value As IPricingRuleInfo)
    Set this.RuleInfo = value
End Property

Public Property Get Amount() As Currency
    Amount = this.Amount
End Property

Public Property Let Amount(ByVal value As Currency)
    this.Amount = value
End Property

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

Public Function Create(ByVal info As IPricingRuleInfo, ByVal value As Currency) As IPricingRule
    With New FixedAmountPricingRule
        Set .RuleInfo = info
        .Amount = value
        Set Create = .Self
    End With
End Function

Again, we give this class a default instance by setting its VB_PredeclaredId attribute to True and re-importing the module into the project.

Next we’ll need an abstraction for hotels – enter IHotel:

Option Explicit

Public Property Get Name() As String
End Property

Public Property Get Rating() As Byte
End Property

Public Function CalculatePricing(ByVal info As IPricingRuleInfo) As Currency
End Function

Public Function GetDateType(ByVal value As Date) As DateType
End Function

Notice how the interface exposes nothing of IPricingRule. The implementation has a dependency on IPricingRule and IPricingRuleInfo, but knows nothing of the concrete types. Here’s the code:

Option Explicit

Private Type THotel
    PricingRules As New Scripting.Dictionary
    Name As String
    Rating As Byte
End Type
Private this As THotel
Implements IHotel

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

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

Public Property Get Rating() As Byte
    Rating = this.Rating
End Property

Public Property Let Rating(ByVal value As Byte)
    this.Rating = value
End Property

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

Public Function Create(ByVal hotelName As String, ByVal stars As Byte, Optional ByVal rules As Collection = Nothing) As StandardHotel
 
    Dim rule As IPricingRule
    With New StandardHotel
 
        .Name = hotelName
        .Rating = stars
 
        If Not rules Is Nothing Then
            For Each rule In rules
                .AddPricingRule rule
            Next
        End If
 
        Set Create = .Self
 
    End With

End Function

Public Sub AddPricingRule(ByVal rule As IPricingRule)
    this.PricingRules.Add rule.RuleInfo.ToString, rule
End Sub

Private Function IHotel_CalculatePricing(ByVal info As IPricingRuleInfo) As Currency
    Dim rule As IPricingRule
    Set rule = this.PricingRules(info.ToString)
    IHotel_CalculatePricing = rule.Evaluate(info)
End Function

Private Function IHotel_GetDateType(ByVal value As Date) As DateType
    IHotel_GetDateType = IIf(Weekday(value, vbMonday) <= 5, WkDay, WkEnd)
End Function

Private Property Get IHotel_Name() As String
    IHotel_Name = this.Name
End Property

Private Property Get IHotel_Rating() As Byte
    IHotel_Rating = this.Rating
End Property

Notice the GetDateType function: it allows a given IHotel implementation to come up with funky creative ways to determine the DateType for a given date value.

Also interesting, the AddPricingRule procedure, which isn’t exposed by the IHotel interface, but that adds pricing rules to the encapsulated dictionary of pricing rules; given an IPricingRuleInfo instance, we can now calculate the price by evaluating the rule.

The HotelFinder class is just an object that encapsulates the logic to find the cheapest hotel, given two dates and a CustomerType:

Option Explicit

Private Type TFinder
    Hotels As Collection
End Type
Private this As TFinder

Public Property Get Hotels() As Collection
    Set Hotels = this.Hotels
End Property

Public Function FindCheapestHotel(ByVal fromDate As Date, ByVal toDate As Date, ByVal custType As CustomerType) As String

    Dim place As IHotel
    Dim checkedDate As Date

    Dim cheapestAmount As Currency
    Dim cheapestHotel As IHotel
 
    Dim hotelTotal As Currency
    For Each place In this.Hotels
 
        hotelTotal = 0
        For checkedDate = fromDate To toDate
            Dim info As IPricingRuleInfo
            Set info = PricingRuleInfo.Create(place.GetDateType(checkedDate), custType)
            hotelTotal = hotelTotal + place.CalculatePricing(info)
        Next
 
        If cheapestAmount = 0 Or hotelTotal < cheapestAmount Then
            cheapestAmount = hotelTotal
            Set cheapestHotel = place
        ElseIf hotelTotal = cheapestAmount And cheapestHotel.Rating > place.Rating Then
            'same price, but higher rating; higher rating gets precedence
            Set cheapestHotel = place
        End If
 
        Debug.Print place.Name, Format(hotelTotal, "$#,##0.00")
    Next
 
    FindCheapestHotel = cheapestHotel.Name

End Function

Private Sub Class_Initialize()
    Set this.Hotels = New Collection
End Sub

Private Sub Class_Terminate()
    Set this.Hotels = Nothing
End Sub

So, we iterate a collection of hotels, evaluate the stay at each one (output the amount to the debug pane), and return the name of the cheapest hotel.

At the top of the call stack lies a procedure that creates an instance of that HotelFinder, populates its Hotels collection, and ouputs the result of the FindCheapestHotel function. This is where we reap the benefits of OOP: initializing the hotels reads pretty much exactly like reading the specs.

Option Explicit

Public Sub Test(ByVal checkin As Date, ByVal checkout As Date, ByVal custType As CustomerType)
    Dim finder As New HotelFinder
    InitializeHotels finder
    Debug.Print finder.FindCheapestHotel(checkin, checkout, custType)
End Sub

Private Sub InitializeHotels(ByVal finder As HotelFinder)

    With StandardHotel.Create("Green Valley", 3)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Premium), 800)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Premium), 800)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Regular), 1100)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Regular), 900)
        finder.Hotels.Add .Self
    End With
 
    With StandardHotel.Create("Red River", 4)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Premium), 1100)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Premium), 500)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Regular), 1600)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Regular), 600)
        finder.Hotels.Add .Self
    End With
 
    With StandardHotel.Create("Blue Hills", 5)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Premium), 1000)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Premium), 400)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkDay, Regular), 2200)
        .AddPricingRule FixedAmountPricingRule.Create(PricingRuleInfo.Create(WkEnd, Regular), 1500)
        finder.Hotels.Add .Self
    End With
 
End Sub

And we get output:

Test Now, Now + 3, Premium
Green Valley $3,200.00
Red River $4,400.00
Blue Hills $4,000.00
Green Valley

Is that over-engineered? As I said above, most definitely. But then, how would a Java, C#, or VB.NET solution look like? Not much different, save a PricingStrategyFactoryFactory class for the Java code of course! The point, again, was an exercise in writing code resistant to change, not just solving a problem. Now when the specs change and we need a new pricing rule that grants 20% off on the first Tuesday of every second month, we don’t need to change any code except for the code that initializes the hotels: we just implement the new functionality, without changing code that already works: that’s the Open/Closed Principle at play. In fact, I tried to depict all of SOLID in this code – I hope I did something like that.

OOP VBA pt.1: Debunking Stuff

June 16, 2016 Rubberduck VBALeave a comment

Ever seen that one?

It’s not a real language

The thing is, object-oriented code can definitively be written in VBA. This series of posts shows how. Let’s first debunk a few myths and misconceptions.

VBA classes don’t have constructors!

What’s a constructor but a tool for instantiating objects? In fact there are many ways to create objects, and in SOLID OOP code there shouldn’t be much Newing-up going on anyway: you would be injecting a factory or an abstract factory instead, to reduce coupling. VBA is COM, and COM loves factories. No constructors? No problem!

VBA code is inherently coupled with a UI or spreadsheet

In OOP, the ideal code has low coupling and high cohesion. This means code that doesn’t directly depend on MsgBox, or any given specific Worksheet or UserForm. Truth is, OOP code written in VB.NET or C# be it with WinForms or WPF UI frameworks, faces the same problems and can easily be written in the same “Smart UI” way that makes the UI run the show and the actual functionality completely untestable: bad code is on the programmer, not the language. And spaghetti code can be written in any language. The very same principles that make well-written VB.NET, C#, or Java code be good code, are perfectly applicable to VBA code.

Writing Object-Oriented VBA code is painful

Okay, point. The VBE’s Project Explorer does make things painful, by listing all class modules alphabetically under the same folder: it’s as if the IDE itself encouraged you to cram as much functionality as possible in as few modules as possible! This is where Rubberduck’s Code Explorer comes in to save the day though: with a simple comment annotation in each class’ declarations section, you can easily organize your project into virtual folders, nest them as you see fit, and best of all you can have a form, a standard module and a dozen class modules under the same folder if you want. There’s simply no reason to avoid VBA code with many small specialized class modules anymore.

OOP is overkill for VBA

After all, VBA is just “macros”, right? Procedural code was good enough back then, why even bother with OOP when you’re writing code in a language that was made to “get things done”, right? So we go and implement hundreds of lines of code in a worksheet event handler; we go and implement dialogs and thousands of lines of code in a form’s code-behind; we declare dozens upon dozens of global variables because “that’s how it was made to work”. Right? Nope.

It works, and everyone’s happy. Until something needs to change, and something else needs to change the week after, and then another feature needs to be added the next week, then a bug needs to be fixed in that new feature, and then fixing that bug ripples in unexpected places in the code; the beast eventually grows hair and tentacles, and you’re left sitting in front of a spaghetti mess.

And it’s hard to maintain, not because it’s VBA, but because it was written “to get things done”, but not to be maintained. This “ball of mud” code can happen in any language: it’s not the language, it’s the mentality. Most VBA developers are not programmers – code gets written the way it is because doing things in a SOLID way feels like going to the Moon and back to end up next door with the exact same functionality… and lots simply don’t know better, because nobody ever taught them. At least, that’s how it started for me.

Then there’s the IDE. You would like to refactor the code a bit, but there are no refactoring tools and no unit tests, and every change you make risks breaking something somewhere, because knowing what’s used where is terribly painful… and there’s no integrated source control, so if you make a change that the undo button doesn’t remember, you better remember what it looked like. And eventually you start commenting-out a chunk of code, or start having DoSomething_v2 procedures, and then DoSomething3. Soon you don’t know which code calls which version and you have more comments than live code. Without source control, it’s impossible to revert back to any specific version, and short of always working off a copy of the host document, code changes are done at the risk of losing everything.

No safety net. Pretty much no tooling. The VBE makes it pretty hard to work with legacy code – at least, harder than with a more modern, full-featured IDE.

Rubberduck will change that: Rubberduck wants to make writing object-oriented VBA code as enjoyable as in a modern IDE, and maintaining and refactoring legacy procedural code as easy and safe as possible.

Is OOP overkill for VBA? If it’s not overkill for even the tiniest piece of modern-language code, then I fail to see why it would be overkill for any VBA project. After all, SOLID principles are language-agnostic, and the fact that VBA doesn’t support class inheritance does nothing to affect the quality of the code that’s possible to achieve in VBA.

Wait, how would SOLID even apply to VBA?

The Single Responsibility Principle is a golden rule that’s as hard to follow in VBA as it is in any other language: write small procedures and functions that do one thing, prefer many small specialized modules over fewer, large ones.

The Open/Closed Principle, which leaves classes open for extension, closed for modification is even harder to get right, again regardless of the language. However like the others, if the other 4 principles are followed, then this one is just common sense.

Liskov Substitution Principle involves no wizardry, it’s about writing code so that an implementation of an interface guarantees that it does what the interface says it’s doing, so that any given implementation of an interface can be injected into the code, it will still run correctly.

The Interface Segregation Principle goes hand in hand with the other principles, and keeps your code cohesive, focused. Interfaces should not leak any specific implementation; an interface with too many members sounds like breaking SRP anyway.

The Dependency Inversion Principle is possibly the one that raises eyebrows, especially if you don’t know that VBA classes can implement interfaces. Yet it’s perfectly possible to write code against an IMsgBox interface, inject a MsgBoxImpl class in the production code, and inject a MsgBoxStub class in the test code.

See? Nothing VBA can’t handle. So object-oriented VBA code is theoretically possible. In the next couple of weeks we’ll go over what it means in real-world VBA code, in terms of project architecture, design patterns, and code design in general.