Excel VBA CDBL Function

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 VBAType Mismatch Error In VBAWhen we assign a value to a variable that is not of its data type, we get Type mismatch Error or Error code 13. For example, if we assign a decimal or long value to an integer data type variable, we will get this error (Error Code 13) when we run the code.read more” 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 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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

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 roundVBA RoundRound function in VBA is a mathematical function that rounds up or down the given number to the specific set of decimal places specified by the user to ease calculation.read more 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.

Examples to Use VBA CDBL Function

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 IntegerVba Data Type From String To IntegerThe “CINT” function in VBA can be used to convert string data type variables to integer data types. It is a built-in function known as the data type conversion function that can be used as macro code.read more.

Sub Double_Example1()

Dim k As Integer

k = 48.14869569

MsgBox k

End Sub

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.

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.

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.

Things to Remember

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

This article has been a guide to VBA CDBL. Here, we learn how to use the 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 TypeTranspose in VBAVBA Switch Function