This is an XY problem.

Bang notation is a means to end.

This is one of the tools available to you to extract an item from a collection .

Nothing more, nothing less.

What you want is not to use the bang operator with a variable.
You want to get an item from a collection using a variable.

Collection types have a default member, usually named Item

. Default members can be specified explicitly or implicitly available:

Dim foo As New Collection
foo.Add Item:=42, Key:="test"

      

        
        
        
      

    

Debug.Print foo.Item("test") 'explicit reference to the default member
Debug.Print foo("test") 'implicit reference to the same default member

      

        
        
        
      

    

The bang operator is another way to make an implicit call to the default collection member:

Debug.Print foo!test

      

        
        
        
      

    

All of the 3 instructions above Debug.Print

will call the foo.Item

default item for the 42

key-related output "test"

.

Square brackets

As you can see, what comes right after the statement !

is indeed a string literal... Since a string literal can contain spaces, the VB6 / VBA parser needed a way to support them.

What is [square brackets]

for:

foo.Add 72, "spaces in the key"
Debug.Print foo![spaces in the key]

      

        
        
        
      

    

Unless they constrain a string literal to denote interference, the square brackets are usually ¹ interpreted as a runtime expression for evaluating the host application. For example, this is legal (albeit dubiously advisable) in Excel VBA:

Debug.Print [A1]

      

        
        
        
      

    

The VBA parser identifies the expression in square brackets and postpones its evaluation to the host application — here Excel; at runtime , the command ends up equating to:

Debug.Print ActiveSheet.Range("A1").Value

      

        
        
        
      

    

If you don't believe that the evaluation of the parenthesized expression is deferred in the host application, consider what should happen for this print instruction 4

:

Debug.Print [Sum(2,2)]

      

        
        
        
      

    

So each of the try / permutations in your post missed the mark and made Excel try to evaluate an expression that only VBA can resolve, because it Me.VBProject.References

means nothing to Excel.

You should generally avoid the square bracket expression as you lose compile-time checks and any error can only be caught at runtime.

¹ Usually because they can also be used in some identifiers, for example in types Enum

, to make an enum member [_hidden]

.

Bottom line

Bang notation is a way to get an item from a collection using the default items and string literals look like an identifier. You cannot get it to work without hard-coding the string literal because it requires a string literal .

If you want to parameterize the retrieval of the collection, you cannot use the bang operator.

This is useful for entering code faster. If you don't know exactly how it works and what it means to you, it is a double-edged blade that hides what is really happening and ultimately makes the code harder to read and understand. Code doesn't have to be written just to run. The code must be written to be read and understood.

Note. Notation notation isn't really just for collections. It actually passes its argument as a string literal for the first parameter of whatever the default member has. I would strongly advise avoiding it for anything other than a collection class, though (such as Collection.Item

, Workbooks.Item

, Worksheets.Item

, Recordset.Fields

etc.), for the sake of common sense of future support.