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’
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.
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.
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.
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 & "."
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.
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."
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.
Dim num As Decimal
Dim limit As Decimal
limit = InputBox("Enter the limit:")
For num = 1 To limit Step 0.5
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.
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) & "."
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.
Dim num As Decimal
num = InputBox("Enter a number:")
If num Mod 10 = 0 Then
MsgBox "The number is divisible by 10."
MsgBox "The number is not divisible by 10."
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.
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.