VBA Operators

An operation is an action performed on one or more values either to modify one value or to produce a new value by combining existing values. Therefore, an operation is performed using at least one symbol and one value. The symbol used in an operation is called an operator. A variable or a value involved in an operation is called an operand.

A unary operator is an operator that performs its operation on only one operand.

An operator is referred to as binary if it operates on two operands.

When interacting with Microsoft Excel, you will be asked to provide a value. Sometimes, you will be presented with a value to view or change. Besides the values you use in a spreadsheet, in the previous lesson, we learned that we could also declare variables in code and assign values to them.

In the previous lesson, we saw that we could use the Dim operator to declare a variable. Here is an example:

Option Explicit

Sub Exercise()
    Dim Value
    
End Sub

After declaring a variable like this, we saw that we could then use it as we saw fit.

We mentioned that you could declare a variable but not specify the type of value that would be stored in the memory area reserved for it. When you have declared a variable, the computer reserves space in the memory and gives an initial value to the variable. If the variable is number based, the computer gives its memory an intial value of 0. If the variable is string based, the computer fills its memory with an empty space, also referred to as an empty string.

Initializing a variable consists of giving it a value as soon as the variable has been declared. To initialize a variable, you use the assignment operator which is "=". You type the name of the variable, followed by =, and followed by the desired value. The value depends on the type of variable. If the variable is integral based, give it an appropriate natural number. Here is an example:

Sub Exercise()
    Dim Integral As Integer
    
    Integral = 9578
End Sub

If the variable is made to hold a decimal number, initialize it with a number that can fit in its type of variable. Here is an example:

Sub Exercise()
    Dim Distance As Double
    
    Distance = 257.84
End Sub

If the variable is for a string, you can initialize it with an empty string or put the value inside of double-quotes.

If you plan to write a long piece of code, to make it easier to read, you may need to divide it in various lines. To do this, you can use the line continuation operator represented by a white space followed by an underscore.

To create a line continuation, put an empty space, then type the underscore, and continue your code in the next line. Here is an example:

Sub _
Exercise()

End Sub

Parentheses are used in various circumstances. The parentheses in an operation help to create sections in an operation. This regularly occurs when more than one operators are used in an operation. Consider the following operation:

8 + 3 * 5

The result of this operation depends on whether you want to add 8 to 3 then multiply the result by 5 or you want to multiply 3 by 5 and then add the result to 8. Parentheses allow you to specify which operation should be performed first in a multi-operator operation. In our example, if you want to add 8 to 3 first and use the result to multiply it by 5, you would write (8 + 3) * 5. This would produce 55. On the other hand, if you want to multiply 3 by 5 first then add the result to 8, you would write 8 + (3 * 5). This would produce 23.

As you can see, results are different when parentheses are used on an operation that involves various operators. This concept is based on a theory called operator precedence. This theory manages which operation would execute before which one; but parentheses allow you to completely control the sequence of these operations.

The comma is used to separate variables used in a group. For example, a comma can be used to delimit the names of variables that are declared on the same line. Here is an example:

Sub Exercise()
    Dim FirstName As String, LastName As String, FullName As String
End Sub

A double-quote is used to delimit a group of characters and symbols. To specify this delimitation, the double-quote is always used in combination with another double-quote, as in "". What ever is inside the double-quotes is the thing that need to be delimited. The value inside the double-quotes is called a string. Here is an example:

Sub Exercise()
    Dim FirstName As String, LastName As String, FullName As String
    
    FirstName = "Valère"
    ActiveCell.FormulaR1C1 = FirstName
End Sub

Most of the time, to make various statements easier to read, you write each on its own line. Here are examples:

Sub Exercise()
    Dim FirstName As String, LastName As String
    
    FirstName = "Valère"
    LastName = "Edou"
End Sub

The Visual Basic language allows you to write as many statements as necessary on the same line. When doing this, the statements must be separated by a colon. Here is an example:

Sub Exercise()
    Dim FirstName As String, LastName As String
    
    FirstName = "Valère" : LastName = "Edou"
    
    ActiveCell.FormulaR1C1 = FirstName
End Sub

The & operator is used to append two strings or expressions. This is considered as concatenating them. For example, it could allow you to concatenate a first name and a last name, producing a full name. The general syntax of the concatenation operator is:

Value1 & Value2

