In the world of
When writing your code, you can use any
variable just by
specifying its name. When you provide this name, the
computer
directly reserves an area in memory for it. Microsoft Visual
Basic allows you to directly use any name for a variable as
you see fit. Fortunately, to eliminate the possibility of
confusion, you can first let Visual Basic know that you will
be
using a variable.
In order to reserve that storage area, you have
to let the computer know. Letting the computer know is referred to
as declaring the variable. To declare a variable, you start
with the Dim
word, like this:
Dim
A variable must have a name. The name is written
on the right side of the Dim word. There are rules you should
follow when naming your variables:
There are some words you should (must) not use
to name your variables. Those words are reserved for the VBA
internal use. Therefore, those words are called keywords. Some of
them are:
As mentioned already, to declare a variable,
type Dim followed by a name. Here is an example:
Sub Exercise()
Dim Something
End Sub
Declaring a variable simply communicates to
Visual Basic the name of that variable. You can still use a mix of
declared and not-declared variable. If you declare one variable and
then start using another variable with a similar but somewhat
different name, Visual Basic would still consider that you
are using two variables. This can create a great deal of
confusion because you may be trying to use the same variable
referred to twice. The solution to this possible confusion is to
tell Visual Basic that a variable cannot be used if it has not been
primarily declared. To communicate this, on top of each file you use
in the Code Editor, type:
Option Explicit
This can also be done automatically for each
file by checking the Require Variable Declaration in the
Options dialog box.
In a regular application, it is not unusual to want to use
many variables. Once again, you should make it a habit to always declare a
variable before using it. To declare a new variable after declaring a first
one, you can simply go to the next line and use the Dim keyword to declare the
new variable. Here is an example:
Sub Exercise()
Dim Something
Dim Whatever
End Sub
In the same way, you can declare as many variables as you
want. Instead of declaring each variable on its own line, you can declare more
than one variable on the same line. To do this, use one Dim keyword and
separate the names of variables with commas. Here are examples:
Sub Exercise()
Dim Father, Mother
Dim Son, Daughter, Nephew, Niece
Dim GrandMa
End Sub
Notice that each line uses its own Dim keyword and
every new line of declaration(s) must have a Dim keyword.
We saw that when you declare a variable, the computer
reserves a memory space for it but the space is kept empty. After declaring
the value, you can store a value you want in the memory that was reserved for
it.
To store a value in the memory reserved for a variable,
you can assign a value to the variable. To do this, type the name of the
variable, followed by the assignment operator which is =, followed by the
value you want to store. Here is an example:
Sub Exercise()
Dim Value
Value = 9374
End Sub
As we will learn in the next few lessons, there are
different types of values you will use in your document. Also as we will see,
the value you (decide to) store must be in accordance with the type of memory
that the computer had reserved for the variable.
After assigning a value to a variable, you can use that
variable knowing the value it is currently holding. At any time and when
necessary, you can change the value held by a variable. That's why it is
called a variable (because its value can vary or change). To change the value
held by a variable, access the variable again and assign it the new desired
value.
A
data type tells the computer what kind of variable you are going to
use. Before using a variable, you
should know how much space it will occupy in memory.
Different variables use different amount of
space in memory. The information that specifies how much space a
variable needs is called a data type. A data type is measured
in bytes.
To specify the data type that will be used for a
variable, after typing Dim followed by the name of the variable,
type the As keyword, followed by one of the data types we will
review next. The formula used is:
Dim VariableName As DataType
We mentioned earlier that you could use various variables
if you judge them necessary. When declaring such variables, we saw that you
could declare each on its own line. To specify the data type of a variable,
use the same formula as above. Here is an example:
Sub Exercise()
Dim FirstName As DataType
Dim LastName As DataType
End Sub
We also saw that you could declare many variables on the
same line as long as you separate the names of the variables with commas. If
you are specifying the data type of each, type the comma after each variable.
Here are examples:
Sub Exercise()
Dim FirstName As DataType, LastName As DataType
Dim Address As DataType, City As DataType, State As DataType
Dim Gender As DataType
End Sub
This code appears as if there is only one type of data. In
the next few sections, we will review various types of values that are
available. To declare variables of different data types, you declare each on
its own line as we saw earlier:
Sub Exercise()
Dim FullName As DataType1
Dim DateHired As DataType2
Dim EmploymentStatus As DataType3
End Sub
You can also declare variables of different data types on
the same line. To do this, use one Dim keyword and separate the declarations
with commas. Here are.examples:
Sub Exercise()
Dim FullName As DataType1, DateHired As DataType2
Dim EmploymentStatus As DataType3
End Sub
To make variable declaration a little faster and
even convenient, you can replace
the As DataType expression with a special character
that represents the intended data type. Such a character is called a
type character and it depends on the data type you intend to apply
to a variable. When used, the type character must be the last
character of the name of the variable. We will see what characters
are available and when it can be applied.
Every time the user enters a value in an application.
That value is primarily considered as text. This means that, if you want
to use such a value in an expression or a calculation that expects a
specific value other than text, you must convert it from that text.
Fortunately, Microsoft Visual Basic provides an effective mechanism to
convert a text value to one of the other values we will see next.
To convert text to another value, there is a keyword adapted for the purpose and that depends on the type of value you want to
convert it to. We will mention each when necessary.
If you are planning to use a number in your
program, you have a choice from different kinds of numbers that the Visual Basic
language can recognize. The Visual Basic language recognizes as
a natural number any number that doesn't include a fractional part. In the
Visual Basic language, the number is made of digits only as a combination of
0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. No other character is allowed. In future
lessons, we will learn that in Microsoft Excel, you can use a comma to
separate the thousands, which would make the number easy to read. Microsoft
Excel recognizes the comma separator, the Visual Basic language doesn't.
By default, when we refer to a natural number, we expect it
in decimal format as a combination of digits. The Visual Basic language also
supports the hexadecimal format. A hexadecimal number starts with &H
followed by a combination of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, a, b, c, d, e,
f, A, B, C, D, E, and F. An example would be &H28E4AABF.
To declare a variable that would hold natural numbers that
range from 0 to 255, use the Byte
data type. Here is an example:
Sub Exercise()
Dim StudentAge As Byte
End Sub
There is no type character for the Byte data type.
After declaring the variable, you can assign it a
small positive number. Here is an example:
Sub Exercise()
Dim Value As Byte
Value = 246
End Sub
You can also use the number in hexadecimal format as long as
the number is less than 255.
If you give either a negative value or a value higher
to 255, when you attempt to access it, you would receive an error:
To convert a value to a small number, you can use CByte(). The formula
to use would be:
Number = CByte(Value to Convert to Byte)
When using CByte(), enter the value to convert
in
the parentheses.
To declare a variable that would hold a number that ranges from
-32768 to 32767, use the
Integer data type.
Here is an example of declaring an integer variable:
Sub Exercise()
Dim Tracks As Integer
End Sub
Instead of using As Integer, you can use the % type
character. Therefore, the above declaration could be done as follows:
Sub Exercise()
Dim Tracks%
End Sub
After declaring the variable, you can assign the desired
value to it. If you assign a value lower than -32768 or higher than 32767,
when you decide to use it, you would receive an error.
If you have a value that needs to be converted into a
natural number, you can use CInt() using the following formula:
Number = CInt(Value to Convert)
Between the parentheses of CInt(), enter the
value, text, or expression that needs to be converted.
A long
integer is a number that can be used for a variable
involving greater numbers than integers. To declare a variable that
would hold such a large number, use the Long data type. Here
is an example:
Sub Exercise()
Dim Population As Long
End Sub
The type character for the Long data type is @. The
above variable could be declared as:
Sub Exercise()
Dim Population@
End Sub
A Long variable can store a value between – 2,147,483,648
and 2,147,483,647 (remember that the commas are used to make the numbers
easy to read; do not be used them in your code). Therefore, after
declaring a Long variable, you can assign it a number in that
range.
To convert a value to a long integer, call CLng()
using the following formula:
Number = CLng(Value to Convert)
To convert a value to long, enter it in the
parentheses of CLng().
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Practical
Learning: Creating a Macro
Practical Learning: Using a Variable
In computer programming, a decimal
number is one
that represents a fraction. Examples are 1.85 or 426.88. If
you plan to use a variable that would that type of number but precision
is not your main concern, declare it using the Single data
type. Here is an example:
Sub Exercise()
Dim Distance As Single
End Sub
The type character for the Single data type is !.
Based on this, the above declaration could be done as:
Sub Exercise()
Dim Distance!
End Sub
A Single variable can hold a number between 1.401298e–45
and 3.402823e38. for negative values or between 1.401298e–45
and 3.402823e38 for positive values.
If you have a value that needs to be converted, use CSng()
with the following formula:
Number = CSng(Value to Convert)
In the parentheses of CSng(), enter the value to be
converted.
If you want to use a decimal number that
requires a good deal of precision, declare a variable using the Double data type.
Here is an example of declaring a Double variable:
Sub Exercise()
Dim Distance As Double
End Sub
Instead of As Double, the type character you
can use is #:
Sub Exercise()
Dim Distance#
End Sub
A Double variable can hold a number between –1.79769313486231e308
and
–4.94065645841247e–324 for negative values or between 4.94065645841247e–324 and 1.79769313486231e308 for positive values.
To convert a value to double-precision, use CDbl()
with the following formula:
Number = CDbl(Value to Convert)
In the parentheses of CDbl(), enter the value
that needs to be converted.
A string is a character or a combination of characters
that constitute text of any
kind and almost any length. To declare a string variable, use the
String data type. Here is an example:
Sub Exercise()
Dim CountryName As String
End Sub
The type character for the String data type is $.
Therefore, the above declaration could be written as:
Sub Exercise()
Dim CountryName$
End Sub
As mentioned already, after declaring a variable, you can
assign a value to it. The value of a string variable must be included inside
of double-quotes. Here is an example:
Sub Exercise()
Dim CountryName As String
CountryName = "Brésil"
End Sub
If you have a value that is not primarily text and you
want to convert it to a string, use CStr() with the following
formula:
CStr(Value To Convert to String)
In the parentheses of the CStr(), enter the
value that you want to convert to string.
The Currency data type is used to deal
with monetary values.
Here is an example of declaring it:
Sub Exercise()
Dim StartingSalary As Currency
End Sub
Instead of using the As Currency expression, you can use @
as the type character to declare a currency variable. Here is an example of declaring it:
Sub Exercise()
Dim StartingSalary@
End Sub
A variable declared with the Currency keyword can store a
value between – 922,337,203,685,477.5808 and 922,337,203,685,477.5807. Once
again, keep in mind that the commas here are used only to make the number easy
to read. Don't use the commas in a number in your code. Also, when assigning a
value to a currency-based variable, do not use the currency symbol.
Here is an example of assigning a currency number to a
variable:
Sub Exercise()
Dim StartingSalary As Currency
StartingSalary = 66500
End Sub
If you want to convert a value to currency,
use CCur() with the following formula:
Number = CCur(Value to Convert)
To perform this conversion, enter the value in the
parentheses of CCur().
In Visual
Basic, a Date data type can be used to store a date
value. Therefore, to declare either a
date or a time variables, use the Date data type. Here is an
example:
Sub Exercise()
Dim DateOfBirth As Date
End Sub
After declaring the variable, you can assign it
a value. A date value must be included between two # signs. Here is
an example:
Sub Exercise()
Dim DateOfBirth As Date
DateOfBirth = #10/8/1988#
End Sub
There are various formats you can use for a
date. We will deal with them in another lesson.
If you have a string or an expression that you
want to convert to a date value, use CDate()
based on the following formula:
Result = CDate(Value to Convert)
In the parentheses of CDate(), enter the
value that needs to be converted.
In Visual
Basic, the Date data type can also be used to store a time
value. Here is an example of declaring a variable that can hold a
time value:
Sub Exercise()
Dim ShiftTimeIn As Date
End Sub
After declaring the variable, to assign a value
to it, include the value between two # signs. The value follows
different rules from a date.
To convert a value or an expression to time, use
CDate().
So far, we declared variables knowing the types
of values we wanted them to hold. VBA provides a universal (or
vague) data type you can use for any type of value. The Variant data
type is used to declare a variable whose type is not explicitly
specified. This means that a Variant data type can hold any type of
value you want.
Here are examples of Variant-declared variables
that hold different types of values:
Sub Exercise()
Dim FullName As Variant
Dim EmploymentStatus As Variant
Dim HourlySalary As Variant
Dim DateHired As Variant
FullName = "Patricia Katts"
EmploymentStatus = 2
HourlySalary = 35.65
DateHired = #6/22/2004#
End Sub
In the variables we declared in the last few sections, we
specified a data type for each. You can declare a variable without giving its
data type. Here are examples:
Sub Exercise()
Dim FullName
Dim EmploymentStatus
Dim HourlySalary
Dim DateHired
End Sub
Of course, you can declare more than one variable on the
same line.
To indicate how much space is needed for the variable, you
must assign it a value. Here are examples:
Sub Exercise()
Dim FullName
Dim EmploymentStatus
Dim HourlySalary
Dim DateHired
FullName = "Patricia Katts"
EmploymentStatus = 2
HourlySalary = 35.65
DateHired = #6/22/2004#
End Sub
Once the variable holds a value, you can use it as you see
fit.
So far, we were declaring our variables between the Sub
Name and the End Sub lines. Such a variable is referred to as a
local variable. A local variable is confined to the area where it is declared.
Here is an example:
Option Explicit
Sub Exercise()
Dim FirstName As String
FirstName = "Patricia"
End Sub
You cannot use such a variable outside of its Sub Name
and the End Sub lines.
A global variable is a variable declared outside of the Sub
Name and the End Sub lines. Such a variable is usually declared in
the top section of the file. Here is an example:
Option Explicit
Dim LastName As String
Sub Exercise()
End Sub
After declaring a global variable, you can access it in the
other areas of the file. Here is an example:
Option Explicit
Dim LastName As String
Sub Exercise()
Dim FirstName As String
FirstName = "Patricia"
LastName = "Katts"
End Sub
Although we declared our global variable inside of the file
where it was used, you can also declare a global variable in a separate module
to be able to use it in another module.
When using a global variable, the Visual Basic language
allows you to control its access level. The access level of a variable is a
process of controlling how much access a section of code has on the variable.
A variable is referred to as private if it can be accessed
only by code from within the same file (the same module) where it is used. To
declare such a variable, instead of Dim, you use the Private
keyword. Here is an example:
Option Explicit
Private LastName As String
Sub Exercise()
Dim FirstName As String
FirstName = "Patricia"
LastName = "Katts"
End Sub
Remember that a private variable can be accessed by any code
in the same module. In the next lesson, we will learn how to create other
sections of code.
A variable is referred to as public if it can be accessed by
code either from within the same file (the same module) where it is declared or
from code outside its module. To declare a public variable, instead of Dim,
you use the Public keyword. Here is an example:
Option Explicit
Private LastName As String
Public FullName As String
Sub Exercise()
Dim FirstName As String
FirstName = "Patricia"
LastName = "Katts"
FullName = FirstName & " " & LastName
End Sub
As a reminder, a public variable is available to code inside
and outside of its module. This means that you can create a module, declare a
public variable in it, and access that variable in another file (module) where
needed.
A private variable is available inside its module but not
outside its module. If you declare a private variable in a module and try
accessing it in another module, you would receive an error:
Module 1:
Option Explicit
Private FullName As String
Module 2:
Option Explicit
Private LastName As String
Private FirstName As String
Sub Exercise()
FirstName = "Patricia"
LastName = "Katts"
FullName = FirstName & " " & LastName
ActiveCell.FormulaR1C1 = FullName
End Sub
This would produce:
|
No comments:
Post a Comment