In the previous lesson you have learned what the variables are and how to declare them. Now we will go further into this subject and see what types of data a variable can be declared as and how they differ from each other.

All the data types available in Visual Basic have been listed below:

• Text data type (String)
• Numeric data types (Byte, Integer, Long, Single, Double, Currency)
• Date and time data type (Date)
• Logical data type (Boolean)
• Variant data type (Variant)

The data types mentioned above are known as primitive data types.

Apart from them, a variable can also be declared as an object (the number of possible object types is limitless, since you can create your own ones). Objects will be the subject of the later chapters.

String data type is intended to hold the text value.

The declaration of a String variable looks like below:

 

Dim variable As String

The default value for a text variable is an empty string - "".

Before we go any further, we should say a few words about text strings in VBA. We have already touched on this subject in the lesson about variables. Now we will cover it in more details.

The way the VBA compiler recognizes if a piece of code is a string is by checking if it is placed within double quotes.

Let's assume you want to display text abcd in the cell A1 of the worksheet Sheet1. You can't do it just like that:

 

Worksheets("Sheet1").Cells(1,1) = abcd

since the string abcd is not put in quotes and the compiler treats it as if it was the name of a variable.

There are three possible ends to this scenario, but none of them is what we expected:

• If there is no variable called abcd in your code, the application will not even start. The compiler will not be able to compile your project, because of the undeclared variable (this issue has been discussed in the previous chapter about declaring variables).
• If the variable called abcd exists, the value assigned to this variable instead of the string abcd will be displayed in the specified cell (you could see how it works in the example from previous lesson where we used the name of the variable to display the values of consecutive powers in an Excel sheet).
• If there is no variable called abcd in your code but you don't use Option Explicit statement (however, as you already know, it is strongly not recommended), the compiler will create new variable of Variant type named abcd right at the moment when it appears in the code for the first time. This variable will be initialized with default value for variables of Variant type (that is, empty string) and this value will be displayed.

More obvious is the case when the string to be displayed consists of two or more words separated by white spaces:

 

Worksheets("Sheet1").Cells(1,1) = Text to be displayed

This code could not even compile and the statement above would be highlighted in red right after you move to the next line of code, so you will be immediately aware that something is wrong. The line above is interpreted by the compiler as if we simply put together the names of three variables (Text, be, displayed) and a keyword To. This would break syntax compatibility, since you cannot state the names of two variables next to each other without any operator between them.

The correct code for the examples discussed above would look like that:

 

Worksheets("Sheet1").Cells(1,1) = "abcd"

 

Worksheets("Sheet1").Cells(1,1) = "Text to be displayed"

The general rule is to put in quotes anything you want to assign to variable of a String type. However, there are some exceptions described below.

The first of them is a quite common case when you want to assign the value of another variable to a string variable.

Look at the following example:

1
2
3
4
5
6
7


Sub assigningStringVariable()
    Dim a As String
    Dim b As String

    a = "abcd"
    b = a
End Sub

There are two variables of String type declared in the first two lines of the code above (excluding procedure opening line): a and b.

In the line 5 text abcd is assigned to the variable a.

In the line 6 the value of variable a is assigned to the variable b.

In this case a means the name of another variable, not the text to be assigned. That is why it is not embraced in quotes. If you would put the name of variable a into quotes, the compiler would interpret it as if you would like to assign text a to the variable b and afterwards variable a would be equal abcd and variable b would be equal a.

The second case when value is assigned to a variable of a String type without quotes is when you want to assign a value that is in fact the text representation of another type of data (i.e. numbers or dates).

String is the most flexible of all primitive data types, because it is possible to assign virtually every value of any other primitive type to it without causing any error. No matter if it is a text, a number or a date, every value that is assigned to a variable of a String type is considered to be a text by the compilator. For example, you can assign numeric value 1 to a variable of a String type, but the value stored by this variable afterward will be the text containing a single character - 1 - instead of the numeric value 1.

You can wonder if there is any difference between number 1 and text 1. Below is the example that will explain it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17


Sub textVariables()
    Dim s As String
    Dim t As String
    Dim a As Long
    Dim b As Long

    'assigning numbers 1 and 2 to variables of a String type
    s = 1
    t = 2

    'assigning numbers 1 and 2 to variables of numeric type
    a = 1
    b = 2

    Worksheets("Sheet1").Cells(1,1) = s + t
    Worksheets("Sheet1").Cells(2,1) = a + b
End Sub

In rows 2-5 of this example four variables are declared:

• two string variables: s, t
• two numeric variables: a, b

In the next few lines numbers 1 and 2 are assigned to string variables s and t, and after that the same values are assigned to numeric variables a and b. It could seem that both pairs of variables have now the same values - 1 and 2, but as it will turn out in a moment, it is not truth.

