VBA CDBL Function

Excel VBA CDBL Function

VBA CDBL is an inbuilt data type conversion function and 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 which is the value of the variable itself.

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

• Expression is the value that we are trying to convert to Double data type.

Any floating number which is stored as other than double data type can be converted by applying the CDBL function.

Point to Remember Here: Only numerical values can be converted to Double data type. Anything other than numerical value cannot be converted to double type, so end up showing “Type Mismatch Error in VBA” like the below.

Have you ever used a double data type in VBA coding?

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

For an example look at the below VBA code.

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

Next, I have assigned the value as k = 25.4561248694615

When I 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.

To show the result as it is we need to change the variable type from Integer to Double.

This should give us the exact number as we assigned to the variable.

Ok, keeping this in mind we can also convert all the fraction numbers which are stores as a non-double data type.

Examples to Use VBA CDBL Function

Example #1

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

Code:

Sub Double_Example1()

    Dim k As String

    k = 48.14869569

    MsgBox k

End Sub

Now I will run the code and see what happens.

Even though the VBA variable type is “String” it is still showing the decimal values. This is because String can be of any kind of data type so decimal or floating numbers are showing as it is.

Now I will change the data type from string to Integer.

Code:

Sub Double_Example1()

    Dim k As Integer

    k = 48.14869569

    MsgBox k

End Sub

Now I will run the code and see what happens.

This is where the CDBL function plays a vital role to convert the integer data type to double. So 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

This will convert the string data type value to Double.

Example #2

Now let’s convert the number 854.6947 which is stored as Variant to 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 I have declared as “Variant”. Dim VaraintNumber

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

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

Now by using the second variable, we have applied CDBL function to convert the Variant value to Double data type.

DoubleNumber = CDbl(VaraintNumber)

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

Now I will run the code to see the result.

Things to Remember

• A double data type can accept only numerical numbers.
• If text value is supplied it will cause an error of Type Mismatch.
• A double data type can display only 13 digits of floating numbers.

Recommended Articles

This has been a guide to VBA CDBL. Here we learn how to use VBA CDBL function to convert the value to double data type along with some simple to advanced examples. Below are some useful excel articles related to VBA –

• How to Declare VBA Global Variables?
• VBA Data Type
• Transpose in VBA