VBA CDBL Function

VBA CDBL is an inbuilt data type conversion function. The use of this function is that it converts the data type of any given variable's value into a Double data type. This function only takes a single argument: the variable's value.

In VBA, "CDBL" stands for "Convert to Double." This function converts the given number to a Double data type. Take a look at the syntax of the CDBL function.

• The expression is the value we are trying to convert to a Double data type.

We can convert any floating number stored as other than the Double data type by applying the CDBL function.

Point to Remember Here: We can convert only numerical values to Double data type. So, we cannot convert anything other than numerical value to double type, so we show “Type Mismatch Error in VBA” like the below.

If not, it is worth having a look at it now. "Double" is the data type used to store the decimal position of the number. We can have up to 13 floating decimal numbers.

For example, look at the below VBA code.

In the above, we have defined the variable (k) type as "Integer." Dim k As Integer

Next, we have assigned the value as k = 25.4561248694615.

When we run the code, we will get the result as follows.

We got the result as 25 since we have defined the variable as Integer VBA round to the nearest integer value.

We need to change the variable type from Integer to Double to show the result.

It should give us the exact number we assigned to the variable.

Keeping this in mind, we can also convert all the fraction numbers stored as a non-double data type.

Example #1

To start the proceedings, let’s look at the code below.

Code:

Sub Double_Example1()

    Dim k As String

    k = 48.14869569

    MsgBox k

End Sub

Now, we will run the code and see what happens.

Even though the VBA variable type is "String," it still shows the decimal values because String can be of any data type, decimal or floating numbers shown as it is.

Now, we will change the vba data type from string to Integer.

Code:

Sub Double_Example1()

    Dim k As Integer

    k = 48.14869569

    MsgBox k

End Sub

Now, we will run the code and see what happens.

It is where the CDBL function plays a vital role in converting the Integer data type to Double. So, the below code is the same for you.

Code:

Sub Double_Example1()

    Dim IntegerNumber As String
    Dim DoubleNumber As Double

    IntegerNumber = 48.14869569

    DoubleNumber = CDbl(IntegerNumber)

    MsgBox DoubleNumber

End Sub

It will convert the String data type value to Double.

Example #2

Now, let's convert the number 854.6947, stored as a Variant, to a Double data type.

Code:

Sub Double_Example2()

    Dim VaraintNumber
    Dim DoubleNumber As Double

    VaraintNumber = 854.6947

    DoubleNumber = CDbl(VaraintNumber)

    MsgBox DoubleNumber

End Sub

The first variable we have declared as "Variant." Dim VaraintNumber.

Note: When the variable type is not declared, it becomes a universal data type Variant.

Next, we have declared one more variable, i.e., Dim DoubleNumber As Double.

For the first variable, VaraintNumber, we have assigned the value as 854.6947.

Using the second variable, we have applied the CDBL function to convert the Variant value to a Double data type.

DoubleNumber = CDbl(VaraintNumber)

The final part is to show the result in the message box. MsgBox DoubleNumber

Now, we will run the code to see the result.

• A Double data type can accept only numerical numbers.
• If we supply the text value, it will cause an error of "Type Mismatch."
• A Double data type can display only 13 digits of floating numbers.