Microsoft Excel VBA - Lesson 03: Variables and Data Types

	Variables and Data Types

Variables

Introduction

To use some values in code, you must first create them. The computer memory is made of small storage areas used to hold the values of your application. When you use a value in your code, the computer puts it in a storage area. When you need it, you let the computer know. The machine "picks it up", brings it to you, and then you can use it as you see fit.

In the world of computer programming, a variable is a value you ask the computer to temporarily store in its memory while the program is running.

Practical Learning: Creating a Macro

Start Microsoft Excel
On the Ribbon, click Developer.
In the Code section, click Record Macro
Set the Name of the macro as Variables
Click OK
In the document, whatever box is selected (don't click any), type =2
On the Formula Bar, click the Enter button
In the Code section of the Ribbon, click Stop Recording
To open Microsoft Visual Basic, in the Code section of the Ribbon, click Macros
In the Macros dialog box, make sure Variables is selected and click Edit

Declaring a Variable

When writing your code, you can use any variable just by specifying its name. When you provide this name, the computer directly reserves an area in memory for it. Microsoft Visual Basic allows you to directly use any name for a variable as you see fit. Fortunately, to eliminate the possibility of confusion, you can first let Visual Basic know that you will be using a variable.

In order to reserve that storage area, you have to let the computer know. Letting the computer know is referred to as declaring the variable. To declare a variable, you start with the Dim word, like this:

Dim

A variable must have a name. The name is written on the right side of the Dim word. There are rules you should follow when naming your variables:

The name of a variable must begin with a letter or an underscore
After starting with a letter or an underscore, the name can be made of letters, underscores, and digits in any order
The name of a variable cannot have a period
The name of a variable can have up to 255 characters.
The name of a variable must be unique in the area where it is used

There are some words you should (must) not use to name your variables. Those words are reserved for the VBA internal use. Therefore, those words are called keywords. Some of them are:

And (Bitwise)	And (Condition)	As	Boolean	ByRef	Byte
ByVal	Call	Case	CBool	CByte	CDate
CDbl	CInt	CLng	Const	CSng	CStr
Date	Dim	Do	Double	Each	Else
ElseIf	End	EndIf	Error	False	For
Function	Get	GoTo	If	Integer	Let
Lib	Long	Loop	Me	Mid	Mod
New	Next	Not	Nothing	Option	Or (Bitwise)
Or (Condition)	Private	Public	ReDim	REM	Resume
Select	Set	Single	Static	Step	String
Sub	Then	To	True	Until	vbCrLf
vbTab	With	While	Xor

As mentioned already, to declare a variable, type Dim followed by a name. Here is an example:

Sub Exercise()
    Dim Something
End Sub

Declaring a variable simply communicates to Visual Basic the name of that variable. You can still use a mix of declared and not-declared variable. If you declare one variable and then start using another variable with a similar but somewhat different name, Visual Basic would still consider that you are using two variables. This can create a great deal of confusion because you may be trying to use the same variable referred to twice. The solution to this possible confusion is to tell Visual Basic that a variable cannot be used if it has not been primarily declared. To communicate this, on top of each file you use in the Code Editor, type:

Option Explicit

This can also be done automatically for each file by checking the Require Variable Declaration in the Options dialog box.

Practical Learning: Using a Variable

On the main menu of Microsoft Visual Basic, click Tools -> Options...
Click the Editor property page if necessary. In the Code Settings section, put a check mark in the Require Variable Declaration check box
Click OK and return to Microsoft Excel
To close Microsoft Excel, click the Office button and click Exit Excel
When asked whether you want to save, click No
Re-start Microsoft Excel

Declaring Many Variables

In a regular application, it is not unusual to want to use many variables. Once again, you should make it a habit to always declare a variable before using it. To declare a new variable after declaring a first one, you can simply go to the next line and use the Dim keyword to declare the new variable. Here is an example:

Sub Exercise()
    Dim Something
    Dim Whatever
End Sub

In the same way, you can declare as many variables as you want. Instead of declaring each variable on its own line, you can declare more than one variable on the same line. To do this, use one Dim keyword and separate the names of variables with commas. Here are examples:

Sub Exercise()
    Dim Father, Mother
    Dim Son, Daughter, Nephew, Niece
    Dim GrandMa
End Sub

Notice that each line uses its own Dim keyword and every new line of declaration(s) must have a Dim keyword.

Value Assignment

We saw that when you declare a variable, the computer reserves a memory space for it but the space is kept empty. After declaring the value, you can store a value you want in the memory that was reserved for it.

To store a value in the memory reserved for a variable, you can assign a value to the variable. To do this, type the name of the variable, followed by the assignment operator which is =, followed by the value you want to store. Here is an example:

Sub Exercise()
    Dim Value
    
    Value = 9374
End Sub

As we will learn in the next few lessons, there are different types of values you will use in your document. Also as we will see, the value you (decide to) store must be in accordance with the type of memory that the computer had reserved for the variable.

After assigning a value to a variable, you can use that variable knowing the value it is currently holding. At any time and when necessary, you can change the value held by a variable. That's why it is called a variable (because its value can vary or change). To change the value held by a variable, access the variable again and assign it the new desired value.

Introduction to Data Types

A Variable As

A data type tells the computer what kind of variable you are going to use. Before using a variable, you should know how much space it will occupy in memory. Different variables use different amount of space in memory. The information that specifies how much space a variable needs is called a data type. A data type is measured in bytes.

To specify the data type that will be used for a variable, after typing Dim followed by the name of the variable, type the As keyword, followed by one of the data types we will review next. The formula used is:

Dim VariableName As DataType

We mentioned earlier that you could use various variables if you judge them necessary. When declaring such variables, we saw that you could declare each on its own line. To specify the data type of a variable, use the same formula as above. Here is an example:

Sub Exercise()
    Dim FirstName As DataType
    Dim LastName As DataType
End Sub

We also saw that you could declare many variables on the same line as long as you separate the names of the variables with commas. If you are specifying the data type of each, type the comma after each variable. Here are examples:

Sub Exercise()
    Dim FirstName As DataType, LastName As DataType
    Dim Address As DataType, City As DataType, State As DataType
    Dim Gender As DataType
End Sub

This code appears as if there is only one type of data. In the next few sections, we will review various types of values that are available. To declare variables of different data types, you declare each on its own line as we saw earlier:

Sub Exercise()
    Dim FullName As DataType1
    Dim DateHired As DataType2
    Dim EmploymentStatus As DataType3
End Sub

You can also declare variables of different data types on the same line. To do this, use one Dim keyword and separate the declarations with commas. Here are.examples:

Sub Exercise()
    Dim FullName As DataType1, DateHired As DataType2
    Dim EmploymentStatus As DataType3
End Sub

Type Characters

To make variable declaration a little faster and even convenient, you can replace the As DataType expression with a special character that represents the intended data type. Such a character is called a type character and it depends on the data type you intend to apply to a variable. When used, the type character must be the last character of the name of the variable. We will see what characters are available and when it can be applied.

Value Conversion

Every time the user enters a value in an application. That value is primarily considered as text. This means that, if you want to use such a value in an expression or a calculation that expects a specific value other than text, you must convert it from that text. Fortunately, Microsoft Visual Basic provides an effective mechanism to convert a text value to one of the other values we will see next.

To convert text to another value, there is a keyword adapted for the purpose and that depends on the type of value you want to convert it to. We will mention each when necessary.

Integral Numeric Variables

Introduction

If you are planning to use a number in your program, you have a choice from different kinds of numbers that the Visual Basic language can recognize. The Visual Basic language recognizes as a natural number any number that doesn't include a fractional part. In the Visual Basic language, the number is made of digits only as a combination of 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. No other character is allowed. In future lessons, we will learn that in Microsoft Excel, you can use a comma to separate the thousands, which would make the number easy to read. Microsoft Excel recognizes the comma separator, the Visual Basic language doesn't.

By default, when we refer to a natural number, we expect it in decimal format as a combination of digits. The Visual Basic language also supports the hexadecimal format. A hexadecimal number starts with &H followed by a combination of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, a, b, c, d, e, f, A, B, C, D, E, and F. An example would be &H28E4AABF.

Byte

To declare a variable that would hold natural numbers that range from 0 to 255, use the Byte data type. Here is an example:

Sub Exercise()
    Dim StudentAge As Byte
End Sub

There is no type character for the Byte data type.

After declaring the variable, you can assign it a small positive number. Here is an example:

Sub Exercise()
    Dim Value As Byte
    
    Value = 246
End Sub

You can also use the number in hexadecimal format as long as the number is less than 255.

If you give either a negative value or a value higher to 255, when you attempt to access it, you would receive an error:

Error

To convert a value to a small number, you can use CByte(). The formula to use would be:

Number = CByte(Value to Convert to Byte)

When using CByte(), enter the value to convert in the parentheses.

Practical Learning: Using Byte Variables

In the code, click ActiveCell, press Home, press Enter, and press the up arrow key

To use byte variables, change the code as follows:

Sub Variables()
    Dim Shirts As Byte
    Dim Pants As Byte
    Dim OtherItems As Byte
    Dim TotalItems As Byte
    
    Shirts = 6
    Pants = 4
    OtherItems = 2
    TotalItems = Shirts + Pants + OtherItems
    
    ActiveCell.FormulaR1C1 = TotalItems
End Sub

To return to Microsoft Excel, on the Standard toolbar, click the View Microsoft Excel button
In Microsoft Excel, click any box
In the Code section of the Ribbon, click the Macros button
In the Macros dialog box, make sure Exercise1 is selected and click Run
To return to Microsoft Visual Basic, in the Code section of the Ribbon, click Visual Basic

Integer

To declare a variable that would hold a number that ranges from -32768 to 32767, use the Integer data type. Here is an example of declaring an integer variable:

Sub Exercise()
    Dim Tracks As Integer
End Sub

Instead of using As Integer, you can use the % type character. Therefore, the above declaration could be done as follows:

Sub Exercise()
    Dim Tracks%
End Sub

After declaring the variable, you can assign the desired value to it. If you assign a value lower than -32768 or higher than 32767, when you decide to use it, you would receive an error.

If you have a value that needs to be converted into a natural number, you can use CInt() using the following formula:

Number = CInt(Value to Convert)

Between the parentheses of CInt(), enter the value, text, or expression that needs to be converted.

Long

A long integer is a number that can be used for a variable involving greater numbers than integers. To declare a variable that would hold such a large number, use the Long data type. Here is an example:

Sub Exercise()
    Dim Population As Long
End Sub

The type character for the Long data type is @. The above variable could be declared as:

Sub Exercise()
    Dim Population@
End Sub

A Long variable can store a value between – 2,147,483,648 and 2,147,483,647 (remember that the commas are used to make the numbers easy to read; do not be used them in your code). Therefore, after declaring a Long variable, you can assign it a number in that range.

To convert a value to a long integer, call CLng() using the following formula:

Number = CLng(Value to Convert)

To convert a value to long, enter it in the parentheses of CLng().

Decimal Variables

Single Precision

In computer programming, a decimal number is one that represents a fraction. Examples are 1.85 or 426.88. If you plan to use a variable that would that type of number but precision is not your main concern, declare it using the Single data type. Here is an example:

Sub Exercise()
    Dim Distance As Single
End Sub

The type character for the Single data type is !. Based on this, the above declaration could be done as:

Sub Exercise()
    Dim Distance!
End Sub

A Single variable can hold a number between 1.401298e^–45 and 3.402823e³⁸. for negative values or between 1.401298e^–45 and 3.402823e³⁸ for positive values.

If you have a value that needs to be converted, use CSng() with the following formula:

Number = CSng(Value to Convert)

In the parentheses of CSng(), enter the value to be converted.

Double Precision

If you want to use a decimal number that requires a good deal of precision, declare a variable using the Double data type. Here is an example of declaring a Double variable:

Sub Exercise()
    Dim Distance As Double
End Sub

Instead of As Double, the type character you can use is #:

Sub Exercise()
    Dim Distance#
End Sub

A Double variable can hold a number between –1.79769313486231e³⁰⁸ and
–4.94065645841247e^–324 for negative values or between 4.94065645841247e^–324 and 1.79769313486231e³⁰⁸ for positive values.

To convert a value to double-precision, use CDbl() with the following formula:

Number = CDbl(Value to Convert)

In the parentheses of CDbl(), enter the value that needs to be converted.

Practical Learning: Using Decimal Variables

Change the code as follows:

Sub Variables()
    Dim Side As Double
    Dim Perimeter As Double
    
    Side = 32.75
    Perimeter = Side * 4
    
    ActiveCell.FormulaR1C1 = Perimeter
End Sub

To return to Microsoft Excel, on the Standard toolbar, click the View Microsoft Excel button
In Microsoft Excel, click any box
In the Code section of the Ribbon, click the Macros button
In the Macros dialog box, make sure Exercise1 is selected and click Run
To return to Microsoft Visual Basic, in the Code section of the Ribbon, click Visual Basic

A String

A string is a character or a combination of characters that constitute text of any kind and almost any length. To declare a string variable, use the String data type. Here is an example:

Sub Exercise()
    Dim CountryName As String
End Sub

The type character for the String data type is $. Therefore, the above declaration could be written as:

Sub Exercise()
    Dim CountryName$
End Sub

As mentioned already, after declaring a variable, you can assign a value to it. The value of a string variable must be included inside of double-quotes. Here is an example:

Sub Exercise()
    Dim CountryName As String
    
    CountryName = "Brésil"
End Sub

If you have a value that is not primarily text and you want to convert it to a string, use CStr() with the following formula:

CStr(Value To Convert to String)

In the parentheses of the CStr(), enter the value that you want to convert to string.

Practical Learning: Using a String

Change the code as follows:

Sub Variables()
    Dim CustomerName As String
    
    CustomerName = "Paul Bertrand Yamaguchi"
    
    ActiveCell.FormulaR1C1 = CustomerName
End Sub

To return to Microsoft Excel, on the Standard toolbar, click the View Microsoft Excel button
In Microsoft Excel, click any box
In the Code section of the Ribbon, click the Macros button
In the Macros dialog box, make sure Exercise1 is selected and click Run
To return to Microsoft Visual Basic, in the Code section of the Ribbon, click Visual Basic

Currency Values

The Currency data type is used to deal with monetary values. Here is an example of declaring it:

Sub Exercise()
    Dim StartingSalary As Currency
End Sub

Instead of using the As Currency expression, you can use @ as the type character to declare a currency variable. Here is an example of declaring it:

Sub Exercise()
    Dim StartingSalary@
End Sub

A variable declared with the Currency keyword can store a value between – 922,337,203,685,477.5808 and 922,337,203,685,477.5807. Once again, keep in mind that the commas here are used only to make the number easy to read. Don't use the commas in a number in your code. Also, when assigning a value to a currency-based variable, do not use the currency symbol.

Here is an example of assigning a currency number to a variable:

Sub Exercise()
    Dim StartingSalary As Currency
    
    StartingSalary = 66500
End Sub

If you want to convert a value to currency, use CCur() with the following formula:

Number = CCur(Value to Convert)

To perform this conversion, enter the value in the parentheses of CCur().

Practical Learning: Using Currency Values

Change the code as follows:

Sub Variables()
    Dim NumberOfShirts As Byte
    Dim PriceOneShirt As Currency
    Dim TotalPriceShirts As Currency
    
    NumberOfShirts = 5
    PriceOneShirt = 1.25
    TotalPriceShirts = NumberOfShirts * PriceOneShirt
    
    ActiveCell.FormulaR1C1 = TotalPriceShirts
End Sub

To return to Microsoft Excel, on the Standard toolbar, click the View Microsoft Excel button
In Microsoft Excel, click any box
In the Code section of the Ribbon, click the Macros button
In the Macros dialog box, make sure Exercise1 is selected and click Run
To return to Microsoft Visual Basic, in the Code section of the Ribbon, click Visual Basic

A Date

In Visual Basic, a Date data type can be used to store a date value. Therefore, to declare either a date or a time variables, use the Date data type. Here is an example:

Sub Exercise()
    Dim DateOfBirth As Date
End Sub

After declaring the variable, you can assign it a value. A date value must be included between two # signs. Here is an example:

Sub Exercise()
    Dim DateOfBirth As Date
    
    DateOfBirth = #10/8/1988#
End Sub

There are various formats you can use for a date. We will deal with them in another lesson.

If you have a string or an expression that you want to convert to a date value, use CDate() based on the following formula:

Result = CDate(Value to Convert)

In the parentheses of CDate(), enter the value that needs to be converted.

Practical Learning: Using a Date

Change the code as follows:

Sub Variables()
    Dim DepositDate As Date
    
    DepositDate = #2/5/2008#
    
    ActiveCell.FormulaR1C1 = DepositDate
End Sub

To return to Microsoft Excel, on the Standard toolbar, click the View Microsoft Excel button
In Microsoft Excel, click any box
In the Code section of the Ribbon, click the Macros button
In the Macros dialog box, make sure Exercise1 is selected and click Run
To return to Microsoft Visual Basic, in the Code section of the Ribbon, click Visual Basic

A Time

In Visual Basic, the Date data type can also be used to store a time value. Here is an example of declaring a variable that can hold a time value:

Sub Exercise()
    Dim ShiftTimeIn As Date
End Sub

After declaring the variable, to assign a value to it, include the value between two # signs. The value follows different rules from a date.

To convert a value or an expression to time, use CDate().

Any-Type Variables

A Variant

So far, we declared variables knowing the types of values we wanted them to hold. VBA provides a universal (or vague) data type you can use for any type of value. The Variant data type is used to declare a variable whose type is not explicitly specified. This means that a Variant data type can hold any type of value you want.

Here are examples of Variant-declared variables that hold different types of values:

Sub Exercise()
    Dim FullName As Variant
    Dim EmploymentStatus As Variant
    Dim HourlySalary As Variant
    Dim DateHired As Variant
    
    FullName = "Patricia Katts"
    EmploymentStatus = 2
    HourlySalary = 35.65
    DateHired = #6/22/2004#
End Sub

A Variable Without a Data Type

In the variables we declared in the last few sections, we specified a data type for each. You can declare a variable without giving its data type. Here are examples:

Sub Exercise()
    Dim FullName
    Dim EmploymentStatus
    Dim HourlySalary
    Dim DateHired
End Sub

Of course, you can declare more than one variable on the same line.

To indicate how much space is needed for the variable, you must assign it a value. Here are examples:

Sub Exercise()
    Dim FullName
    Dim EmploymentStatus
    Dim HourlySalary
    Dim DateHired
    
    FullName = "Patricia Katts"
    EmploymentStatus = 2
    HourlySalary = 35.65
    DateHired = #6/22/2004#
End Sub

Once the variable holds a value, you can use it as you see fit.

The Scope or Lifetime of a Variable

Introduction

So far, we were declaring our variables between the Sub Name and the End Sub lines. Such a variable is referred to as a local variable. A local variable is confined to the area where it is declared. Here is an example:

Option Explicit

Sub Exercise()
    Dim FirstName As String
    
    FirstName = "Patricia"
End Sub

You cannot use such a variable outside of its Sub Name and the End Sub lines.

Global Variables

A global variable is a variable declared outside of the Sub Name and the End Sub lines. Such a variable is usually declared in the top section of the file. Here is an example:

Option Explicit

Dim LastName As String

Sub Exercise()
    
End Sub

After declaring a global variable, you can access it in the other areas of the file. Here is an example:

Option Explicit

Dim LastName As String

Sub Exercise()
    Dim FirstName As String
    
    FirstName = "Patricia"
    LastName = "Katts"
End Sub

Although we declared our global variable inside of the file where it was used, you can also declare a global variable in a separate module to be able to use it in another module.

The Access Level of a Global Variable

Introduction

When using a global variable, the Visual Basic language allows you to control its access level. The access level of a variable is a process of controlling how much access a section of code has on the variable.

Private Variables

A variable is referred to as private if it can be accessed only by code from within the same file (the same module) where it is used. To declare such a variable, instead of Dim, you use the Private keyword. Here is an example:

Option Explicit

Private LastName As String

Sub Exercise()
    Dim FirstName As String
    
    FirstName = "Patricia"
    LastName = "Katts"
End Sub

Remember that a private variable can be accessed by any code in the same module. In the next lesson, we will learn how to create other sections of code.

Public Variables

A variable is referred to as public if it can be accessed by code either from within the same file (the same module) where it is declared or from code outside its module. To declare a public variable, instead of Dim, you use the Public keyword. Here is an example:

Option Explicit

Private LastName As String
Public FullName As String

Sub Exercise()
    Dim FirstName As String
    
    FirstName = "Patricia"
    LastName = "Katts"
    FullName = FirstName & " " & LastName
End Sub

As a reminder, a public variable is available to code inside and outside of its module. This means that you can create a module, declare a public variable in it, and access that variable in another file (module) where needed.

A private variable is available inside its module but not outside its module. If you declare a private variable in a module and try accessing it in another module, you would receive an error:

Module 1:

Option Explicit

Private FullName As String

Module 2:

Option Explicit

Private LastName As String
Private FirstName As String

Sub Exercise()
    FirstName = "Patricia"
    LastName = "Katts"
    FullName = FirstName & " " & LastName
    
    ActiveCell.FormulaR1C1 = FullName
End Sub

This would produce:

Error: Variable Not Declared

Practical Learning: Closing Microsoft Excel

To close Microsoft Visual Basic, on the main menu, click File -> Close and Return to Microsoft Excel
To close Microsoft Excel, click the Office Button and click Exit Excel.
If you are asked whether you want to save the file, click No


Previous	Copyright © 2008-2016, FunctionX, Inc.	Next