REAL-TIME

VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

120+ PROFESSIONAL

Project Management Templates

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

Share Post

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.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA Data TypesLast Updated: September 23, 2023

Leave A Comment