 VBA Operators and Operands

 VBA Operators

 Introduction

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.

 Dimensioning a Variable

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.

 The Assignment Operator

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.

 The Line Continuation Operator: _

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
 The Parentheses: ()

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 ,

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
 The Double Quotes: ""

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
 The Colon Operator :

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
 String Concatenation: &

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
 Carriage Return-Line Feed

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
 Arithmetic Operators

 Positive Unary Operator: +

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:

 -∞ -6 -5 -4 -3 -2 -1 1 2 3 4 5 6 +∞ 0 -∞ -6 -5 -4 -3 -2 -1 1 2 3 4 5 6 +∞

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.

 The Negative Operator -

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
 Multiplication *

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

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.

 Integer Division \

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.

 Decimal Division /

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 ^

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.

 Remainder: Mod

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.

 Bit Manipulations

 Introduction

From our introduction to variables, you may remember that the computer stores its data in memory using small locations that look like boxes and each box contains a bit of information. Because a bit can be represented only either as 1 or 0, we can say that each box contains 1 or 0. Bit manipulation consists of changing the value (1 or 0, or 0 or 1) in a box. As we will see in the next few operations, it is not just about changing a value. It can involve reversing a value or kind of "moving" a box from its current position to the next position.

The operations on bits are performed on 1s and 0s only. This means that any number in decimal or hexadecimal format involved in a bit operation must be converted to binary first.

You will almost never perform some of the operations we are going to review. You will hardly perform some other operations. There is only one operation you will perform sometimes: the OR operation.

 "Reversing" a Bit

Remember that, at any time, a box (or chunk) in memory contains either 1 or 0:  0 1

Bit reversal consists of reversing the value of a bit. If the box contains 1, you can reverse it to 0. If it contains 0, you can reverse it to 1. To support this operation, the Visual Basic language provides the Not Operator.

As an example, consider the number 286. The decimal number 286 converted to binary is 100011110. You can reverse each bit as follows:

 286 1 0 0 0 1 1 1 1 0 Not 286 0 1 1 1 0 0 0 0 1

 Bitwise Conjunction

Bitwise conjunction consists of adding the content of one box (a bit) to the content of another box (a bit). To support the bitwise conjunction operation, the Visual Basic language provides the And operator.

To perform the bit addition on two numbers, remember that they must be converted to binary first. Then:

• If a bit with value 0 is added to a bit with value 0, the result is 0
 Bit0 0 Bit1 0 Bit0 And Bit1 0
• If a bit with value 1 is added to a bit with value 0, the result is 0
 Bit0 1 Bit1 0 Bit0 And Bit1 0
• If a bit with value 0 is added to a bit with value 1, the result is 0
 Bit0 0 Bit1 1 Bit0 And Bit1 0
• If a bit with value 1 is added to a bit with value 1, the result is 1
 Bit0 1 Bit1 1 Bit0 And Bit1 1

As an example, consider the number 286 bit-added to 475. The decimal number 286 converted to binary is 100011110. The decimal number 4075 converted to binary is 111111101011. Based on the above 4 points, we can add these two numbers as follows:

 286 0 0 0 1 0 0 0 1 1 1 1 0 4075 1 1 1 1 1 1 1 0 1 0 1 1 286 And 4075 0 0 0 1 0 0 0 0 1 0 1 0

Therefore, 286 And 4075 produces 100001010 which is equivalent to:

 Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0 256 128 64 32 16 8 4 2 1 286 And 4075 1 0 0 0 0 1 0 1 0 256 0 0 0 0 8 0 2 0

This means that 286 And 4075 = 256 + 16 + 2 = 266

This can also be programmatically calculated as follows:

Sub Exercise()
Dim Number1 As Integer
Dim Number2 As Integer
Dim Result As Integer

Number1 = 286
Number2 = 4075
Result = Number1 And Number2

ActiveCell.FormulaR1C1 = Result
End Sub
 Bitwise Disjunction

Bitwise disjunction consists of disjoining one a bit from another bit. To support this operation, the Visual Basic language provides the Or operator.

To perform a bitwise conjunction on two numbers, remember that they must be converted to binary first. Then:

• If a bit with value 0 is added to a bit with value 0, the result is 0
 Bit0 0 Bit1 0 Bit0 Or Bit1 0
• If a bit with value 1 is added to a bit with value 0, the result is 1
 Bit0 1 Bit1 0 Bit0 Or Bit1 1
• If a bit with value 0 is added to a bit with value 1, the result is 1
 Bit0 0 Bit1 1 Bit0 Or Bit1 1
• If a bit with value 1 is added to a bit with value 1, the result is 1
 Bit0 1 Bit1 1 Bit0 Or Bit1 1

As an example, consider the number 305 bit-disjoined to 2853. The decimal number 305 converted to binary is 100110001. The decimal number 2853 converted to binary is 101100100101. Based on the above 4 points, we can disjoin these two numbers as follows:

 305 0 0 0 1 0 0 1 1 0 0 0 1 2853 1 0 1 1 0 0 1 0 0 1 0 1 305 Or 2853 1 0 1 1 0 0 1 1 0 1 0 1

Therefore, 305 Or 2853 produces 101100110101 which is equivalent to:

 Bit11 Bit10 Bit9 Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0 2048 1024 512 256 128 64 32 16 8 4 2 1 305 Or 2853 1 0 1 1 0 0 1 1 0 1 0 1 2048 0 512 256 0 0 32 16 0 4 0 1

This means that 286 And 4075 = 2048 + 512 + 256 + 32 + 16 + 4 + 1 = 2869

This can also be programmatically calculated as follows:

Sub Exercise()
Dim Number1 As Integer
Dim Number2 As Integer
Dim Result As Integer

Number1 = 286
Number2 = 4075
Result = Number1 Or Number2

ActiveCell.FormulaR1C1 = Result
End Sub
 Bitwise Exclusion

Bitwise exclusion consists of adding two bits with the following rules. To support bitwise exclusion, the Visual Basic language provides an operator named Xor:

• If both bits have the same value, the result is 0
 Bit0 0 1 Bit1 0 1 Bit0 Xor Bit1 0 0
• If both bits are different, the result is 1
 Bit0 0 1 Bit1 1 0 Bit0 Xor Bit1 1 1

As an example, consider the number 618 bit-excluded from 2548. The decimal number 618 converted to binary is 1001101010. The decimal number 2548 converted to binary is 100111110100. Based on the above 2 points, we can bit-exclude these two numbers as follows:

 618 0 0 1 0 0 1 1 0 1 0 1 0 2548 1 0 0 1 1 1 1 1 0 1 0 0 618 Xor 2548 1 0 1 1 1 0 0 1 1 1 1 0

Therefore, 305 Or 2853 produces 101110011110 which is equivalent to:

 Bit11 Bit10 Bit9 Bit8 Bit7 Bit6 Bit5 Bit4 Bit3 Bit2 Bit1 Bit0 2048 1024 512 256 128 64 32 16 8 4 2 1 618 Xor 2548 1 0 1 1 1 0 0 1 1 1 1 0 2048 0 512 256 128 0 0 16 8 4 2 0

This means that 286 And 4075 = 2048 + 512 + 256 + 128 + 16 + 8 + 4 + 2 = 2974

This can also be programmatically calculated as follows:

Sub Exercise()
Dim Number1 As Integer
Dim Number2 As Integer
Dim Result As Integer

Number1 = 286
Number2 = 4075
Result = Number1 Xor Number2

ActiveCell.FormulaR1C1 = Result
End Sub