CDEC Function in VBA
CDEC function in VBA is an inbuilt function of conversion, which usually known as “Convert to Decimal’. Now as we know, its full name which clearly states its work which is used to convert a number into decimal. It does not directly convert an integer number into a decimal, but it allows us to keep the decimal digits up to any number of digits. If you see the syntax of VBA CDEC, it just requires the expression to be selected as shown below.
Where, Expression = Decimal number which we need to convert or retain in decimal format.
For this, we have other data types as well such as Double, Long or Integers which are used for numbers. But there are some constraints using all the mentioned data type.
Integer data type only considers whole numbers and that is also between -32,768 to 32,767, Long data type allows 4 Byte of data into which is between -2,147,483,648 to 2,147,483,648.
And Double data type allow 8 Byte of data which is from -1.79769313486232e+308 to 1.79769313486232e+308. Whereas in CDEC function, we can store any type of decimal number and perform any type of mathematical operations in which decimals are involved but it Variant and Double data types.
How to Use the CDEC Function in Excel VBA?
We will learn how to use a CBEC Function in Excel by using the VBA Code. For this, follow the below steps:
Example #1
First, we will see a simple VBA Code where we will try to print a decimal value using the Integer data type.
Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.
Step 2: Write the subprocedure preferably in the name of performed operations.
Code:
Sub VBA_CDEC() End Sub
Step 3: Define a variable using DIM as Integer.
Code:
Sub VBA_CDEC() Dim A As Integer End Sub
Step 4: Now assign any decimal number of any length in the defined variable.
Code:
Sub VBA_CDEC() Dim A As Integer A = 1.234567 End Sub
Step 5: And to print the value stored in variable A we can use MsgBox or Debug Print. Here we are using Msgbox.
Code:
Sub VBA_CDEC() Dim A As Integer A = 1.234567 MsgBox A End Sub
Step 6: Now compile the code by pressing function key F8 and to run the code, click on the Play button located below the menu bar. We will get a message box with the number as integer (Whole number) as shown below.
Step 7: Now as we understood the Integer data type will not convert or retain any decimal numbers. Now let try to change the data from Integer to Variant.
Code:
Sub VBA_CDEC() Dim A As Variant A = 1.234567 MsgBox A End Sub
Step 8: Now if we run the code, we would probably get the mentioned decimal values as 1.234567.
Step 9: Now if we cover the earlier used decimal value in CDEC function. And see what we get.
Code:
Sub VBA_CDEC() Dim A As Variant A = 1.234567 MsgBox A End Sub
Step 10: Now again, we run the code by pressing function key F5, and to run the code, click on the Play button. we would get the same decimal value as CDEC is meant for decimal values only.
Step 11: Now let test the limit of CDEC function by keeping the 10 digit decimal numbers as 1.2345672347.
Code:
Sub VBA_CDEC() Dim A As Variant A = CDec(1.2345672347) MsgBox A End Sub
Step 12: Now run this code by pressing function key F8. We will see the message with a complete 10 decimal digit number.
Step 13: As we know the Double data type can only uphold the decimal number up to 14 digits. Let’s test CDEC function with a number of decimal values up to 18 digits which is 1.234567234723456723.
Code:
Sub VBA_CDEC() Dim A As Variant A = CDec(1.234567234723456723) MsgBox A End Sub
Step 14: We will see in the message box that CDEC only returned the decimal value up to 14 digits which is same as DOUBLE data type.
Step 15: To keep the limit of decimal values which we entered up to 18 digits, let try to cover the value in inverted commas and increasing the decimal digits up to 25.
Code:
Sub VBA_CDEC() Dim A As Variant A = CDec("1.23456723478923234567234789") MsgBox A End Sub
Step 16: And if we see the output in the message box, then we will notice that it has the same value which we fed in above VBA Code.
Example #2
In this example, we will perform a simple mathematical operation like multiplication using the same type of VBA Code.
Step 1: For this again open a new module and using DIM define a variable considering DOUBLE data type.
Code:
Sub VBA_CDEC2() Dim A As Double End Sub
Step 2: Now let try to multiply some decimal numbers as shown below. There is no restriction to use any type of value in it.
Code:
Sub VBA_CDEC2() Dim A As Double A = CDec(1.0232 * 1.00044 * 0.3333338979) End Sub
Step 3: And to see the output, we will use msgbox.
Code:
Sub VBA_CDEC2() Dim A As Double A = CDec(1.0232 * 1.00044 * 0.3333338979) MsgBox A End Sub
Step 4: And if we run the code by pressing function key F5 and to run the code, click on the Play button located below the menu bar, we will get the output.
We can try to test this code, using the different types of data types. But using CDEC with Variant data type gives the decimal value up to 28 digits.
Pros of VBA CDEC:
- CDEC is also another type of simplest function to be used for decimal values.
- It allows us to see the decimal values up to any length if used in inverted commas.
Things to Remember
- CDEC stands for “Convert to Decimal” which only accepts Variant data types if we want to see the decimal numbers up to 28 digits.
- CDEC rounds off the number of not used in inverted commas up to 14 digits.
- Generally, CDEC is not used but it could be used mainly into statistics where huge precision needs to be taken for any kind of calculation.
- We can convert a number to into decimal using CDEC function easily whereas we all know there is no other data type available to get such lengthy decimal values.
Recommended Articles
This is a guide to the VBA CDEC. Here we discuss how to convert an integer number into a decimal using CDEC function in excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –
