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
|
|
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
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.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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.
Remember that, at any time, a box (or chunk) in memory
contains either 1 or 0:
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:
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:
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:
Therefore, 286 And 4075 produces 100001010 which is
equivalent to:
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 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:
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:
Therefore, 305 Or 2853 produces 101100110101 which is
equivalent to:
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 consists of adding two bits with the
following rules. To support bitwise exclusion, the Visual Basic language
provides an operator named Xor:
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:
Therefore, 305 Or 2853 produces 101110011110 which is
equivalent to:
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
|
No comments:
Post a Comment