Lesson 2

Lesson 2: Working with Variables

Continue learning classic Excel VBA with the same shared lesson template and cleaner visual style.

Classic Excel VBA Shared modern template Ad-free lesson layout

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. Like the mail boxes, each variable must be given a name.

The following are rules for naming variables:

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

There are two data types in Excel VBA , the numeric data types and non-numeric data types, as classified below:

2.2.1 Numeric Data Types

Numeric data types are types of data that consist of numbers, which can be computed mathematically . In Excel VBA, the numeric data are divided into 7 types, as ummarized 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 must declare the variables before using them by assigning names and data types. You may declare the variables implicitly or explicitly.

2.3.1 Implicit Declaration

Implicit declaration means we assign an initial value to a variable. 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.

2.3.2 Explicit Declaration

Explicit declaration means declaring variables 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

❮ Previous Lesson Next Lesson ❯