Here is an example:
Sub Exercise()
ActiveCell = "AAA"
End Sub
When this code runs, the value AAA would be entered into any
Sub Exercise()
Dim FirstName As Variant
Dim
If you want, you can make the computer produce a
beeping a sound in response to something, anything. To support this, the
Visual Basic language provides a function called Beep. Its syntax
is:
Public Sub Beep()
Here is an example of calling it:
Sub Exercise()
Beep
End Sub
If this function is called when a program is running,
the computer emits a brief sound.
A string concatenation consists of
adding one string to another. to support this operation, you can use
either the + or the & operator. Here are examples:
Sub Exercise()
Dim FirstName As Variant
Dim LastName As String
Dim FullName As String
FirstName = "William"
LastName = "Sansen"
FullName = LastName + ", " & FirstName
ActiveCell = "Full Name: " & FullName
End Sub
This would produce:
The characters used in the US English and the most
common characters of Latin-based languages are created in a list or map of
character codes. Each character is represented with a small number between
0 and 255. This means that each character must fit in a byte.
To help you find the equivalent ASCII character of
such a number, the Visual Basic language provides a function named Chr.
Its syntax is:
Public Function Chr(ByVal CharCode As Integer) As String
When calling this function, pass a small number as
argument. Here is an example:
Sub Exercise()
Dim Character As String
Dim Number As Integer
Number = 114
Character = Chr(Number)
ActiveCell = "The ASCII character of " & Number & " is " & Character
End Sub
This would produce:
Besides finding the ASCII equivalent of a number, the Chr()
function can be used to apply some behavior in a program. For example, a
combination of Chr(13) and Chr(10) would break a line in an
expression, which is equivalent to the vbCrLf operator.
If you pass a number lower than 0 or higher than 255
to the Chr() function, you would receive an error.
The reason you
may pass a number higher than 255 is that you may want to get a character
beyond those of US English, such as â. To support such numbers, the
Visual Basic language provides another version of the function. Its syntax
is:
Public Function ChrW(ByVal CharCode As Integer) As String
The W here represents Wide Character. This makes it
possible to store the character in the memory equivalent to the Short
integer data type, which can hold numbers from -32768 to 32767. Normally,
you should consider that the character should fit in a Char data type,,
which should be a positive number between 0 and 65535.
Here is an example:
Sub Exercise()
Dim Character As String
Dim Number As Long
Number = 358
Character = ChrW(Number)
ActiveCell = "The ASCII character of " & Number & " is " & Character
End Sub
This would produce:
The length of a
string is the number of characters it contains. To assist you with finding the length of a
string, the Visual Basic language provides a function named Len.
Its syntax is:
Public Function Len(ByVal Expression As String) As Integer
This function expects a string as argument. If the
function succeeds in counting the number of characters, which it usually
does, it returns the an integer. Here is an example:
Sub Exercise()
Dim Item As String
Dim Length As Integer
Item = "Television"
Length = Len(Item)
ActiveCell = "The number of characters in """ & Item & """ is " & Length
End Sub
This would produce:
The Microsoft Excel library provides the LEN()
function that produces the same result.
Like a normal value, a character or a string can be
passed to a procedure. When creating the procedure, enter the argument and
its name in the parentheses of the procedure. Then, in the body of the
procedure, use the argument as you see fit. When calling the procedure, you can pass a value for
the argument in double-quotes. In the same way, you can apply any of the features we
studied for procedures, including passing as many arguments as you want or
passing a mixture of characters, strings, and other types of arguments.
You can also create a procedure that receives an optional argument.
To create a function that returns a character or a
string, create the procedure using the Function keyword and, on the right
side of the parentheses, include the String data type preceded by
the As keyword or use the $ character. Here is an example we saw in Lesson 5:
Function GetFullName$() Dim FirstName$, LastName$ FirstName = "Raymond" LastName = "Kouma" GetFullName$ = LastName & ", " & FirstName End Function
When calling the function, you can use it as a normal
function or you can retrieve the value it returns and use it as you see
fit. Here is an example:
Function GetFullName$()
Dim FirstName$, LastName$
FirstName = "Raymond"
LastName = "Kouma"
GetFullName$ = LastName & ", " & FirstName
End Function
Sub Exercise()
Range("B2") = GetFullName$
End Sub
To convert an expression to
a string, you can call the VBA's CStr() function. Its syntax is:
Public Function CStr(ByVal Expression As Variant) As String
The argument can be almost any expression that can be converted it to a string, which in most cases it
can. If it is successful, the function returns a string. Here is an
example:
Sub Exercise()
Dim DateHired As Date
DateHired = #1/4/2005#
ActiveCell = CStr(DateHired)
End Sub
The CStr() function is used to convert any type
of value to a string. If the value to be converted is a number, you can
use the Str() function. Its syntax is:
Public Function Str(ByVal Number As Variant) As String
This function expects a number as argument. Here is an
example:
Sub Exercise()
Dim Number As Double
Number = 1450.5 / 2
ActiveCell = Str(Number)
End Sub
In Lesson 3, we saw that the Visual Basic language
supports hexadecimal number and we saw how to initialize an integer
variable with a hexadecimal number. Now, on the other hand, if you have a
decimal number but need it in hexadecimal format, you can convert it. To
support this operation, you can call the Hex() function. Its syntax
is:
Function Hex(ByVal Number As { Byte | Integer | Long | Variant} ) As String
This function is used to convert either an
integer-based or a decimal number to its hexadecimal equivalent. It
returns the result as a string. Here is an example:
Sub Exercise()
Dim Number As Integer
Number = 28645
ActiveCell = Hex(Number)
End Sub
The Microsoft Excel library provides more functions to
perform this type of operation.
If you have a decimal number you want to convert to
its octal format, you can call the Oct() function. Its syntax is:
Function Oct(ByVal Number As { Byte | Integer | Long | Variant} ) As String
This function takes an integer-based or a decimal
number and converts its octal equivalent. It returns the result as a
string. Here is an example:
Sub Exercise()
Dim Number As Double
Number = 28645
ActiveCell = Oct(Number)
End Sub
The Microsoft Excel library provides more functions to
perform this type of operation.
If you are presented with a string or an expression
whose cases must be the same, you can convert all of its characters in
either uppercase or lowercase.
To convert a character, a string or an expression to
uppercase, you can call the VBA's UCase() or the Microsoft Excel's
UPPER() functions. These functions take
one argument as the string or expression to be considered. The syntaxes
are:
Function UCase(ByVal Value As String) As String Function UPPER(ByVal Value As String) As String
Each function receives a character or string as argument. If
a character is already in uppercase, it would be returned the same. If
the character is not a readable character, no conversion would happen and
the function would return it. If the character is in lowercase, it would
be converted to uppercase and the function would then return the uppercase
equivalent.
Here is an example:
Sub Exercise()
Dim ProgrammingEnvironment As String
ProgrammingEnvironment = "Visual Basic for Application for Microsoft Excel"
ActiveCell = UCase(ProgrammingEnvironment)
End Sub
To convert a character or a string to lowercase, you
can call the VBA's LCase() or the Microsoft Excel's UPPER()
functions. Their syntaxes are:
Function LCase(ByVal Value As String) As String Function LOWER(ByVal Value As String) As String
The function takes a character or a string as
argument. If a character is not a readable symbol, it would be kept
"as is". If the character is in lowercase, it would not be
converted. If the character is in uppercase, it would be converted to
lowercase.
A sub-string is a character or a group of characters
or symbols that are part of an existing string. The Visual Basic language
provides functions to create, manipulate, or manage sub-strings. The
primary rule to keep in mind is that a sub-string is part of, and depends
on, a string. In other words, you cannot have a sub-string if you do
not have a string in the first place.
If you have an existing string but want to create a
new string using a number of characters from the left side characters of
the string, you can use the Microsoft Excel's LEFT() or the VBA's Left()
functions.
Their syntaxes are:
Function LEFT(ByVal str As String, ByVal Length As Integer) As String Function Left(ByVal str As String, ByVal Length As Integer) As String
Each function takes two arguments and both are
required. The first argument is the existing string. The second argument
is the number of characters counted from the left side of the string. Here
is an example:
Sub Exercise()
Dim Process As String
Process = "learning"
ActiveCell = "To " & Left(Process, 5) & " is to gain understanding"
End Sub
This would produce:
To create a new string using one or more characters
from the right side of an existing string, call the Microsoft Excel RIGHT()
or the VBA's Right() functions.
Its syntax is:
Function RIGHT(ByVal str As String, ByVal Length As Integer) As String Function Right(ByVal str As String, ByVal Length As Integer) As String
Both arguments are required. The first argument is the
original string. The second argument is the number of characters counted
from the right side of the string.
You may want to create a string using some characters either
from the left, from the right, or from somewhere inside an existing
string. To assist you with this, the Visual Basic language provides a
function named Mid and the Microsoft Excel library is equipped with
a function named MID. Here is an example of calling the Mid()
function:
Sub Exercise()
Dim ProgrammingEnvironment As String
ProgrammingEnvironment = "VBA for Microsoft Excel"
ActiveCell = "The " & Mid(ProgrammingEnvironment, 10, 13) & " language"
End Sub
One of the most regular operations you will perform on
a string consists of finding out whether it contains a certain character
or a certain contiguous group of characters. To help you with this
operation, the Visual Basic language provides the InStr() function
and the Microsoft Excel library equipped with the FIND() function. Their syntaxes are:
InStr([start, ]string1, string2[, compare]) FIND([Find_Text, Within_Text, Start_Num)
In the first version of the function, the String1
argument is the string on which the operation will be performed. The String2
argument is the character or the sub-string to look for. If String2
is found in String1 (as part of String1), the function
return the position of the first character. Here is an example:
The first version of the function asks the interpreter
to check String1 from the left looking for String2. If String1
contains more than one instance of String2, the function returns
(only) the position of the first instance. Any other subsequent instance
would be ignored. If you want to skip the first instance or want the
interpreter to start checking from a position other than the left
character, use the second version. In this case, the Start argument allows
you to specify the starting position from where to start looking for String2
in String1.
The InStr() function is used to start checking
a string from the left side. If you want to start checking from the right
side, call the InStrRev() function. Its syntax is:
InstrRev(stringcheck, stringmatch[, start[, compare]])
After finding a character or a sub-string inside of a
string, you can take action on it. One of the operations you can perform
consists of replacing that character or that sub-string with another
character or a sub-string. To do this, the Visual Basic language provides
the Replace() function and Microsoft Excel provides the REPLACE()
function. Its syntax is:
Replace(expression, find, replace[, start[, count[, compare]]]) REPLACE(Old_Text, Find_Text, Start_Num, Num_Characters, New_Text)
The first argument is the string on which the
operation will be performed. The second argument is the character or
string to look for in the Expression. If that character or string
is found, the third argument is the character or string to replace it
with.
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Strings
Subscribe to:
Post Comments (Atom)
Related Scripting
No comments:
Post a Comment