One of the most peculiar aspects of Visual Basic for Applications in the ! operator, called the “bang”. 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!

The bang is particularly common in code for Microsoft Access, where you may see things like this:

strCriteria = "EmployeeID = " & Me!txtEmplIDSet rst = CurrentDb.OpenRecordSet("SELECT * FROM Employees WHERE " & strCriteria)If Not rst.EOF Then    Me!txtName  = rst!Name    Me!txtTitle = rst!Title    Me!txtHireDate = rst!HireDateEnd Ifrst.CloseSet rst = Nothing

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 Width, Height, Filter, etc. If the bang was a late-bound dot, then the following would work:

123	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:

1	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:

123456789101112131415161718

‘ **** Imaginary VBA with square bracket index access ****Dim arrLongs(5) As LongDim oRst As Recordset ‘ This would work because it is true index access:myLong = arrLongs[2] ‘ 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 forSet 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.

123456789101112131415161718192021

VERSION 1.0 CLASSBEGIN  MultiUse = -1  ‘TrueENDAttribute 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 VariantAttribute 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):

12345

Sub TestBang()    Dim oBangDemo As New BangDemo    Debug.Print “TestBang      : ” & oBangDemo!Foo    Debug.Print “TestBang      : ” & oBangDemo!BlahBlahBlahEnd Sub

The result

You will see the following printed to the Immediate window:

1234	BangDemo.Frob : Invoked FrobTestBang      : Frobbed Foo, 0x2ABangDemo.Frob : Invoked FrobTestBang      : Frobbed BlahBlahBlah, 0x2A

What this shows

• Not a member access operator: Note BangDemo has a method called Foo, but it is not invoked because the bang isn’t a member access operator. In addition, BangDemo doesn’t have any member called “BlahBlahBlah”, but oBangDemo!BlahBlahBlah works just fine, because again the bang isn’t a member access operator.
• Not a collection accessor: BangDemo is not a collection of any kind, so clearly there are no “items” to access.
• The name of the default member doesn’t matter: BangDemo does have a method called Item but that is not what is invoked by the bang because the bang doesn’t care what your methods are called. However the Frob method is invoked by the bang simply because it is marked as the default member.

VBang-operator