Code Insights with Rubberduck + Excel

You’re writing a rather large VBA/VB6 project, and you’re starting to have a sizable amount of passing unit tests. Did you know you can copy the test results to the clipboard with a single click?

…and then paste them onto a new worksheet and turn it into a data table:

If you’re not sure what to do next, you can even let Excel give you ideas – you’ll find the Recommended Charts button under the Insert Ribbon tab:

With the count of method by component chart, we can see what test modules have more test methods; the sum of duration by component chart can show us which test modules take the longer to execute – or we could average it across test categories, or archive test results and later aggregate them… and then use this data to performance-profile problematic test scenarios.

Similarly, the “Copy to Clipboard” button from the Code Explorer can be used to export a table into Excel, and using the recommended pivot tables feature, we can get a detailed breakdown of the project – for example count of names by declaration type creates a pivot table that lists all Rubberduck declaration types, so you can easily know how many line labels your project has, or how many Declare Function imports are used:

With a little bit of filtering and creativity, we can regroup all Constant, Function, PropertyGet and Variable declarations by return type, and easily identify, say, everything that returns a Variant:

The possibilities are practically endless: the data could be timestamped and exported to some Access or SQL Server database, to feed some dashboard or report showing how a project grows over time.

How would you analyze your VBA projects? What code metrics would you like to be able to review and pivot like this? Share your ideas, or implement them, and send a pull request our way!

Advertisements

Introducing the Reference Explorer

Back in the 2.1.x announcement post over a whole year ago, one of the bullet points about the upcoming roadmap said we were going to “make you never want to use the VBE’s Project References dialog ever again“; it took a bit longer than expected, but as far as we can tell, this feature does exactly that.

If you’ve been following the project on social media recently, you already know that the next version of Rubberduck will introduce a very exciting, unique new feature: the Reference Explorer dialog, and the addition of a references node in the Code Explorer tree.

Vanilla-VBE

Since forever, adding a reference to the active project in the VBE is a rather… vanilla experience. Functional, but somewhere between bland and tedious.

What’s wrong with it?

Regardless of what we think of the very 1998-era buttons docked on the side, the dialog works. There’s a list of available libraries (sorted alphabetically), we can browse for unlisted ones, cancel or accept changes, and the libraries that are selected when the dialog is displayed, are conveniently shown at the top of the list!

On a closer look though…

The vanilla-VBE project references dialog
  1. The list of available libraries has the available libraries listed in alphabetical order. You can’t resize the dialog to show more, but you get first-key search. The Scripting runtime’s library name starts with “Microsoft”… which happens to also be the case for a few other libraries; this makes the extremely useful Scripting.Dictionary and Scripting.FileSystemObject classes pretty much hidden until you stumble upon a blog post or a Stack Overflow answer that introduces them.
  2. The selected libraries show up at the top of the list, in priority order. Locked libraries are stacked at the top. You use the up/down arrow buttons to move the selected library up or down, but you can’t move the locked ones.
  3. The priority buttons are used to determine the identifier resolution order; if an identifier exists in two or more libraries, VBA/VB6 binds to the type defined in the library with the highest priority. There’s no visual cue in the list itself to identify the locked-in type libraries, so the Enabled state of these buttons is used to convey that information: you can’t move the locked-in, default references.
  4. The bottom panel is useful… but the path gets cropped if it’s longer than the rather narrow dialog can fit, and you can’t select or copy the text. The actual library version number isn’t shown.

Visual Studio

Let’s take a look at what adding a project reference using the latest version of Microsoft Visual Studio feels like:

The Microsoft Visual Studio 2017 Reference Manager dialog

The dialog can be resized, search is no longer limited to a single character, but still limited to the beginning of the [Name]. The library info is now richer; it moved to the right side, and a panel on the left side determines the contents of the list. Other than that, besides a new [Version] column and a nice dark theme, …the mechanics are pretty much the same as they were 20 years ago: check boxes in a list. Priority is no longer relevant in .NET though – namespaces fixed that.

Rubberduck

This screenshot was taken shortly before the pull request was opened:

The Rubberduck ‘Add/Remove References’ dialog (work in process: release build may differ)
  1. Available libraries appear in a list on the left-hand side of the dialog. Like in Visual Studio, the version number appears next to the library name, and the list is sorted alphabetically. There is no checkbox: instead, the selected library can be moved into the list of referenced libraries.
  2. Referenced libraries appear in a list on the right-hand side of the dialog. Since there is no checkbox, the selected library can be moved back into the list of available libraries.
  3. Priority up/down buttons appear for the selected referenced library, unless it’s locked.
  4. Icons differentiate locked libraries, libraries that were already referenced when the dialog was shown, and libraries that were newly added. In the list of available libraries, recent and pinned libraries have an icon too.
  5. Search works on a “contains” basis, and matches the library name, description, and path. It immediately filters the list of available libraries.
  6. Tabs for quickly accessing type libraries recently referenced, or pinned libraries, or registered. Host-specific project types are in a separate tab, as applicable.
  7. Bottom panel displays the full name and path of the selected type library. The text can be selected and copied into the clipboard.
  8. Browse button allows referencing any project/library that isn’t listed anywhere. If a library can’t be loaded, it will appear in the list as a broken reference, before it’s even tentatively added to the project.

If you haven’t seen it in action yet, here’s a sneak peek:

Of course that’s just the beginning: layout is not completely final, drag-and-drop functionality remains to-do, among other enhancements.

A first iteration of this feature will likely be merged some time next week, and since this is a major, completely new feature, we’ll bump the minor version and that will be Rubberduck 2.4.0, to be released by the end of 2018…

…not too long after the imminent 2.3.1 hotfix release.

If you think this is one of the coolest things a VBE add-in could possibly do, you’re probably not alone. Share the news, and star us on GitHub!

Introducing Rubberduck v2.3.0

Version 2.2.0 was released in April 2018. Well over 1,700 commits and 2,185 modified files later, Rubberduck is now more stable than ever, and well overdue for a new release. November 25th will see Rubberduck 2.3 issued – as of this writing, we’re ironing a few wrinkles, but everything looks like we’re on track to release some time this Sunday.

A tremendous amount of effort went into the core, the engine, and the brain: the number of situations causing inspection false positives is on a serious decline, and we’ve taken very important steps towards ensuring proper tear-down of every component. Rubberduck 2.3 is by far the most stable release to date, and all the invisible work lays the foundation for the very exciting things to come.

We’re all extremely proud to present the results of so many months of hard work! Here’s a non-exhaustive overview of the new features (versus 2.2.0).

Official VB6 IDE Support

As of v2.3.0 (with special thanks to @mansellan), Rubberduck officially works in Visual Studio 6.0, the glorious, the… legendary VB6 IDE.

That’s right: code inspections, code metrics, all navigation enhancements, unit testing, refactorings, …all your favorite Rubberduck features, in the Visual Basic 6.0 editor.

VB6

This is without a doubt the biggest improvement to ever come to the VB6 IDE this century, from an open-source project.

As is the case for VBA hosts, if you already have Smart Indenter installed, Rubberduck will detect the legacy 32-bit add-in and prompt to import your settings – note that configuring Rubberduck’s indenter will not affect your Smart Indenter settings.


Autocompletion Enhancements

Rubberduck now changes how typing code in the editor feels. If you ever edited VBA/VB6 code in Notepad++ (let alone VB.NET code in the latest Visual Studio), you know that the VBE shows its age when you type a " double quote or open a ( parenthesis. With Rubberduck, typing code in the VBE will now feel radically different than without, in a very good way. A new dedicated settings page makes it easy to enable/disable each feature separately, and leaves room for future customization and enhancements.

Due to its rather invasive nature, a design decision was made to ship autocompletion features disabled by default; these features must be enabled manually, in the autocompletion settings tab of Rubberduck’s “Settings” dialog:

AutoCompleteSettings-v2.3

Self-Closing Pairs

It’s hard to describe everything enabling SCP completion does. A picture is worth a thousand words, so… how about seeing them in action?

When {BACKSPACE} is pressed and the caret immediately follows any opening token, Rubberduck attempts to locate and remote the matching closing token, wherever it is on the current logical line of code – nested or not.

Smart Concatenation

When enabled, Rubberduck will step in when the {ENTER} key is pressed while the caret is inside a string literal, to automatically append ” & _” to the current line.