In the last two lines of code before closing the subroutine, sums of both pairs of variables are displayed into the worksheet Sheet1.

If you run the code above and look into the worksheet Sheet1 afterwards you will see that number 12 is displayed in the cell A1, but the cell A2 has another value - 3.

It is of course caused by the way the compiler interprets different types of data.

When you add strings to each other (just like texts 1 and 2 held by variables s and t in the example above), all the compiler does it simply join all single characters into one longer text (in our example it has just joined strings 1 and 2 into longer string 12).

When you add values stored by variables of numeric type, they are being summed up (in our example values 1 and 2 are summed up and the result is 3).

In conclusion, though string variables are so flexible and can accept different types of data, you should avoid using them for different purposes than storing texts, since it can cause unexpected results.

All numeric data types are discussed together since they are very similar and the only differences between them are the range of values they can take and whether they can store the fractional part of a number.

There are several numeric data types available in Visual Basic: Byte, Integer, Long, Single, Double and Currency. General form of declaration for each of them is presented below:

 

Dim number As Byte

 

Dim number As Integer

 

Dim number As Long

 

Dim number As Single

 

Dim number As Double

 

Dim number As Currency

Default value for all numeric data types is 0.

Numeric variables are not as flexible as string variables. As you learned, it is possible to assign a value of any primitive data types to a text variable without error being raised. However, this does not apply to numeric variables.

Let's look at the example below:

1
2
3
4


Sub textToNumericVariable
    Dim a As Long
    a = "abcd"
End Sub

In this example we try to assign text abcd to a variable of Long type. Since the compiler cannot convert this text into any numeric values, error Run-time error '13': Type mismatch will be thrown if you run this code.

However, there are cases when it is possible to assign text to a numeric variable. If you try to assign a string that is simply text representation of a number, compiler will easily convert it to this number and assign it to the variable. Example of this case is presented below:

1
2
3
4


Sub assigningTextToNumericVariable
    Dim number As Long
    number = "1"
End Sub

In the third line of this code value "1" is being assigned to the variable number of Long type. As you know from the previous chapter, in VBA everything that is embraced in quotes is a text. Therefore, value 1 in this case is not a number but text, since it is put in quotes. However, the compiler can convert this text to the corresponding numeric value without any problem, so value 1 (not a text any more, but numeric value already) is assigned to the variable number without any errors being generated.

Let's move forward to discuss in details each one of numeric data types. As it was mentioned before, the only differences between them are the available range of values, the accuracy of fractional part and the amount of memory they use.

A variable of Byte type can store only integer numbers from range 0-255.

You should declare variable as a Byte type only if you are sure it will not take values outside of this range (i.e. month number can takes only values from range 1-12 and it can be safely declared as a variable of Byte type).

It can happen that during the macro is running, compiler will get the command to assign negative value or value higher than 255 to a variable of Byte type. In such case an error Run-time error '6' Overflow will be generated.

All the numbers with fractional part are rounded to the nearest integral number before being assigned to a variable of Byte type. For example, if you assign value 1.2 to a variable of Byte type, this variable will equal 1 afterwards (because 1.2 is rounded to 1), and if you assign 1.5, value of 2 will be assigned.

A single variable of Byte type needs only 1 bit of memory and this is the smallest type of data available in VBA. Therefore, if you are sure that a value will not exceed the range limit of Byte type (examples of such values are month number, day number, hours in day, minutes in hour etc.), it is worth to declare it as a variable of Byte type. It will allow you to save some memory and thereby improve your application's efficiency. The positive impact is obviously not visible in small applications we discussed so far, however, in bigger applications with thousands of variables benefits can be quite significant.

Variable of a Integer type can store integer values from range -32 768 to 32 767.

Each attempt to assign value out of this range to a variable of Integer type results in error Run-time error '6' Overflow being generated.

The values with fractional part are rounded to the nearest integral number before being assigned, just like it was described for Byte data type.

A single variable of Integer type requires 2 bits of memory.

Variable of a Long type can store integer values from range -2 147 483 648 to 2 147 483 647.

Each attempt to assign value out of this range to a variable of Long type results in error Run-time error '6' Overflow being generated.

The values with fractional part are rounded to the nearest integral number before being assigned, just like it was described for Byte and Integer data types.

A single variable of Long type requires 4 bits of memory.

Variable of a Single type can store real values from range -3.4⋅1038 to -1.4⋅10-45 for negative values and from 1.4⋅10-45 to 3.4⋅1038 for positive values.

Each attempt to assign value out of this range to a variable of Single type results in error Run-time error '6' Overflow being generated.

A single variable of Single type requires 4 bits of memory.

Variable of a Double type can store real values from range -1.80⋅10308 to -4.94⋅10-324 for negative values and from 4.94⋅10-324 to 1.80⋅10308 for positive values..

