Programming Excel with VBA and .NET

Conversions

Visual Basic automatically converts between data types during assignment. If one variable doesn’t exactly match the type of another, Basic changes the value to fit. You can see this easily if you perform the following assignments:

    Sub Conversions( )
        Dim d As Double, s As Single, i As Integer
        d = WorksheetFunction.Pi
        s = d
        i = d
        Debug.Print d, s, i
    End Sub

The preceding code displays the following output in the Immediate window:

    3.14159265358979            3.141593      3

Tip

You need to be aware of automatic conversion , because it can result in the unintended loss of precision.

Here the conversion is done by rounding the number up or down to reflect the precision of the variable receiving the assignment. Not all conversion can be done by rounding. For example, the following lines convert pi to a string:

    Dim str As String
    str = WorksheetFunction.Pi

Warning: not all conversions succeed. The following line causes a type mismatch error because d is a numeric variable and the "Pi" can’t be converted to a number:

    d = "Pi"

Conversions may also fail if the assignment exceeds the limit of the target variable. For example, the following lines result in an overflow error since the positive limit for Integers is 32,627:

    Dim l As Long
    l = 32768
    i = l

You can explicitly perform any of these automatic conversions using the Visual Basic conversion functions listed in Table 2-4.

Table 2-4. Visual Basic type conversion functions

CBool	CByte	CCur	CDate	CDbl
CDec	CInt	CLng	CSng	CStr
CVar

It would ...