Using the Bang (!) or Dot (.) in Microsoft Access

/ Uncategorized / By

The Bang! (Exclamation Operator) in VBA

In Visual Basic for Applications the ! operator, called the “bang” can often be confused with the . operator, called the “dot”.
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

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”.

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

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[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 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

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

The definition above really says it all. To clarify here is 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

The result

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 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.