The ‘Decimal’ data type is used to store numbers with decimal points. One of the important aspects of VBA coding is knowing and understanding different data types that are available. In this blog post, we will be taking a closer look at the VBA DataType ‘Decimal’.
Exploring the VBA DataType ‘Decimal’
Syntax
The ‘Decimal’ data type is used to store numbers with decimal points, also known as floating-point numbers. In VBA, it is declared using the Dim
keyword followed by the variable name and the keyword As Decimal
. For example:
Dim myNum As Decimal
The Decimal data type can store a larger range of values compared to other numeric data types in VBA, allowing for more precise calculations when working with large or very small numbers.
Storage
The ‘Decimal’ data type uses 14 bytes of memory to store values. This is significantly larger than other numeric data types, such as ‘Integer’ or ‘Long’, which use 2 and 4 bytes respectively. This means that when working with large numbers or performing calculations, the Decimal data type will provide more accurate results as it can hold more digits after the decimal point.
Range
The range of values that can be stored in a ‘Decimal’ data type is -79228162514264337593543950335 to 79228162514264337593543950335. That may seem like a random range, but it is actually based on the number of bytes used for storage (14 bytes). This range is large enough to cover most calculations and is also flexible enough to accommodate for scientific notation.
Example of Decimal DataType in VBA
1. Basic Arithmetic Operations
The following example showcases the use of the ‘Decimal’ data type in performing simple arithmetic operations.
Sub decimalArithmetic() Dim num1 As Decimal Dim num2 As Decimal num1 = 12.543 num2 = 8.245 MsgBox "The sum of " & num1 & " and " & num2 & " is " & num1 + num2 & "." MsgBox "The result of " & num1 & " multiplied by " & num2 & " is " & num1 * num2 & "." MsgBox "The result of " & num1 & " divided by " & num2 & " is " & num1 / num2 & "." End Sub
The output of this code will be:
The sum of 12.543 and 8.245 is 20.788. The result of 12.543 multiplied by 8.245 is 103.325335. The result of 12.543 divided by 8.245 is 1.519803427.
2. Currency Conversion
The ‘Decimal’ data type is especially useful when working with monetary values that require a high degree of accuracy. The following code demonstrates converting a specified amount from US dollars to Euros using the exchange rate.
Sub currencyConversion() Dim dollarAmt As Decimal Dim euroAmt As Decimal Dim exchangeRate As Decimal dollarAmt = InputBox("Enter a dollar amount:") exchangeRate = 0.84 'as of January 2021' euroAmt = dollarAmt * exchangeRate MsgBox dollarAmt & " USD is equivalent to " & euroAmt & " Euros at the current exchange rate." End Sub
The output of this code will prompt the user to enter a dollar amount, convert it to Euros using the exchange rate and display the result in a message box.
3. Using Decimal values in Loops
Loops are often used in VBA to iterate through a set of data and perform actions on each item. The following code uses the ‘Decimal’ data type to generate a series of numbers starting from 1 and increasing by 0.5 up to a specified limit.
Sub decimalLoop() Dim num As Decimal Dim limit As Decimal limit = InputBox("Enter the limit:") For num = 1 To limit Step 0.5 MsgBox num Next num End Sub
The output of this code will display all the numbers from 1 to the specified limit (inclusive) in a message box, with each number incrementing by 0.5.
4. Precision in Financial Calculations
Financial calculations often involve a high degree of precision to avoid rounding errors. In the following example, we will be computing the present value of an investment using the ‘Decimal’ data type.
Sub presentValue() Dim investment As Decimal Dim interestRate As Decimal Dim numOfYears As Decimal investment = 5000 interestRate = 0.065 '6.5%' numOfYears = 10 MsgBox "If you invest " & investment & " for " & numOfYears & " years at an interest rate of " & interestRate * 100 & "%, the present value will be " & investment * (1 + interestRate) ^ (-numOfYears) & "." End Sub
The output of this code will display the present value of the investment which is calculated by dividing the investment by (1 + interest rate) to the power of the number of years.
5. Using Decimal values in Conditional Statements
The ‘Decimal’ data type can also be used in conditional statements to perform different actions depending on the value of the variable. In the following code, we are checking whether a number is divisible by 10 or not.
Sub divisibleBy10() Dim num As Decimal num = InputBox("Enter a number:") If num Mod 10 = 0 Then MsgBox "The number is divisible by 10." Else MsgBox "The number is not divisible by 10." End If End Sub
The output of this code will prompt the user to enter a number and check whether it is divisible by 10 or not, displaying the result in a message box.
Conclusion
The ‘Decimal’ data type may not be used as frequently as other numeric data types in VBA, but it is undoubtedly a valuable tool to be aware of. It provides a larger range of values, higher precision, and is especially useful for financial calculations and working with large or small numbers. With the help of the five example codes, we hope you have a better understanding of the ‘Decimal’ data type and its capabilities in VBA coding.