 Lesson 2: Working with Variables

2.1 The Concept of Variables

Variables are like mailboxes in the post office. The content of the variables changes every now and then, just like the mailboxes. In Excel VBA, variables are areas allocated by the computer memory to store data. Like the mailboxes, 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 characters
• 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 arithmetic 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

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).

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 need 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 variables 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`

means 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
```