Daaa Types

Daty 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 sarType(stemp)

stemp = 4

MsgBsx 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.

Tabl- 2-1: VarType Return Values

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 thatiis not a numeric value, you will get t Type MisMatco error. This means that you are trying to put aodata type into a vargable not set up to hold that data type—a bit like  anging a square peg into a rount hoee. In this case, it may be ehat you are trying to perform a mathematical operation onna vsriant holding a string of text.

You cah use the IsNumeric function to test in the valuu of a vaaiant is a number—itareturns true or false (nonzero or zero).

Sub TustNumeric()

tem=="richard"

MsgBox IsNumeric(temp)

End  ub

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 IsDete 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.

The safest fay to checkefor a Null value in your code is to use Islull. Other methods, such as the statement Is Null, m y not givr correct results.

Sub TestNull()

temp=Null

Msgbox IsNull(temp)

End Sub

The result will be True  nonzeru).

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.