The feature can also be configured so that when the {CTRL} key is held down when {ENTER} is pressed, ” & vbNewLine & _” will be appended to the current line.

{BACKSPACE} cleanly reverts smart-concatenation when the caret is on the last line of the logical code line and the caret line contains nothing but the opening & closing quotes.

Block Completion

Block completion will be implemented early in the 2.3.x cycle: these settings have no effect whatsoever for now.

The vision for this feature, is to capture “trigger” keywords (e.g. For), select them; if {TAB} or {ENTER} is pressed (as configured) when the “trigger” is selected (among other conditions), then the block expands, and Rubberduck automatically highlights a placeholder expression; hitting {TAB} again selects the next placeholder, {SHIFT}+{TAB} the previous. Providing a value for the last placeholder places the caret inside the block, indented as per indenter settings.

Auto-correct

Later in the 2.3.x cycle, auto-completion will be further enhanced with an “auto-correct” feature, which will enable automatically expanding e.g. foo++ into foo = foo + 1, among other ideas… including automatic fixing of a configurable list of “frequent typos”.


New Inspections

As with every new Rubberduck release, the team implemented a number of new inspections. This release introduces an internal API for code path analysis, which allows us to start implementing the more involved inspections on our plate!

These new inspections bring the total number to 75!

AssignmentNotUsed

The first inspection to leverage code path analysis, will now flag this code:

foo = 42 ' <~ value is never used
foo = 10
Debug.Print foo

When an assignment is subsequently discarded before the stored value is accessed, Rubberduck will notify about the redundant assignment, as a code quality issue.

DuplicatedAnnotation

This inspection, spliced from the existing “illegal annotation” inspection, helps validate/sanitize Rubberduck annotations – for example, contradicting @Folder annotations:

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

ExcelUdfNameIsValidCellReference

An Excel-specific inspection that flags public functions that are visible as worksheet user-defined functions (UDF), but shadowed by a cell reference. This inspection is particularly useful with recent 64-bit versions of Microsoft Excel, where 16,384 columns effectively reserve every 3-letter combination up to “XFC”.

Public Function Foo123() As String
'FOO123 is a valid cell reference; function cannot be invoked!
End Function

This first iteration only inspects public functions in standard procedural modules.

IsMissingOnInappropriateArgument

A rather specific inspection validating usages of IsMissing, flagging instances where the function is given a non-Variant argument.

Public Sub DoSomething(Optional ByVal foo As String)
    If IsMissing(foo) Then ' condition is always false
    End If
End Sub

IsMissingWithNonArgumentParameter

Another inspection validating usages of IsMissing, flagging instances where the function is given a non-parameter argument.

Public Sub DoSomething()
    Dim foo As Variant
    If IsMissing(foo) Then ' condition is always false
    End If
End Sub

ObsoleteCallingConvention

CDecl calling convention isn’t supported on Windows; Declare statements using it should be wrapped with conditional compilation directives so as to only compile in a Mac environment.

Private Declare Sub Beep CDecl Lib "kernel32" (dwFreq As Any, dwDuration As Any)

ObsoleteMemberUsage

Rubberduck 2.3 introduces a new @Obsolete annotation, which can be used for annotating “obsolete” procedures – the inspection flags usages of procedures marked with this annotation.

OnLocalError

The Local token is redundant in On Error statements. This inspection flags usages.

Private Sub DoSomething()
    On Local Error GoTo ErrHandler
    '...
    Exit Sub
ErrHandler:
End Sub

The rationale being, runtime errors are always local; the two syntaxes look different but do exactly the same thing.


v2.3.x

There are a number of features that were intended to be developed for 2.2.x, that didn’t make it into this release – not because the ideas were dropped, but because of mere time constraints. The add/remove references dialog is one such feature. Keep an eye out on 2.3.x pre-release builds and announcements; the v2.4 announcement will recap everything that happened in 2.3.x, but every new feature will very likely see its own dedicated blog post as it is merged and pre-released.

Clean VBA Code pt.2: Avoiding implicit code

Clean code adheres to a number of principles. Does adhering to these principles make good code? Maybe, maybe not. But it definitely helps. One thing I find myself repeating quite a lot in my more recent Stack Overflow answers, is that code should “say what it does, and do what it says” – to me this means writing explicit code. Not just having  Option Explicit specified, but avoiding the pitfalls of various “shortcuts” VBA lets us use to… cheat ourselves.

