Excel 2010 VBA Lesson 2: Variables, Constants and Data Types.

[Lesson 1]<<[Table of Contents]>>[Lesson 3]

2.1 The Definition of Variables in Excel 2010 VBA 

In general, variables are something that varies or prone to variations, just like the files in your cabinet or the mailboxes where their contents always change from time to time. In Excel 2010 VBA programming environment, variables are areas allocated by the computer memory to hold data.

To safeguard the data created in Excel 2010 VBA, we suggest that  you migrate your essential programming/testing environment into the cloud by remotely loading/accessing your programming tools such as emulators and IDE`s on your preferred device(PC/android/iOS) with high performance hosted citrix xendesktop from CloudDesktopOnline at an unbelievable xendesktop pricing. If you prefer a server, Rent a gpu dedicated server from Apps4Rent with 24*7*365 days assured tech-support & migration assistance.

2.2 Rules in Naming Variables

Like the mailboxes, each variable must be given a name. The following are the rules when naming a variable :

  • They must not exceed 40 characters
  • No space is allowed
  • They must contain only letters, numbers and underscore characters
  • They must not begin with a number, must always begin with a letter
  • Period is not permitted




Examples of valid and invalid variable names are displayed in Table 2.1

Table 2.1

Valid Name Invalid Name
My_Car My.Car
Year1234 1234Year
Long_Name999 Father&Son
A8Group A Group

2.3 Data Types

Excel 2010 VBA  data types can be grossly divided into two types, the numeric data types and non-numeric data types. They are classified below:

2.3.1 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 addition, subtraction, multiplication, division and more. Examples of numeric data types are examination marks, height, weight, number of students in a class, share values, the price of goods, monthly bills, fees and others.

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.3.2 Non-Numeric Data Types

Non-numeric data types are data that cannot be manipulated mathematically using standard arithmetic operators. The non-numeric data comprises text or string data types, the Date data types, the Boolean data types that store only two values (true or false), Object data type and Variant data type .They are summarized in Table 5.2

Table 5.2: 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.3 Testing Data Types

We can test out the type of data held by a variant by using the function VarType. To run the test, enter the Excel 2010 VBA code IDE and create a subroutine such as below:

Sub TestVarType()
 MyVariable1 = "Francis"
 MsgBox VarType(MyVariable1)
 MyVariable1 = 1234
 MsgBox VarType(MyVariable1)
End Sub

When you run the macro, the message box will first display 8, which means it is a string. It will display 2 subsequently, which means it is an integer. We can call MyVariable1 a variant data type, and Excel 2010 VBA is clever enough to automatically identify the type of data.

2.4 Declaration of variables

In Excel  VBA 2010, we need to declare the variables before using them by assigning names and data types.

2.4.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"

Excel  2010 VBA  will automatically create a variable MyFirstName as a variant, and it will hold the data as John. This type of declaration is called implicit declaration.

2.4.2 Explicit Declaration

Implicit declaration of the variable often leads to errors in writing code, therefore, it is better to declare a variable explicitly. It is  declared using the Dim statement as follows:

Dim VariableName As DataType

If you want to declare more variables, you can declare them in separate lines or you may also combine more in one line, separating each variable with a comma, as follows:

Dim VariableName1 As DataType1, VariableName2 As DataType2, VariableName3 As DataType3

Example 2.1

Dim password As String
Dim yourName As String
Dim firstnum As Integer
Dim secondnum As Integer
Dim total As Integer
Dim doDate As Date
Dim password As String,  yourName As String, firstnum As Integer

For fixed-length string, you can use the statement to declare the variable:

Dim VariableName as String * n,

where n defines the number of characters the string can hold.
Example:

Dim yourName as String * 10

yourName can hold no more than 10 Characters.

2.4.3  Scope of Declaration

Other than using the Dim keyword to declare the data, you can also use other keywords to declare the data. These keywords indicate the scope of the declaration, they are private ,static and public,as follows:

Private VariableName as Datatype
Static VariableName as Datatype
Public VariableName as Datatype

The Private declares a local variable or a variable that is local to a procedure or module in Excel VBA 2010.

The Static keyword declares a variable that can be used multiple times, even after a procedure has been terminated. Most variables created inside a procedure are discarded by Visual Basic when the procedure is finished, static keyword preserves the value of a variable even after the procedure is terminated.

The Public keyword declares a global variable, which means it can be used by all the procedures and modules of the whole program.

2.5  Constants

Constants are different from variables in the sense that their values do not change during the running of the program. In Excel 2010 VBA , the statement to declare a constant is

Const Constant Name As Data Type = Value

Examples:

Const Pi As Single=3.142
Const Temp As Double=37
Const Score As Single=100



[Lesson 1]<<[Table of Contents]>>[Lesson 3]

Excel VBA Lesson 2: Working with Variables in Excel VBA

<<Lesson 1>> [Contents] <<Lesson 3>>

2.1 The Concept of Variables in Excel VBA

A variable is like a  mailbox in the post office as its content changes every now and then, just like the mailboxes. In Excel VBA, variables are areas allocated by the computer memory to hold data. Similar to the mailbox, each variable must be given a name. In order to name a variable in Excel VBA, you have to follow a set of rules, as follows:

  • It must be less than 255 characters
  • No spacing is allowed
  • It must not begin with a number
  • Period is not permitted




Examples of valid and invalid variable names are displayed in Table 2.1

Excel vba table2.1

2.2 Declaring Variables

In Excel VBA, you need to declare the variables before using them by assigning names and data types. We can divide the Excel VBA data types  into two types,  the numeric data types and the non-numeric data types

2.2.1 Numeric Data Types

Numeric data types are types of data that consist of numbers. We can compute the numeric data mathematically with various standard operators such as plus, subtract, multiply, divide and so on. In Excel VBA,  we can divide the numeric data into 7 types,  as shown in Table 2.2

Excel vba table2.2

 

2.2.2 Non-numeric Data Types

The non-numeric data types are summarized in Table 2.3 below.
vba_table2.3

We 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 the Text1 textbox. In addition, other examples of the implicit declaration are volume=8 and label=”Welcome”. On the other hand, for the explicit declaration, we declare the variables in the general section of the code window by 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

On the other hand, you may  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 is as follows:

 

<<Lesson 1>> [Contents] <<Lesson 3>>