| |
|
Valid
Name |
Invalid Name |
|
My_Car |
My.Car |
|
ThisYear |
1NewBoy |
|
Long_Name_Can_beUSE |
He&HisFather
*& is not acceptable |
|
Group88 |
Student ID
* Spacing not allowed |
Table
2.1 : Example of valid and invalid variable names
b)
Declaring Variables
In VBA, one needs to declare the variables before using
them by assigning names and data types. There are many VBA data types, which can
be grossly divided into two types, namely the numeric data types and non-numeric
data types
Numeric data types are types of data that consist of
numbers, which can be computed mathematically with various standard operators
such as add, minus, multiply, divide and so on. In VBA, the numeric data are
divided into 7 types, which are summarized in Table 2.2
|
Type |
Storage |
Range of
Values |
|
Byte |
1 byte |
0 to 255 |
|
Integer |
2 bytes |
-32,768 to 32,767 |
|
Long |
4 bytes |
-2,147,483,648 to
2,147,483,648 |
|
Single |
4 bytes |
-3.402823E+38 to
-1.401298E-45 for negative values
1.401298E-45 to 3.402823E+38 for positive values. |
|
Double |
8 bytes |
-1.79769313486232e+308 to
-4.94065645841247E-324 for negative values
4.94065645841247E-324 to 1.79769313486232e+308 for positive values. |
|
Currency |
8 bytes |
-922,337,203,685,477.5808
to 922,337,203,685,477.5807 |
|
Decimal |
12 bytes |
+/-
79,228,162,514,264,337,593,543,950,335 if no decimal is use
+/- 7.9228162514264337593543950335 (28 decimal places). |
Table 2.2:
Numeric Data Types
The nonnumeric data types are
summarized in Table 2.3
|
Data
Type |
Storage |
Range |
|
String(fixed length) |
Length of string |
1 to 65,400 characters |
|
String(variable length) |
Length + 10 bytes |
0 to 2 billion characters |
|
Date |
8 bytes |
January 1, 100 to December
31, 9999 |
|
Boolean |
2 bytes |
True or False |
|
Object |
4 bytes |
Any embedded object |
|
Variant(numeric) |
16 bytes |
Any value as large as
Double |
|
Variant(text) |
Length+22 bytes |
Same as variable-length
string |
Table 2.3:
Nonnumeric Data Types
You can declare the variables implicitly or explicitly.
For example, sum=text1.text means that the variable sum is declared implicitly
and ready to receive the input in Text1 textbox. Other examples of implicit
declaration are volume=8 and label=￣Welcome￣. On the other hand, for explicit
declaration, variables are normally declared in the general section of the
codes' windows using the Dim statement. The format is as follows:
Dim variableName as
DataType
Example 2.1
Dim password As String
Dim yourName As String
Dim firstnum As Integer
Dim secondnum As Integer
Dim total As Integer
Dim BirthDay As Date
You may also combine them in one line, separating each
variable with a comma, as follows:
Dim password As String, yourName As String, firstnum As Integer.
If the data type is not specified, VB will automatically
declare the variable as a Variant. For string declaration, there are two
possible formats, one for the variable-length string and another for the
fixed-length string. For the variable-length string, just use the same format as
Example 2.1 above. However, for the fixed-length string, you have to use the
format as shown below:
Dim VariableName as String * n
where n defines
the number of characters the string can hold. For example, Dim yourName as
String * 10 mean yourName can hold no more than 10 Characters.
|
Example 2.2
In this example, we declared three types of variables,
namely the string, date and currency.
Private Sub CommandButton1_Click()
Dim YourName As String
Dim BirthDay As Date
Dim Income As Currency
YourName = "Alex"
BirthDay = "1 April 1980"
Income = 1000
Range("A1") = YourName
Range("A2") = BirthDay
Range("A3") = Income
End Sub
|
The output screen of
Example 2.2
 |
|