Member Access Operators in VBA

by Sola Bode
in VBA Coding Constructs
February 25, 2021

VBA member access operators - symbols that ease the referencing of class or object members (i.e., properties, methods, or events) - are explored herein.

4 minutes read

VBA Operators

VBA operators are special tokens that instruct the compiler to “operate” on values or program identifiers. They are also referred to as program symbols or code elements. Also, the program identifiers they operate on (e.g., addition) must be capable of holding values.

You may have heard of the term operand. These are the values (i.e., literals) or identifiers (e.g., variables and constants) that operators act on. Based on their number of operands, operators are either unary or binary.

Unary VBA operators are operators that only act on a single operand at a time. Binary VBA operators are those that only act on two operands at a time.

VBA supports the following categories of operators:

This article takes an in-depth look at the VBA member access operators.

VBA Operator Precedence

Often, an operation involves more than one operator, sometimes of different categories. So, it is worthwhile paying special attention to the order of precedence of the VBA operators. Otherwise, VBA’s default operator precedence applies.

The default precedence is as follows: arithmetic first, then concatenation, followed by comparison, then logical, and finally assignment operators.

Moreover, there is a pre-set precedence for operators within each category. How do you override this default order and remove any ambiguity? Easy, set the preferred precedence with open and close parentheses, ( ).

Member Access Operators

Member access operators ease the referencing (i.e., accessing) of class or object’s members (i.e., properties, methods, or events). There are two such VBA operators, the dot (.) and bang or exclamation point (!) operators, as described in the table below.

Operator	Description	Outcome	Precedence
.	Dot (binary) operator grants access (*read/write) to a class, object, or enumeration’s properties, methods, and events. It also allows access to a class or object’s child classes or objects, serving as the only way to traverse the VBA object hierarchy*.	Gets or sets a *Property-value; Calls or invokes a Method; Triggers an Event*.	1
!	Bang (binary) operator grants *write-only* access to a class or object’s *default* property – whose value must be a *String*.	Sets the *default Property-value* to a *String*	1

As shown in the table, both member access operators have the same precedence. So, when they both appear together in an expression, evaluation proceeds in the order of appearance from left to right.

Besides, enclosing a part of an expression in parentheses prioritizes its evaluation ahead of other parts.

For both the dot and bang operators the class, object, or enumeration (1^st operand) must be separated from the member being accessed (2^nd operand) by the operator, with no space in-between.

Member Access Operators: Dot (.)

The sample code below shows the dot (.) operator’s usage in accessing class or object members.

Option Explicit

Sub MemberAccessOperators_Dot_Example01()
    
    'access active worksheet's Name property
    Debug.Print Application.ThisWorkbook.ActiveSheet.Name   'getting
    Application.ThisWorkbook.ActiveSheet.Name = "New Name"  'setting
    Debug.Print Application.ThisWorkbook.ActiveSheet.Name
    '   ActiveSheet is a child of ThisWorkbook,
    '   ThisWorkbook is a child of Application
    '   the dot operator drives you through till the Name property
    
    'invoke the Range object's Clear method
    Application.ThisWorkbook.ActiveSheet.Range("A1").Clear
    '   this clears both content and formatting for cell A1.
    
    'trigger event
    Application.ThisWorkbook.Worksheets(1).Activate
    '   Activate event of the worksheet with index no. '1' is run
    
End Sub

Option Explicit

Sub MemberAccessOperators_Dot_Example01()

'access active worksheet's Name property

Debug.Print Application.ThisWorkbook.ActiveSheet.Name 'getting

Application.ThisWorkbook.ActiveSheet.Name = "New Name" 'setting

Debug.Print Application.ThisWorkbook.ActiveSheet.Name

' ActiveSheet is a child of ThisWorkbook,

' ThisWorkbook is a child of Application

' the dot operator drives you through till the Name property

'invoke the Range object's Clear method

Application.ThisWorkbook.ActiveSheet.Range("A1").Clear

' this clears both content and formatting for cell A1.

'trigger event

Application.ThisWorkbook.Worksheets(1).Activate

' Activate event of the worksheet with index no. '1' is run

End Sub

Sample code illustrating the Member Access Dot (.) Operator’s usage. — Sample code illustrating the Member Access ***Dot (.)*** operator’s usage.

Member Access Operators: Bang (!)

While the dot operator allows retrieving and setting any property’s value, the bang operator only allows setting a single String as the value of only the default property. The sample code below demonstrates the bang operator’s usage.

Option Explicit

Sub MemberAccessOperators_Bang_Example01()
    
    'bang operator passes a textbox's name (i.e., Txt_NameFirst) to the
    '   default member of the userform object (i.e., ufm_BangOperatorDemo),
    '   which is the Controls collection (i.e., a grouping of all controls
    '   on the userform).
    Debug.Print ufm_BangOperatorDemo!Txt_NameFirst.Text
    ufm_BangOperatorDemo!Txt_NameFirst.Text = "James"
    Debug.Print ufm_BangOperatorDemo!Txt_NameFirst.Text
    
    'dot operator invokes userform's Show method
    ufm_BangOperatorDemo.Show
    
End Sub

Option Explicit

Sub MemberAccessOperators_Bang_Example01()

'bang operator passes a textbox's name (i.e., Txt_NameFirst) to the

' default member of the userform object (i.e., ufm_BangOperatorDemo),

' which is the Controls collection (i.e., a grouping of all controls

' on the userform).

Debug.Print ufm_BangOperatorDemo!Txt_NameFirst.Text

ufm_BangOperatorDemo!Txt_NameFirst.Text = "James"

Debug.Print ufm_BangOperatorDemo!Txt_NameFirst.Text

'dot operator invokes userform's Show method

ufm_BangOperatorDemo.Show

End Sub

Sample code illustrating the Member Access Bang (!) Operator’s usage – setting a UserForm Textbox’s Text property via the UserForms default property, i.e. the Controls collection. — Sample code illustrating the Member Access ***Bang (!)*** operator’s usage – setting a UserForm Textbox’s *Text property* via the UserForms default property, i.e. the *Controls collection*.

UserForm used in the sample code, at both design and runtime. — UserForm used in the ***Bang (!)*** operator sample code, at both design and runtime.

Lastly, for the bang operator, the 2^nd operand cannot be a String literal (i.e., a series of characters enclosed in double-quotes) or String variable. Only valid Visual Basic identifiers are allowed, e.g. object name as in the sample code above.

5 1 vote

Article Rating