VBA CDEC

CDEC is a built-in data type conversion function available as a 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 data and ask users to input the values, there can be several formats in which a user can provide us with values, such as a user can provide a decimal value and a non-decimal value. The CDEC function in VBA helps us convert any value to a decimal value, a built-in function in Excel.

Below is the syntax of the CDEC function.

Expression: This is nothing but the value that we are trying to convert to the decimal data type. It 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 are “Long, Integer, and Double.” There is no special data type as “Decimal” available to hold long decimal values. Long and 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.
• Regarding memory occupation, CDEC will consume 14 bytes of the system memory. However, since we declare the variable data type as “Variant” upfront, the value will always be a zero.

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

Look at the VBA code below.

Code:

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.

When assigned the value of 2.5698979797646, the results are 3 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, it will round up to the next integer value. If the decimal value is <0.51, it will round down to below integer value.

• Let us assign the CDEC function to convert the data type value to Decimal.
• As we can see above, we 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 get the result as 3 because of the moment the Integer data type is assigned. It always rounds up the number to the next integer value.

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

Code:

Sub CDEC_Example()

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

End Sub

Now, execute the code and see what we get.

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

Now, we will enter more than 14 digits to the variable in double quotes.

Code:

Sub CDEC_Example()

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

End Sub

Note: Enter the number with double quotes because without them, after the 14 digits, it will keep rounding off to enter numbers in double quotes.

Now, run the code and see what we get.

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

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