Variable types in VBA

When a name has been assigned to a variable, the programmer should decide what data type is to be assigned to it, as this defines what sort of data the variable may hold. For example, if we wanted to declare a variable that is going to hold a person's age it would best be a whole number -an integer. The variable would then be declared as type Integer and could be said to be an integer variable. A variable that holds textual data, such as a person's address would be declared as a String data type. A variable holding currency data, such as the cost of an item in a supermarket would be Currency. There are many other data types available in VBA, and Table 5.1 lists them all.

Table 5.1 Data types in Visual Basic for Applications for Excel

Data type

Description of range

Size in bytes

Boolean

Logical values: True or False

2 (16 bits)

Integer

-32768 to 32767

2 (16 bits)

Long

-2,147,483,648 to 2,147,483,647

4 (32 bits)

Single

Single precision floating point i.e.

Negative numbers from: -3.402823 X 1038 to -1.401298 X 10-45 Positive numbers from: 1.401298. X 10-45 to 3.402823 X 1038

4 (32 bits)

Double

Double precision floating point

8 (64 bits)

Currency

Accurate fixed point calculation

8 (64 bits)

Date

Stores date and time information.

Times range from 00:00:00 to 23:59:59.

8 (64 bits)

Object

Used to access any object recognised by VBA

4 (32 bits)

String

Stores text. "abc ABC 123 [email protected]£"

1 per char

Variant

Can store any other data type. Range depends on the data stored. If text, the range is of type String. If numeric, the range is of type Double.

16 + 1 per char

Array

Multidimensional data type. Each array element is of the same base type. i.e. integer, string, long etc.

Variable

User-defined

Data structures

Variable

An integer variable is assigned two bytes of memory. This gives a range of possible values of between -32768 and +32767 but this is not enough for some integer values. VBA therefore includes another integer data type called Long that assigns four bytes of memory. The choice between Integer and Long, will clearly depend on what range of values are likely to be represented by the variable. If the range of possible values is -32768 to 32767 then a data type Integer could be assigned to that variable. However, if a variable is to be used which is likely to store an integer value outside this range, then the data type would have to be Long. Similarly, when deciding on a choice between Single, Double, and Currency, the programmer should consider the likely data range taking extreme possibilities into consideration.

If in doubt about the choice of data type to assign to a variable, choose the maximum size data type.

A variable declared as a String data type may contain any text characters: alphabetic, numeric digits, punctuation characters, or other ASCII character symbols. A string size can befixed or variable (see the examples that follow). Variable length, as the name suggests, permits a string to be of any length. The fixed length is assigned by the programmer - for example a string assigned to a person's name might be fixed length 20 characters. When a name is assigned to a fixed length string is shorter than the fixed length, then the remainder of the string is padded with spaces to reach the correct length. The default value for an unassigned string variable is "". While the default value for an assigned string variable is the number of spaces corresponding to the fixed size for a fixed length string.

Notice that VBA also includes a generic Object data type. It is possible to declare any Excel object using this type, but it is more efficient programming to restrict the object to a specific class declaration where possible. For example, if the programmer knows that a declared variable is going to be a Range object, then it is better to declare that variable as a Range data type rather than a generic Object data type. The reason is that Excel cannot tell what specific type of object it is using until the program is running. This is known as late binding and some execution time is wasted in making this determination. If the programmer has not declared the data type, then VBA will assign default declaration to Variant. The default value is Empty.

0 -1

Responses

  • Fiorenza
    What are the types of variable vba?
    7 years ago

Post a comment