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, say, 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 it is a string. It will then display 2, which means it is an integer.

Table-1 shows the return values for specific data types.
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 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.

ReturnValue 	Type
0 		Variant
1 		Null
2		Integer
3 		Long
4 		Single
5 		Double
6 		Currency
7 		Date/Time
8 		String
11 		Boolean
17 		Byte

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.