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 numerous number of formats in which a user can provide us with values, such as user can provide a decimal value and a non decimal value, CDEC function in VBA helps us to convert any value to a decimal value and it is a inbuilt 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. 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 “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.
For an example look at the below VBA 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 we will get the value in the message box in VBA as below.
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.
- 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 3 because of the moment Integer data type assigned it always round 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 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.
One more specialty of this decimal data type is we can store more than 14 digit decimal values, unlike 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
Now run the code and see what we get.
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 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 VBA CDEC function along with examples. You can learn more about VBA functions from the following articles –