2.1 The
Concept of Variables
Variables are like mail boxes in the post office. The content of
the variables changes every now and then, just like the mail boxes. In Excel VBA,
variables are areas allocated by the computer memory to store data. Like the mail
boxes, each variable must be given a name.
The following are the rules when naming the variables in
Excel VBA
-
They must not exceed 40 characters
- They must contain only letters, numbers and underscore chacaters
-
No spacing is allowed
-
It must not begin with a number
-
Period is not permitted
Table
2.1 : Examples of valid and invalid variable names
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 |
2.2 Data Types
Excel VBA data types can be grossly divided into two types, the numeric data types and non-numeric data types. They are classified below:
2.2.1 Numeric Data Types
Numeric data types are types of data that consist of
numbers, which can be computed mathematically with various standard aritmetic operators
such as add, minus, multiply, divide and so on. In Excel VBA, the numeric data are
divided into 7 types, which are summarized in Table 2.2
Table 2.2:
Numeric Data Types

2.2.2 Non-numeric
Data Types
The nonnumeric data types are
summarized in Table 2.3
Table 2.3:
Nonnumeric Data Types
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 |
2.3 Declaration of variables
In Excel VBA, we needs to declare the variables before using them by assigning names and data types.
You can declare the variables implicitly or explicitly.
2.3.1 Implicit Declaration
We can use a variable without openly(explicitly) declare it if we assign an initial value to it. For example,
MyFirstName="John"
MyAge=32
Excel VBA automatically create two varaibles MyFirstName and MyAge as variants, and they are assigned data as John and 32 respectively. This type of declaration is called implicit declaration.
2.3.2 Explicit Declaration
Implicit declaration of variable often leads to errors in writing code therefore it is better to declare a variable explicitly. Variables are normally declared in the general section of the
codes' window using the Dim statement.
The syntax 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, Excel VBA 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
Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved
Contact: admin@excelvbatutor.com [Privacy Policy]