docs:programming:office_vba:methods_for_referring_to_objects [hartungdesign.net wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Methods for Referring to Objects ======

===== Bang (!) verses Dot (.) verses Quotes ("") =====
In general, you follow the bang with the name of something you created: a form, report, or control.  The bang also indicates that the item to follow is an element of a collection.  You'll usually follow the dot with a property, collection, or method name.  Actually, under the covers, the bang separator really says, "retrieve the following object from the default collection of the parent object."
  * One place where the bang operator is necessary is in query parameters that refer to form fields.  That is, you cannot avaoid the Forms!FormName!ControlName syntax in this case.
  * Quotes allow you to use a string variable in place of the object name
  * It is generally considered to be bad practice to use the bang in VBA code.
  * Examples:<code>
Set ctl = Forms("frmTest").Controls("txtName")

' since Controls is the default collection of a form, you can abbreviate the above line as either of the following:
Set ctl = Forms("frmTest")("txtName")
Set ctl = Forms("frmTest")!txtName

' bang vs quotes - the following statements are the same
cat.Tables!tblCustomers.Columns!Address.Type
cat.Tables("tblCustomers").Columns("Address").Type
</code>

^Syntax  ^Details  ^Example  ^
|collection("name")  |n/a  |cat.Tables("tblCustomers").Fields("Street Number")|
|collection(var)  |Where var is a string or variant variable  |<html>
strTable = "tblCustomers"
<br />strField = "Street Number"
<br />cat.Tables(strTable).Fields(strField)
</html>|
|collection(ordinal position)  |Where ordinal position is the object's position within its collection  |cat.Tables(0).Fields(0)  |
|<html>
collection!name
<br />collection![name]
</html>|Brackets are necessary if name contains a nonstandard character, such as a space  |cat.Tables("tblCustomers").Fields![Street Number]  |