A single variable of Double type requires 8 bits of memory.

Variable of a Currency type can store values from range -922 337 203 685 477.5808 to 922 337 203 685 477.5807.

A single variable of Currency type requires 8 bits of memory.

The Currency data type is especially helpful in calculations where precision and accuracy is crucial, like financial calculations. You must be aware that when you use variables of Single or Double type in your calculations, the results are sometimes inaccurate, i.e. operation 2 + 2 can return 3.999999998 instead of 4. In most cases it doesn't matter and efficiency of Single/Double type is more important than their precision. However, if you need exact results in your application you should use Currency data type because it ensures that all the results are accurate.

Date data type is intended to hold information about date and time.

Variables of Date type are declared as below:

 

Dim variable As Date

The date variable can hold dates within the range from 01.01.100 to 31.12.9999. If you try to assign a date out of this range, error Run-time error 5: Invalid procedure Call or argument will be thrown by the compiler.

If you want to assign the specific date to a variable of the Date type, you need to embrace this date with hash characters (#) and separate its parts (years, months, days) from each other by dash (-) or slash (/) characters.

A few examples of assigning date 21st April 2010 to the variable varDate are presented below:

 

varDate = #21-04-2010#

 

varDate = #2010-04-21#

 

varDate = #2010/4/21#

 

varDate = #4/21/2010#

The same rule applies when you try to assign time to a variable of the Date type - the time being assigned must be embraced with hash (#) and its components (hours, minutes, seconds) must be separated by colon (:).

Only hours and minutes are obligatory when using literal time in the code. Seconds can be omitted - in such case default value of 0 is used for representing the number of seconds.

Below are a few examples of assigning time to the variable time.

 
 

'seconds omitted so default value 0 will be used for seconds.
time = #12:00#

 

time = #23:15:20#

You can also assign the combination of a date and a time to a variable of the Date type. The whole expression must be embraced in hash characters (#) and proper separators must be used to divide date and time components from each other - dash(-) or slash (/) for date and colon(:) for time. Additionally, a date part must be separated from a time part by at least one space.

Below are a few examples of assigning the combination of a date and a time to the variable datetime:

 

datetime = #21-04-2010 12:00#

 

datetime = #2010/4/21 13:14:15#

The important attribute of Date type variables is that each of them has its numeric equivalent. The numeric representation of a date consists of two parts:

• Integer part - represents a date part; it is equal to the number of days between the date and the 30th December 1899,
• Fractional part - represents a time part; it refers to the amount of time that has passed from the midnight in relation to 24 hours (e.g. 0.5 for 12:00 AM and 0.75 for 6:00 PM).

This makes it possible to assign numeric values to variables of Date type and execute arithmetic operations. However, as it has been mentioned before, the Date data type has its limitation - it can only hold dates within the range from 01.01.100 to 31.12.9999. Therefore, the range of numbers that can be assigned to variables of Date type is also limited to the range from -657 434 to 2 958 465. If you try to assign a number out of this range to a variable of the Date type, Run-time error '6': Overflow will be thrown.

The table below contains a few examples of dates & times with theirs numeric representations.

DATE	NUMBER
30 December 1899	0
6 July 2009	40 000
13 August 1872	-10 000
6 July 2009, 6:00	40 000.25
13 August 1872, 6:00	-10 000.25

Logical variable is defined in VBA by the keyword Boolean.

Declaration of a logical variable looks like below:

 

Dim variable As Boolean

Variables of Boolean types can get only two values: True or False.

Variables of Boolean type are always initialized with default value False.

Variant is the most universal type of data. Variables declared as of Variant type can accept all kinds of values, no matter primitives, objects or arrays.

Declaration of the variable of Variant type looks like below:

 

Dim variable As Variant

If the Variant type is so flexible, it accepts all values and can be used instead of any other data type, why not use it all the time? Why bother with deciding what data type is the most suitable if all of them could be simply replaced by Variant? Well, there are some arguments against it.

First of all, variables of Variant type need much more memory than variables of other types (e.g. 16 bits for a single number, while the same number declared as Integer needs only 2 bits) that makes macros working slower and less effective.

The even more important disadvantage of using Variant types is losing control of data types. If you see in the code a variable declared as Integer, you know immediately what range of values it can accept. If you accidentally assign a value out of this range, you will know it as soon as you run your code because 'Run-time error' will be thrown (or even earlier, if this issue caused compilation error). If this variable was declared with Variant type, the compiler would not throw any error and there is a risk you would learn about your mistake very late (and as one of the software rules says - the later code error is detected, the higher is the cost of fixing it).

However, there are also cases when using Variant data type is the best or even the only way to solve a problem. Examples of such cases will be discussed in details in the next chapters of this tutorial.