Avoid implicit member calls, write code that says what it does, and does what it says.  Instead of:

Cells(i, 2) = 42

 
Prefer explicit qualifiers, and explicit member calls:

ActiveSheet.Cells(i, 2).Value = 42

In Excel, avoid working with ActiveSheet when you mean to work with Sheet1. Use the Worksheets collection instead of the Sheets collection when you mean to retrieve a worksheet in a workbook; sheets can contain charts and other non-worksheet sheet types.

Dim targetBook As Workbook
Set targetBook = Application.Workbooks.Open(path)

Dim targetSheet As Worksheet
Set targetSheet = targetBook.Worksheets("Sheet1")

Debug.Print targetSheet.Range("A1").Value

If the sheet we need exists in ThisWorkbook at compile-time, then we don’t need a variable for it – it already exists:

Debug.Print Sheet1.Range("A1").Value

Every sheet in your Excel VBA project has a code name that you can set to any valid VBA module identifier name (up to 31 characters), and that identifier is now accessible from anywhere in your VBA project. To change the name, modify the (Name) property in the properties toolwindow (F4).


About the Bang! operator…

Avoid the Bang! operator. How many of the people using it know that the identifier to the right of the operator is a string literal that isn’t compile-time validated? It looks like early-bound code, but it isn’t. The Bang! operator is an implicit default member call against a default member that takes a string parameter. So this:

rs.Fields!Field1 = 42

Is really this:

rs.Fields.Item("Field1").Value = 42

Now, this doesn’t mean we have to go crazy and dogmatic here – default properties are idiomatic, and not necessarily toxic… when used carefully. The Item member of a collection class is, by convention, the default member of the class:

rs.Fields("Field1").Value = 42

Note that Fields is plural, which strongly signals that ("Field1") is an indexed  property accessor (it is)… and we could even infer that it returns a Field object reference. There’s an implicit default member call happening, yes, but it’s pure syntax sugar here: even if we don’t know that Fields is a class with a default Item property, we can tell that syntactically, we’re invoking something, getting an object reference back and assigning its Value property with a value.

Contrast with rs.Fields!Field1 = 42, which reads like… witchcraft, come to think of it.

As an Excel programmer I’m biased though: Access programmers probably see the Bang! operator differently. After all, it’s everywhere, in every tutorial – why would it suddenly be wrong?

Pros:

  • Faster to type (?).
  • Encourages using standard PascalCase field names and collection keys. Kinda.

Cons:

  • Confusing syntax for an unfamiliar reader; makes a string look like a member access. That one’s arguably on the reader/maintainer to read up, yes. Still.
  • No compile-time validation: what follows the ! operator is a string. Option Explicit will not save you from a typo.
  • If any explicit member call follows the string, it is inherently late-bound and not compile-time validated either; the editor will offer no intellisense for it.
  • Requires otherwise rather uncommon [square bracket] tokens around the name when the name contains spaces.

You have to put the Bang! operator in context: 25 years ago, using fully spelled-out variable names was seen as wasteful and borderline ludicrous. Code was written to be executed, not read: the faster you could type, the better. Oh, how things have changed!

Here’s a screenshot from an old, deleted Stack Overflow question about the Bang! operator in… VB.NET:

BangOperator

The Bang! operator is a relic of the past. There’s no reason to use it in modern code, be it in VBA, VB6… or VB.NET.

Self-Closing Pairs: Dancing with the VBE

A few months ago I merrily announced the first Rubberduck feature that actively interfered with typing code in the VBE. It wasn’t the first opportunity though: a rather long time ago, I flirted with the idea of triggering a parse task at every keypress, so that Rubberduck’s parse trees would always be up-to-date – but back then the parse task cancellation mechanics weren’t as fine-tuned as they are now, and it ended up being a bad idea. Interfering with typing in any way that introduces any kind of lag, or exacerbates a memory leak, can only be a bad idea.

