April 11, 2013 in VBA
One of the most peculiar aspects of Visual Basic for Applications in the
! operator, called the “bang”. It is particularly common in code for Microsoft Access, where you may see things like this:
strCriteria = "EmployeeID = " & Me!txtEmplID Set rst = CurrentDb.OpenRecordSet("SELECT * FROM Employees WHERE " & strCriteria) If Not rst.EOF Then Me!txtName = rst!Name Me!txtTitle = rst!Title Me!txtHireDate = rst!HireDate End If rst.Close Set rst = Nothing
There’s a lot of confusion about the bang operator, and nowhere have I seen the correct whole story about it. So, I present it here!
Definition: The Bang (!) Operator
What the bang operator does is simple and specific:
The bang operator provides late-bound access to the default member of an object, by passing the literal name following the bang operator as a string argument to that default member.
What it is NOT, in detail
The bang operator is not a “late-bound dot”
The most common explanation is that the “the difference between the dot operator and the bang operator is that the dot is early-bound and the bang is late-bound”. This is perpetuated in places such as the accepted answer to the StackOverflow question Bang Notation and Dot Notation in VBA and MS-Access or even a post on the Microsoft Office blog : Access Blog – Dot or Bang?.
It is true that the dot is early-bound and the bang is late-bound, but they are not equivalent beyond that. The dot is the one and only way to access a named public member of an object. The bang causes the runtime to invoke the default member of an object and pass the name following the bang as a string argument. It looks like the bang is a “late-bound dot” when working with forms (as in the blog post linked above) because forms (and reports, and some other objects) have a default member called
Controls which returns a reference to a control when given the name of a control in the form of a string. The result is that
Me!someControl works just like
Me.someControl … when working with a form or report.
As a quick demonstration of this: Note that forms also have a number of built-in properties, such as
Filter, etc. If the bang was a late-bound dot, then the following would work:
Sub Form_Load() MsgBox "I am " & Me!Width & " twips wide" End Sub
In fact, it does not work. It compiles fine because of course the bang is late-bound (there is no compile-time symbol validation). But at runtime the result is as if you had coded the following:
MsgBox "I am " & Me.Controls("Width") & " twips wide"
Of course there is no control that is called “Width”, so the code throws an error.
The bang operator is not a collection accessor, or a call to “Item”
You may also see the bang operator used against collection types, such as
Recordset or plain
Collection objects. In fact there is no requirement that the underlying object be a collection type, nor that the default method be called “Item”. It is merely convention that leads these things to normally be true.
The one and only index accessor in VBA is the parenthesized expression, which confusingly is identical to the syntax for calling a function. In fact only arrays can be indexed in VBA. Object collections of all types accomplish the same-looking syntax by providing a default member that does in fact fetch a member of the collection. Put it this way: if VBA used square brackets for index access like all the C-style languages, the difference would become clear:
' **** Imaginary VBA with square bracket index access **** Dim arrLongs(5) As Long Dim oRst As Recordset ' This would work because it is true index access: myLong = arrLongs ' This would NOT work, because it is not actually index access: Set oNameField = Recordset["Name"] ' This would still work: Set oNameField = Recordset("Name") ' ... because it is really shorthand for Set oNameField = Recorset.Fields.Item("Name") ' ... because Fields is the default member (a property) of ' Recordset, and Item is the default member (a function) of Fields
Am I just being nit-picky? I don’t think so. The difference between true index access as with arrays and invoking an Item method is the same as the very real difference between directly accessing a public field of an object (a variable declared as Public) and invoking an
Property Get method.
What it is, in detail
My definition above really says it all. To clarify I will present an example which demonstrates what it is by also showing what it is not, by constructing a simple class and then using the bang on it.
Exported Module Code for BangDemo
Note that flagging a default member in VBA is a little tricky because the IDE bundled with MS Office does not provide a way to set this flag. You can still do so by exporting the module, adding the required attribute manually, and re-importing the module. The following is the code for the BangDemo class as exported and modified to flag the default member. Save it in a file with the extension “cls” and import it to a VBA project in any Office application by choosing File > Import File from the menu in the Visual Basic editor.
VERSION 1.0 CLASS BEGIN MultiUse = -1 'True END Attribute VB_Name = "BangDemo" Option Explicit Public Function Item(ByVal Index) As Variant Item = "I'm not a collection, but Index = " & CStr(Index) End Function Public Function Foo(ByVal Arg) As Variant Foo = "Foo! Arg = " & CStr(Arg) End Function Public Function Frob(ByVal Index, _ Optional ByVal ExtraArg As Long = 42) As Variant Attribute Foo.VB_UserMemId = 0 Debug.Print "BangDemo.Frob : Invoked Frob" Frob = "Frobbed " & CStr(Index) & ", 0x" & Hex$(ExtraArg) End Function
The test code
Put the following code in a standard module, and run it (F5):
Sub TestBang() Dim oBangDemo As New BangDemo Debug.Print "TestBang : " & oBangDemo!Foo Debug.Print "TestBang : " & oBangDemo!BlahBlahBlah End Sub
You will see the following printed to the Immediate window:
BangDemo.Frob : Invoked Frob TestBang : Frobbed Foo, 0x2A BangDemo.Frob : Invoked Frob TestBang : Frobbed BlahBlahBlah, 0x2A
What this shows
- Not a member access operator: Note
BangDemohas a method called
Foo, but it is not invoked because the bang isn’t a member access operator. In addition,
BangDemodoesn’t have any member called “BlahBlahBlah”, but
oBangDemo!BlahBlahBlahworks just fine, because again the bang isn’t a member access operator.
- Not a collection accessor:
BangDemois not a collection of any kind, so clearly there are no “items” to access.
- The name of the default member doesn’t matter:
BangDemodoes have a method called
Itembut that is not what is invoked by the bang because the bang doesn’t care what your methods are called. However the
Frobmethod is invoked by the bang simply because it is marked as the default member.