In the same way, you can use as many & operators as you want between any two strings or expressions. After concatenating the expressions or values, you can assign the result to another variable or expression using the assignment operator. Here are examples:

Sub Exercise()
    Dim FirstName As String, LastName As String, FullName As String
    
    FirstName = "Valère"
    LastName = "Edou"
    FullName = FirstName & " " & LastName
End Sub

If you are displaying a string but judge it too long, you can segment it in appropriate sections as you see fit. To do this, you can use vbCrLf. Here is an example:

Sub Exercise()
    Dim FirstName As String, LastName As String, FullName As String
    Dim Accouncement As String
    
    FirstName = "Valère"
    LastName = "Edou"
    FullName = FirstName & " " & LastName
    Accouncement = "Student Registration - Student Full Name: " & _
                   vbCrLf & FullName
    ActiveCell.FormulaR1C1 = Accouncement
End Sub

Algebra uses a type of ruler to classify numbers. This ruler has a middle position of zero. The numbers on the left side of the 0 are referred to as negative while the numbers on the right side of the rulers are considered positive:

A value on the right side of 0 is considered positive. To express that a number is positive, you can write a + sign on its left. Examples are +4, +228, +90335. In this case the + symbol is called a unary operator because it acts on only one operand.

The positive unary operator, when used, must be positioned on the left side of its operand, never on the right side.

As a mathematical convention, when a value is positive, you don't need to express it with the + operator. Just writing the number without any symbol signifies that the number is positive. Therefore, the numbers +4, +228, and +90335 can be, and are better, expressed as 4, 228, 90335. Because the value does not display a sign, it is referred as unsigned.

As you can see on the above ruler, in order to express any number on the left side of 0, it must be appended with a sign, namely the - symbol. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative.

The - sign must be typed on the left side of the number it is used to negate.

Remember that if a number does not have a sign, it is considered positive. Therefore, whenever a number is negative, it MUST have a - sign. In the same way, if you want to change a value from positive to negative, you can just add a - sign to its left.

The addition is performed with the + sign. It is used to add one value to another. Here is an example:

Sub Exercise()
    Dim Side#
    Dim Perimeter#
    
    Side# = 42.58
    Perimeter# = Side# + Side# + Side# + Side#
End Sub

Besides arithmetic operations, the + symbol can also be used to concatenate strings, that is, to add one string to another. This is done by appending one string at the end of another. Here is an example:

Sub Exercise()
    Dim FirstName$, LastName$, FullName$
    
    FirstName$ = "Danielle"
    LastName$ = "Kouma"
    FullName$ = FirstName$ + " " + LastName$
    
    ActiveCell.FormulaR1C1 = FullName$
End Sub

The multiplication operation allows you to add a number to itself a certain number of times set by another number. The multiplication operation is performed using the * sign. Here is an example:

Sub Exercise()
    Dim Side#
    Dim Area#
    
    Side# = 42.58
    Area# = Side# * Side#
End Sub

The subtraction operation is performed using the - sign. This operation produces the difference of two or more numbers. It could also be used to display a number as a negative value. To subtract 28 from 65, you express this with 65-28.

The subtraction can also be used to subtract the values of two values.

Dividing an item means cutting it in pieces or fractions of a set value. Therefore, the division is used to get the fraction of one number in terms of another. The Visual Basic language provides two types of operations for the division. If you want the result of the operation to be a natural number, called an integer, use the backlash operator "\" as the divisor. The formula to use is:

Value1 \ Value2

This operation can be performed on two types of valid numbers, with or without decimal parts. After the operation, the result would be a natural number.

The second type of division results in a decimal number. It is performed with the forward slash "/". Its formula is:

Value1 / Value2

After the operation is performed, the result is a decimal number.

Exponentiation is the ability to raise a number to the power of another number. This operation is performed using the ^ operator (Shift + 6). It uses the following formula:

yx

In Microsoft Visual Basic, this formula is written as:

y^x

and means the same thing. Either or both y and x can be values, variables, or expressions, but they must carry valid values that can be evaluated. When the operation is performed, the value of y is raised to the power of x.

The division operation gives a result of a number with or without decimal values, which is fine in some circumstances. Sometimes you will want to get the value remaining after a division renders a natural result.

The remainder operation is performed with keyword Mod. Its formula is:

Value1 Mod Value2

The result of the operation can be used as you see fit or you can display it in a control or be involved in another operation or expression.