But auto-completion was different. If done right, it would be the single best thing to happen to the VBE since Smart Indenter came along, two decades ago. So in less than two weeks I whipped up something I thought would work, got ecstatic over how awesome seeing blocks automatically completing, I announced the feature… and as feedback from the pre-release builds started coming in as bug reports, I started to realize the reason why no other VBE add-in offered a feature like this: the feature is far from trivial, and any mistake or oversight means interfering with typing code in an utterly annoying and disrupting way – the margin for error is very thin, as is the fine line between being incredibly intuitive & helpful, and being a complete pain in the neck.

The VBIDE API wasn’t made for this. The VBE wasn’t made to be extended that way.

But I’m not letting that stop me.

So I scrapped most of my hasty work, went back to the drawing board, rolled up my sleeves, and started over. At the time of this writing, block completion still hasn’t gotten the attention it deserves, for I decided to start round 2 with self-closing pairs.

As of this writing, I can confidently say that the feature is going to be rock-solid.

Fighting the VBE

The Visual Basic Editor has a soul of its own. And when you twist its arm, it slaps you back at every chance it has. To fight it, you need to know how it moves. You can’t prevent its mischievous deeds; to win, you need to embrace them, anticipate them. The extensibility API won’t let us inject a single character on the current line of code: we need to replace the entire line – and then dance with the devil.

man doing boxing
Photo by Pixabay on Pexels.com

Warm-Up

With the code panes subclassed to pick up keystrokes, VBENativeServices fires up an event that the AutoCompleteService handles (assuming settings have autocompletion enabled – failing which the event isn’t even fired). At this point if the IntelliSense drop-down is shown or the current selection isn’t at a single-character position, we immediately bail out. Otherwise, we run the self-closing pairs feature proper.

Cue Eye of the Tiger backing track…

Know where you are

We need to get the integral text of the current logical line of code (i.e. accounting for line continuations), take note of the caret position relative to the beginning of this logical line of code; take note of the line position relative to line 1 of the module as well – we encapsulate this data into a CodeString – a class that represents a logical line of code, a caret position in that logical line, with the position of this logical line in the module: that’s the original, and only the first real punch…

Know where the VBE is

The original is a trap though. If you don’t tread carefully here, you’ll take a serious one in the ribs. The problem is that because the original code is currently being edited, it’s e.g. “msgbox|” (where | would be the caret), if the keypress was " then when you mean to write “msgbox"|"” by replacing the entire current line of code, the VBE inserts that string but then the caret is now on the next line and you need to explicitly set the ICodePane.Selection value. Now dodge this: between the moment you replace the current line msgbox with msgbox"" and by the next moment you want to place the caret back to msgbox"|", if you skipped a step you have an uppercut to dodge, for at that point what’s really in the VBE is MsgBox "", so the caret ends up here: MsgBox |"". If you counter with offsetting the caret position by one, you just broke the case where the user would have typed that whitespace: msgbox "" would be off by one also: MsgBox ""|.

The solution is Judoesque: let the VBE come at you with everything it can. Embrace the flames. Fight fire with fire. The whole “prettification” trick is encapsulated in a specialized ICodeStringPrettifier object, whose role is to tell the VBE to bring it.

At the core of the prettifier, this:

module.DeleteLines(original.SnippetPosition);
module.InsertLines(original.SnippetPosition.StartLine, original.Code);

Hit me with your best shot. To work out the “prettified” version of the code, we determine the original caret position in terms of non-whitespace character count. Then we make the VBE modify the code, get the new prettifiedCode, and the caret position we want  to be at should be at the index of the nth non-whitespace character, where n is the original count. And that should get us out of trouble.

The only problem is that we don’t know which self-closing pair we’re dealing with, so it’s too early do intervene now – now that we know where the VBE stands, we need to know if we want to deliver a left or a right.

Find an opening

Once we know which SelfClosingPair to test for a result, it’s still too early to pull the prettifier trick – first we need to be sure our pair produces an output given the input, so we Execute it once, against the original code. If the pair returns a result, then we get the prettified original caret position… that way we don’t ruin the show by swinging into the void 3 times for every one time we land a hit.

One-Two

If we just hit once with everything we’ve got, the VBE will beat us again. We need a combo. First we replace the current logical line (“snippet”) with the result we got from the second Execute of the pair, which ran off the prettifier code:

result = scpService.Execute(selfClosingPair, prettified, e.Character);

