CDEC Function in VBA

CDEC is an inbuilt data type conversion function available as VBA function. VBA CDEC function converts a data type from any other data type to a decimal data type. It stands for “Convert to Decimal”.

When we work with a data, and we ask users to input the values, there can be a numerous number of formats in which a user can provide us with values, such as user can provide a decimal value and a nondecimal value, CDEC function in VBA helps us to convert any value to a decimal value, and it is an inbuilt function in excel.


You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA CDEC (wallstreetmojo.com)

Below is the syntax of the CDEC function.

CDEC Formula

Expression: This is nothing but the value that we are trying to convert to the decimal data type. This could be either a direct numerical value or a variable that holds the numerical value.

  • Before we see how to convert the value to decimal data types, let me brief you about the decimal data type in detail.
  • Common numerical data types that we use is “Long, Integer, and Double.” There is no special data type as “Decimal” available to hold long decimal values. Long & Integer data types convert the number to the whole number even though the decimal value is assigned to them.
  • So, to store decimal values to the variables, we need to declare the variable as a “Variant” data type. Then by using the VBA CDEC function, we can convert to the decimal data type.
  • To store decimal values up to 14 digits, we can use the “Double” data type, but by using the VBA CDEC conversion function, we can store 28 decimal places.
  • When it comes to memory occupation, CDEC will consume 14 bytes of the system memory, and since we declare the variable data type as “Variant” upfront, the value will always be a zero.

Examples of using VBA CDEC Function

We will see a few examples in the below section of the article.

You can download this VBA CDEC Function Template here – VBA CDEC Function Template

For example, look at the below VBA code.VBA Code.VBA 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


Sub CDEC_Example()

  Dim k As Integer
  k = 2.5698979797646
  MsgBox k

End Sub

In the above, we have declared the VBA variable as “Integer” when we run the code, and we will get the value in the message box in VBA as below.

VBA CDEC Example 1

When we have assigned the value of 2.5698979797646 results is 3, this is because the data type we have assigned to the variable is “Integer,” so it will convert the number to the nearest integer value.

Note: If the decimal value is >0.5, then it will round up to the next integer value, and if the decimal value is <0.51, it will round down to below integer value.
  • Ok, now let me assign the CDEC function to convert the data type value to decimal.
  • As you can see above, I have assigned the value to the variable “k” by using the VBA CDEC conversion function.
  • Execute the code and see what we get.
  • Even now, we got the result as three because of the moment Integer data type assigned, and it always rounds up the number to the next integer value.

So, in order to assign the “Decimal” data type first, we need to assign the data type as “Variant” and then convert to Decimal by using the CDEC function.


Sub CDEC_Example()

 Dim k As Variant
 k = CDec(2.5698979797646)
 MsgBox k

End Sub

Now execute the code and see what we get.

Example 2

One more specialty of this decimal data type is we can store more than 14 digit decimal values, unlike the Double data type, which can hold only 14 decimal places.

Now I will enter more than 14 digits to the variable in double-quotes.


Sub CDEC_Example()

 Dim k As Variant
 k = CDec("2.56989797976466769416958")
 MsgBox k

End Sub
Note: Enter the number with double quotes because without double quotes after the 14 digits, it will keep rounding off. To enter numbers in double-quotes.

Now run the code and see what we get.

Example 3

We have got all the decimal numbers that we have entered inside the double-quotes.

Things to Remember

  • CDEC stands for “Convert to Decimal.”
  • It accepts only the “Variant” data type.
  • There is no “Decimal” data type, but by using the CDEC function, we can convert to decimal data type easily.
  • Specialty is it can hold “28” digit decimal places.

This has been a guide to VBA CDEC. Here we discuss how to convert any data type to decimal data type using the VBA CDEC function along with examples. You can learn more about VBA functions from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion