Data Type Differences

In VBA an integer is 16 bits, which is equivalent to a Short in Visual Basic 2005. A Long is 32 bits in VBA, which is equivalent to an Integer in Visual Basic 2005.

The Currency data type is not supported in Visual Basic 2005. Instead, you can use the Decimal data type. Decimal is more accurate than the Currency data type. Decimal is a 128-bit fixed-point data type that can hold up to 28 digits to the right of the decimal point. You can force the data type of a literal value to Decimal by adding a D to the end of the literal value. For example, 23445937D is forced to be interpreted as a Decimal. Without the D, the literal value is interpreted as a Long.

The Variant data type is not supported in Visual Basic 2005. When you copy VBA code into the Visual Basic 2005 Code Editor, if that code either declares a variable as a Variant or does not specify the data type, it is automatically converted to the Object type. Instead of declaring a variable as an Object, you should specify its data type to avoid late binding of the object. Late binding means that the type of the object is not known until run time. Early binding gives you the added bonus of having access to all the IntelliSense features of Visual Studio at design time.

A Date in Visual Basic 2005 maps to a System.DateTime type in the .NET Framework. In VBA, the Date data type is stored as a floating-point number. To convert between a Double and a Date data type in Visual Basic 2005, you should use the Date's ToOADate and FromOADate methods. Listing 4.9 demonstrates the conversion of a date stored in an Excel range to a Date. The example assumes that cell A1 contains a valid date.

Listing 4.9. Using the FromOADate method

Sub GetDate()

Dim myDate As Date = Date.FromOADate( _

Globals.Sheet1.Range("A1").Value2 MsgBox(CStr(myDate))

End Sub

When the GetDate method runs, the message box displays the date in cell A1. If you rewrite the code to declare myDate as a Double and assign it the value of cell A1, the message box displays the value as a double rather than in date format.

Table 4.2 shows the mapping between data types in Visual Basic 2005, VBA, and the .NET Framework.

Table 4.2. Data Types in VBA, Visual Basic 2005, and the .NET Framework


Visual Basic 2005

.NET Framework













Date (stored as double)



0 0

Post a comment