module.DeleteLines(result.SnippetPosition);
module.InsertLines(result.SnippetPosition.StartLine, result.Code);

Here the VBE will prettify again, so you need to take it by surprise with a second blow – if the re-prettified code isn’t the code we’ve just written to the code pane, then we’re likely off by one and the final Selection will have to be offset:

var reprettified = module.GetLines(result.SnippetPosition);
var offByOne = result.Code != reprettified;
var finalSelection = new Selection(result.SnippetPosition.StartLine, 
                                   result.CaretPosition.StartColumn + 1)
                     .ShiftRight(offByOne ? 1 : 0);
pane.Selection = finalSelection;

If we dodged every bullet up to this point, we win… round 1.

Round 2: Backspace

Handling the pair-opening character is one thing, handling the pair-closing character is trivial. Handling backspace is fun though: we get to locate the matching character for our pair, and make both the opening and closing characters to be removed from the logical code line that we write back. Round 2 is just as riveting as round 1!

So if you have this:

foo = (| _
    (2 + 2) + 42
)

If the next keypress is BACKSPACE then you get this:

foo = | _
(2 + 2) + 42

Or given this:

foo = ( _
    (|2 + 2) + 42
)

You’d get:

foo = ( _
    2 + 2 + 42
)

We won’t be handling the DELETE key, but we’re not done yet: we can deliver another blow.

Round 3: Smart Concatenation

By handling the ENTER key and knowing whether the CTRL key was also pressed, we can turn this:

MsgBox "Lorem ipsum dolor sit amet,|"

if the next keypress is ENTER, into this:

MsgBox "Lorem ipsum dolor sit amet," & _
       "|"

and if the next keypress is CTRL+ENTER, into this:

MsgBox "Lorem ipsum dolor sit amet," & vbNewLine & _
       "|"

The VBE will only fight back with a compile error if the logical line of code contains too many line continations. We don’t have anything to do: the VBIDE API will throw an error, but Rubberduck’s wrappers simply catch that COM exception, making the line-insert operation no-op: the new line ends up not being added, no annoying message box, and the caret ends up on the next line, at the same indent.

Ding Ding Ding!

Rubberduck wins this fight for self-closing pairs, but the VBE will be back for more soon enough: it is anticipated to put up a good fight for block completion as well…

OOP Battleship Part 1: The Patterns

Battleship

About OOP

If you’ve been following this blog, you know that VBA is indeed very capable of “real” object-oriented code, regardless of what “real programmers” say about the language.

So far I’ve presented snippets illustrating patterns, and tiny example projects – the main reason I haven’t posted recently is, I’ve been busy writing a VBA project that would illustrate everything, from factory methods to unit testing and Model-View-Controller architecture. In this blog series, you will discover not only that VBA code can be very elegant code, but also why you would want to take your skills up to the next level, and write object-oriented code.

You may have been writing VBA code for well over a decade already, and never felt the need or saw a reason to write your code in class modules. Indeed, you can write code that works – OOP will not change that. At one point or another you may find yourself thinking “well that’s nice, but I’ll never need to do any of this” – and you very well might be completely right. Think of OOP as another tool in your toolbox. OOP isn’t for throw-away code or small, simple projects; OOP is for large projects that need to scale and be maintained over the years – projects you would show to a programmer in your IT department and they’d go “but why are you doing this in Excel/VBA?” …and of course the reason is “because that’s the only tool you guys are letting me use!” – for these projects (and they exist, and they’re mission-critical in every business that have them!), the structure and architecture of the code is more important than its implementation details; being easy to extend is more important than everything else: these projects are the projects that will benefit the most from OOP.

Object-Oriented VBA code is much easier to port to another language than procedural VBA code, especially with proper unit test coverage – which simply can’t be done with traditional, procedural code. In fact, OOP VBA code reads very, very much like plain VB.NET, the only difference being the syntactic differences between the two languages. If your mission-critical VBA project ever falls in the hands of your IT department, they will be extremely grateful (not to mention utterly surprised) to see its components neatly identified, responsibilities clearly separated, and specifications beautifully documented in a thorough test suite.

