Let's talk about one of the most confusing aspects of Microsoft Access's VBA syntax.
A simple textbox
We'll start with a form and add a single textbox to it. Next, let's add a command button to the form. We've got three controls:
Command2. Let's add some code to the button's on click event:
Private Sub Command2_Click() Debug.Print "Bang: "; Me!Text0 Debug.Print "Dot: "; Me.Text0 Debug.Print "Default: "; Text0 End Sub
Now, let's switch to Form view, enter "Test" in the text box, and click [Command2].
Here are the results in the immediate window:
Bang: Test Dot: Test Default: Test
So far, so good. These three approaches seem pretty interchangeable.
Now, let's declare a local variable in our code-behind named
Private Sub Command2_Click() Dim Text0 As String Debug.Print "Bang: "; Me!Text0 Debug.Print "Dot: "; Me.Text0 Debug.Print "Default: "; Text0 End Sub
Look what happens when we click the [Command2] button now:
Bang: Test Dot: Test Default:
What happened? VBA, like most languages, resolves variables in scope priority, where the closest scope wins. In this case, we introduced a new local variable that happens to match the name of the textbox on our form. Now, when we use the identifier
Text0 in our routine, it resolves to the local variable and not our text box.
"Text0" is not a very descriptive name for our text box. Since we are going to use that text box on our form to prompt the user for their name, let's go ahead and rename it from "Text0" to "Name". I changed the Label1 caption, too.
With the change in control name, we have to update our code-behind or it won't compile:
Private Sub Command2_Click() Dim Name As String Debug.Print "Bang: "; Me!Name Debug.Print "Dot: "; Me.Name Debug.Print "Default: "; Name End Sub
Who can guess what will happen when we click [Command2] now?
Bang: John Doe Dot: Form1 Default:
The Default is blank due to scope priority as before. But now the Dot operator is returning the name of the form instead of the contents of the Name textbox. That's because Name is a named public member of the Form object.
Now we're getting into the difference between Dot and Bang. The best definition of the bang operator I've read comes from Josh Honig at bytecomb.com:
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.
Contrast this with Josh's definition of the Dot operator:
The dot is the one and only way to access a named public member of an object.
Read Josh's entire article. It's the best explanation of the differences between VBA's dot and bang operators anywhere on the internet.
So...always use bang?
After working through my examples, one might come to the conclusion that since the Default and Dot operators each failed, the Bang operator is the obvious choice.
Not so fast! The bang operator has flaws of its own. Most notably, since it is late-bound, we lose the benefit of compile-time checking.
Let's say I decide to change the name of my texbox to something other than Name, since that's causing property collisions. I change it to
That means I have to update my code. Not paying attention, I restore the code to how it was before when my textbox was named "Text0." When I run a Debug -> Compile, VBA skips right over the Bang line and warns me about the Dot line:
I resolve that line then try compiling again:
After I fix that last line, my code compiles. But, when I click the Command2 button, the Bang line raises an error:
We want to find broken code as soon as possible. Thus, code that fails at compile time is better than similar code that does not fail until runtime.
My Preference: Dot
In the majority of my code, I prefer to use the dot operator. My main reason? I'm lazy ;-). The compile-time checking is great and all, but the real reason I use the dot operator is for all that delicious IntelliSense.
The big drawback to that approach is the potential for naming collisions. I rely on Allen Browne to help me with that, though.
UPDATE [2023-06-02]: Changed the bytecomb.com link to point to the preserved page in the Internet Archive as the site has been abandoned (h/t Chris M in the comments below).