VBA Operations with Excel VBA

In VBA programs, a standard set of operations on data can be used. There are three types of operations:

• mathematical — performed on numbers, and their result is numerical;
• relational — applied not only to numbers, and their result is logical values, for example, x > y;
• logical — applied to logical expressions, and their result is logical values, for example, Not x And y.

Mathematical Operations

VBA supports a standard set of mathematical operations from addition to exponentiation.

Table. Mathematical operations

Relational Operations

Table presents the relational operations used in VBA.

Table. Relational operations

Logical Operations

Table presents the main logical operations used in VBA.

Table. Main logical operations

The Option Compare Directive

The behavior of the Like comparison operation depends on the Option Compare directive, which is placed in the module declaration section. By default, each module is considered to have the Option Compare Binary instruction, in which uppercase and lowercase letters are distinguished. That is, the following operator will return False:

Debug.Print « AA » Like « aa »

If, however, the Option Compare Text instruction is specified in the module declaration section, then uppercase and lowercase letters are not distinguished, and the same operator will now return True.

In the following example The Option Compare Directive , in the input box of a dialog window the user must enter their name in Latin letters. When pressing the OK button, the program analyzes the input and displays a message. Specifically:

• if the user forgot to enter a name, they are informed about it;
• if the entered name contains characters other than Latin letters, they are informed about it;
• if the name consists only of Latin letters, the program greets them.

Listing. The Option Compare Directive

Option Compare Text
Sub DemoCompare()
    Dim name As String, lng As Integer, i As Integer
    name = InputBox("Введите имя")
    lng = Len(Trim(name))
    If lng = 0 Then
        MsgBox "You forgot to enter a name"
        Exit Sub
    Else
        For i = 1 To lng
            If Not Mid(name, i, 1) Like "[A-Z]" Then
                MsgBox "The name must consist only" & vbCr _
                       & "of Latin alphabet letters"
                MsgBox "Hello, " & name
                Exit Sub
            End If
        Next i
    End If
End Sub

Operation Priorities

VBA performs operations according to their priorities, which ensures unambiguous interpretation of expression values. Table lists the execution priorities of operations.

Table . Operation priorities