Is OOP necessary to make a working Battleship game in VBA? Of course not. But taking this Battleship game as a fun metaphor for some business-critical complex application, OOP makes it much easier to make the game work with the human player on Grid1 just as well as on Grid2, or making it work with an AI player on both Grid1 and Grid2, or making different difficulty levels / strategies for the AI player to use, or trashing the entire Excel-based UI and making the game work in Word, Access, or PowerPoint, or all of the above… with minimal, inconsequential changes to the existing code.

Any of the above “changing requirements” could easily be a nightmare, even with the cleanest-written procedural code. As we explore this project, you’ll see how adhering to the SOLID OOP principles makes extending the game so much easier.

But before we dive into the details, let’s review the patterns at play.


PredeclaredId / default instance

I’ve covered this before, but here’s a refresher. I find myself using this trick so often, that I’ve got a StaticClass.cls class module readily available to import in any project under my C:\Dev\VBA folder. The file looks like this:

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

The VB_PredeclaredId = True attribute is the important part. With this attribute on, the class now has a default instance. What’s critical is to avoid storing instance state in this default instance (see UserForm1.Show). But for pure functions such as factory methods, it’s golden.

Under the hood, every single object is given an ID: when you New up a class, you create a new object ID. When a class has this attribute set to True, VBA automatically pre-declares an ID for an object that’s named after the class itself.

Interfaces

Perhaps the single most powerful (yet underused) feature of VBA: the Implements keyword makes an instance of a class able to present different public interfaces to its clients. This allows us to have public mutators on a class, and yet only expose public accessors to client code that is written against an interface. More on that below.

Think of an interface as a 110V power outlet.

449px-nema_5-15_outlet_120v-15a

It doesn’t care what it’s powering, so long as it fulfills the contract: any device that operates on a standard North American 110V power outlet can be plugged into it, and it’s just going to work, regardless of whether it’s a laptop, a desktop, a monitor, or a hairdryer.

An interface is a contract: it says “anything that implements this interface must have a method that does {thing}”, without any restrictions on how that {thing} is actually implemented: you can swap implementations at any given time, and the program will happily work with that implementation, unaware and uncaring of the implementation details.

This is a very powerful tool, enabling polymorphism – one of the 4 pillars of OOP. But strictly speaking, every single object exposes an interface: its public members are its interface – what the outside world sees of them. When you make a class implement an interface, you allow that class to be accessed through that interface.

Say you want to model the concept of a grid coordinate. You’ll want to have X and Y properties, …but will you want to expose Public Property Let members for these values? The GridCoord class can very well allow it, and then the IGridCoord interface can just as well deny it, making code written against IGridCoord only able to read the values: being able to make something read-only through an interface is a very desirable thing – it’s the closest we can get to immutable types in VBA.

In VBA you make an interface by adding a class module that includes stubs for the public members you want to have on that interface. For example, this is the entire code for the IPlayer interface module:

'@Folder("Battleship.Model.Player")
Option Explicit

Public Enum PlayerType
HumanControlled
ComputerControlled
End Enum

'@Description("Gets the player's grid/state.")
Public Property Get PlayGrid() As PlayerGrid
End Property

'@Description("Identifies the player class implementation.")
Public Property Get PlayerType() As PlayerType
End Property

'@Description("Attempts to make a hit on the enemy grid.")
Public Function Play(ByVal enemyGrid As PlayerGrid) As IGridCoord
End Function

'@Description("Places specified ship on game grid.")
Public Sub PlaceShip(ByVal currentShip As IShip)
End Sub

Anything that says Implements IPlayer will be required (by the VBA compiler) to implement these members – be it a HumanPlayer or a AIPlayer.

Here’s the a part of the actual implementation for the AIPlayer:

Private Sub IPlayer_PlaceShip(ByVal currentShip As IShip)
this.Strategy.PlaceShip this.PlayGrid, currentShip
End Sub

Private Function IPlayer_Play(ByVal enemyGrid As PlayerGrid) As IGridCoord
Set IPlayer_Play = this.Strategy.Play(enemyGrid)
End Function

The HumanPlayer class does something completely different (i.e. it does nothing / lets the view drive what the player does), but as far as the game is concerned, both are perfectly acceptable IPlayer implementations.

Factory Method

