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.
- The 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 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.

4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
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 vba 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 in converting 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
Note: When the variable type is not declared, it becomes a universal data type Variant.
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 the 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 –
- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion