Data Types

A variable can be given a data type that determines the type of data it can store. This can have an effect on the efficiency of your code. If there is no data type, the default type is Variant.

Variant

A variant can store all kinds of data, whether it is text, numbers , dates, or other information. It can even store an entire array. A variant variable can freely change its type at runtime, whereas one that has been specified as, for example, a string cannot. You can use the function VarType to find out the type of data held by a variant:

 Sub TestVariables() 
 stemp = "richard" 
 MsgBox VarType(stemp) 
 stemp = 4 
 MsgBox VarType(stemp) 
 End Sub 

The message box will first display 8, which means that it is a string. It will then display 2, which means that it is an integer.

Table 2-1 shows the return values for specific data types.

Table 2-1: VarType Return Values

ReturnValue	Type
	Empty
1	Null
2	Integer
3	Long
4	Single
5	Double
6	Currency
7	Date/Time
8	String

VBA always uses the most efficient means of storing data in a variant. As you can see from the preceding example, it automatically changes to suit the data stored in it.

If you perform a mathematical operation on a variant that is not a numeric value, you will get a Type MisMatch error. This means that you are trying to put a data type into a variable not set up to hold that data type ‚ a bit like banging a square peg into a round hole. In this case, it may be that you are trying to perform a mathematical operation on a variant holding a string of text.

You can use the IsNumeric function to test if the value of a variant is a number ‚ it returns true or false (nonzero or zero).

 Sub TestNumeric() 
 temp="richard" 
 MsgBox IsNumeric(temp) 
 End Sub 

This will give the result False.

Date/Time Values Stored in Variants

Variant variables can also contain Date/Time values. This is a floating point number ‚ the integer part represents the days since 31-Dec-1899, and the decimal part represents the hours, minutes, and seconds expressed as a proportion of 24 hours. For example, 37786.75 represents 14-June-2003 at 18:00. The difference between 31-Dec-1899 and 14-June-2003 is 37,786 days, and 0.75 of 24 hours is 18 hours.

Adding or subtracting numbers adds or subtracts days. Adding decimals increases the time of day; for example, adding 1/24 adds one hour . There are a number of functions for handling date and time, as explained in Chapter 5.

Note that the interpretation of day and month is dependent on the Regional Options settings within the Windows Control Panel. If you set your date to mm/dd/yy in Regional Options, this will be the default interpretation of day and month.

As you can use IsNumeric to determine if there is a numeric value, you can use the IsDate function to determine if there is a date value.

 temp = "01-Feb-2002" 
 MsgBox IsDate(temp) 

This will return True (nonzero).

Empty Value

A variant that has not had a variable assigned to it will have an empty value. This can be tested for using the IsEmpty function.

 MsgBox IsEmpty(temp) 

This will return True (nonzero) because temp has not been assigned a value.

Null Value

A variant can contain a special value of Null. The Null value is used to indicate unknown or missing data. Variables are not set to Null unless you write code to do this. If you do not use Null in your application, you do not have to worry about Null.

Note ‚  

Null is not 0. This is an easy but incorrect assumption to make when you're starting to learn VBA.

The safest way to check for a Null value in your code is to use IsNull . Other methods , such as the statement Is Null , may not give correct results.

 Sub TestNull() 
 temp=Null 
 Msgbox IsNull(temp) 
 End Sub 

The result will be True (nonzero).

Other Data Types

Why use data types other than Variant? Because Variant may not use the best data type for the purpose. If you want to create concise fast code, then you need other data types. For example, if you are doing lots of mathematical calculations on relatively small integer numbers, you can gain an enormous speed advantage by using the data type Integer instead of Variant. You can use Variant by default, but Variant will not necessarily assume that it is an integer being used. It could assume that it is a floating point number, in which case calculations will take far longer, although the same result will be produced.

There are also memory considerations to take into account. Each Double number takes up 8 bytes of memory, which may not seem like a lot. However, across a large array it can use a large amount of RAM, which would slow the process down. This will use up memory in the computer, which Windows could be using as virtual memory for its graphical display.