VBA - Bang Syntax Part1 - Worksheets, Dictionaries and User Classes

A flurry of ideas occurred today but I will start with the ! bang syntax. The bang (!) allows a more compact syntax of code to be written. It works by passing what follows to the default member. Some examples should clarify

Sub TestWorksheetsBang()
    
    Dim ws As Excel.Worksheet
    
    Set ws = ThisWorkbook.Worksheets!Sheet1
    'is equivalent to
    Set ws = ThisWorkbook.Worksheets.Item("Sheet1")

End Sub

Curiously, the Item method does not have DispId of 0 which is what I'd expect (it has &hAA instead). Nevertheless this works like a default member. Run the above code if you don't believe me.

Next example is using the Scripting.Dictionary from the Microsoft Scripting Runtime, the default methood is item which means we can write the following code. In this case Item does has Disp of 0.

Sub TestScriptingDictionaryBang()

    Dim oDic As Scripting.Dictionary      '* Tools->References Microsoft Scripting Runtime
    Set oDic = New Scripting.Dictionary
    
    oDic.Add "green", &H80
    oDic.Add "red", &H40
    oDic.Add "blue", &HFF
    
    Debug.Assert oDic.Count = 3
    Debug.Print oDic.Item("green")
    
    
    Debug.Print oDic("green")    '* I don't much like this
    Debug.Print oDic!green       '* for compactness I'd prefer this

End Sub

Final example in this part can we write our own classes and take advantage of bang syntax. Yes we can but we have to export the class module and annotate in a text editor the default member with Attribute Item.VB_UserMemID = 0 before re-importing. Here is a class which subclasses the Scripting.Dictionary and I have already annotated the Item method.

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

Private mdic As New Scripting.Dictionary   '* Tools->References Microsoft Scripting Runtime

Public Function Add(Key, Item)
    mdic.Add Key, Item
End Function

Public Function Exists(Key)
    Exists = mdic.Exists(Key)
End Function

Public Function Keys()
    Keys = mdic.Keys
End Function

Public Function Items()
    Items = mdic.Items
End Function

Public Property Get Item(Key)
    Attribute Item.VB_UserMemID = 0
    Item = mdic.Item(Key)
End Property

Public Property Get Count() As Long
    Count = mdic.Count
End Property

Public Sub Reset()
    Set mdic = New Scripting.Dictionary
End Sub

The client code for the above class is almost identical to before. Nevertheless here it is.

Sub TestVBADictionaryBang()

    Dim oDic2 As Dictionary2
    Set oDic2 = New Dictionary2
    
    oDic2.Add "green", &H80
    oDic2.Add "red", &H40
    oDic2.Add "blue", &HFF
    
    Debug.Assert oDic2.Count = 3
    Debug.Print oDic2.Item("green")
    
    
    Debug.Print oDic2("green")
    Debug.Print oDic2!green

End Sub