VBA doesn’t let you parameterize the initialization of a class. You need to first create an instance, then initialize it. With a factory method on the default instance (see above) of a class, you can write a parameterized Create function that creates the object, initializes it, and returns the instance ready to use:

Dim position As IGridCoord
Set position = GridCoord.Create(4, 2)

Because the sole purpose of this function is to create an instance of a class, it’s effectively a factory method: “factory” is a very useful OOP pattern. There are several ways to implement a factory, including making a class whose sole responsibility is to create instances of another object. When that class implements an interface that creates an instance of a class that implements another interface, we’re looking at an abstract factory – but we’re not going to need that much abstraction here: in most cases a simple factory method is all we need, at least in this project.

Public Function Create(ByVal xPosition As Long, ByVal yPosition As Long) As IGridCoord
With New GridCoord
.X = xPosition
.Y = yPosition
Set Create = .Self
End With
End Function

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

The GridCoord class exposes Property Let members for both the X and Y properties, but the IGridCoord interface only exposes Property Get accessors for them – if we consistently write the client code against the “abstract” interface (as opposed to coding against the “concrete” GridCoord class), then we effectively get a read-only object, which is nice because it makes the intent of the code quite explicit.

Model-View-Controller

This architectural pattern is extremely widespread and very well known and documented: the model is essentially our game data, the game state – the players, their respective grids, the ships on these grids, the contents of each grid cell. The view is the component that’s responsible for presenting the model to the user, implementing commands it receives from the controller, and exposing events that the controller can handle. The controller is the central piece that coordinates everything: it’s the component that tells the view that a new game should begin; it’s also the component that knows what to do when the view says “hey just so you know, the user just interacted with cell F7”.

So the controller knows about the model and the view, the view knows about the model, and the model knows nothing about no view or controller: it’s just data.

Adapter

The adapter pattern is, in this case, implemented as a layer of abstraction between the controller and the view, that allows the former to interact with anything that implements the interfaces that are required of the latter. In other words, the controller is blissfully unaware whether the view is an Excel.Worksheet, a MSForms.Userform, a PowerPoint.Slide, or whatever: as long as it respects the contract expected by the controller, it can be the “view”.

Different view implementations will have their own public interface, which may or may not be compatible with what the controller needs to work with: quite possibly, an electronic device you plug into a 110V outlet, would be fried if it took the 110V directly. So we use an adapter to conform to the expected interface:

adapter

Or you may have taken your laptop to Europe, and need to plug it into some funny-looking 220V outlet: an adapter is needed to take one interface and make it compatible with another. This is quite literally exactly what the adapter pattern does: as long as it implements the IViewCommands interface, we can make the controller talk to it.

Autocomplete Enhancements

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

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

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

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

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

AutoCompleteSettings.png


Inline Completion

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

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

Block Completion

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

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

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


Quirks & Edge Cases

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

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

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

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

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

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

Private this As TSomething

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

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

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

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

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

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

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

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

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

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

Option Explicit
Private mFirstName As String
Private mLastName As String

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

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

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

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

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

Several things.

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

mFields-locals
The Locals toolwindow, showing fields and properties as members of Me.

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

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

TPerson-locals
The Locals toolwindow, showing properties as members of Me, and a collapsed this member encapsulating the otherwise redundant fields.

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

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

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

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

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

Would that make it more compelling?

Coming soon, in Rubberduck 2.2

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

What’s up duck?

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

Source Control Disintegration

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

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

Shiny & New

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

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

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

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

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

Administrative Privileges no longer needed!

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

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

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


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

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

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

RD2018

VBA Trap: Default Members

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

Do what you say; say what you do.

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

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

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

Example

Consider this seemingly simple code:

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

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


Discovering Default Members

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

Collection.Item

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

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

IntelliSense-Collection.Item

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

rubberduck-collection-item.png

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


What’s a Default Member anyway?

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

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

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


Can my classes have a default member?

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

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

An Item property that looks like this in the VBE:

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

Might look like this once exported:

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

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

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

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

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


What’s Confusing About it?

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

You may have encountered MSForms.ReturnBoolean before:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Write code that speaks for itself:

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

Bang (!) Operator

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

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

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


Can Rubberduck help / Can I help Rubberduck?

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

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

ImplicitActiveSheetReference

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

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

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

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

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