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.
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.
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
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
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 '...
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 '...
foo is a
Variant, it assigns
Range("B12").Value (given multiple cells e.g.
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 '...
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
Write code that speaks for itself:
- Avoid implicit default member calls
- Avoid implicit global qualifiers (e.g.
- Avoid implicit type conversions from
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
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
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!
3 thoughts on “VBA Trap: Default Members”
Another danger using default members:
Say that in a worksheet, A1 value is C3
Write “Hello” in cell C3.
In VBA, this works:
But this doesn’t work:
LikeLiked by 1 person
I will be investigating that one. See I lied a little: the default member of Range isn’t its Value property, it’s a hidden [_Default] member that *appears* to ultimately resolve to Value… but who knows how it’s implemented… What’s the content of A1?
[…] upper left corner as in or . Some classes designate a default members while others don’t. https://rubberduckvba.wordpress.com/2018/03/15/vba-trap-default-members/ is a great post that explains […]
LikeLiked by 1 person