docs:programming:office_vba:methods_for_referring_to_objects

Methods for Referring to Objects

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:
    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
Syntax Details Example
collection(“name”) n/a cat.Tables(“tblCustomers”).Fields(“Street Number”)
collection(var) Where var is a string or variant variable

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)

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

  • docs/programming/office_vba/methods_for_referring_to_objects.txt
  • Last modified: 2008/08/03 00:25
  • by 127.0.0.1