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 ‘Currency’ data type in VBA helps to deal with Currency data. It is commonly used to store monetary values in the form of decimal numbers. In this blog post, we will explore the ‘Currency’ data type in VBA, its syntax, storage, range, and provide examples of how it can be used in different VBA codes.

VBA data type ‘Currency’

Syntax

The ‘Currency’ data type in VBA is declared using the ‘Currency’ keyword. Its syntax is as follows:

Dim variable_name As Currency 

Here, ‘variable_name’ is the name of the variable to be declared. The ‘As’ keyword is used to specify the data type, in this case, ‘Currency’.

Storage

The ‘Currency’ data type in VBA is stored as a 64-bit (8-byte) signed integer, similar to the ‘Long’ data type. However, the ‘Currency’ data type can hold values with a higher precision, up to 4 decimal places, making it suitable for storing monetary values.

Range

The range of values that can be stored in a ‘Currency’ data type is between -922,337,203,685,477.5808 and +922,337,203,685,477.5807. This range is sufficient for storing most monetary values and avoids any rounding or approximation errors that may occur with other data types such as ‘Double’ or ‘Single’.

Examples of VBA data type ‘Currency’

Now, let’s look at some examples of how the ‘Currency’ data type can be used in VBA codes.

Example 1: Simple Currency Calculation

Dim totalPrice As Currency
Dim itemPrice As Currency
Dim quantity As Integer

itemPrice = 10.50
quantity = 5
totalPrice = itemPrice * quantity
Debug.Print "Total Price: $" & totalPrice

In this example, we declare three variables: ‘totalPrice’ of type ‘Currency’, ‘itemPrice’ of type ‘Currency’, and ‘quantity’ of type ‘Integer’. We then assign a value of 10.50 to ‘itemPrice’ and 5 to ‘quantity’. Finally, we calculate the total price by multiplying ‘itemPrice’ and ‘quantity’ and store the result in ‘totalPrice’. The resulting value is then printed to the Immediate window using the ‘Debug.Print’ statement.

Example 2: Formatting Currency Value

Dim salary As Currency
salary = 5000
Debug.Print "Salary: " & Format(salary, "$#,##0.00")

In this example, we declare a variable ‘salary’ of type ‘Currency’ and assign it a value of 5000. We then use the ‘Format’ function to convert the value into a currency format with two decimal places and a dollar sign. The resulting value is then printed to the Immediate window using the ‘Debug.Print’ statement.

Example 3: Currency Conversion

Dim euroValue As Currency
Dim dollarValue As Currency

euroValue = 100
dollarValue = euroValue * 1.2 'assumes exchange rate of 1 euro = 1.2 dollars
Debug.Print "Equivalent Dollar Value: " & Format(dollarValue, "$#,##0.00")

Here, we declare two variables: ‘euroValue’ and ‘dollarValue’, both of type ‘Currency’. We assign a value of 100 to ‘euroValue’ and then calculate the equivalent dollar value by multiplying it with the exchange rate. The result is then formatted as a currency value and printed to the Immediate window.

Example 4: Currency Comparison

Dim accountBalance As Currency
Dim transactionAmount As Currency

accountBalance = 5000
transactionAmount = 2000

If transactionAmount > accountBalance Then
    Debug.Print "Insufficient funds."
Else
    accountBalance = accountBalance - transactionAmount
    Debug.Print "Remaining Balance: $" & accountBalance
End If

In this example, we declare two variables: ‘accountBalance’ and ‘transactionAmount’, both of type ‘Currency’. We assign a value of 5000 to ‘accountBalance’ and 2000 to ‘transactionAmount’. We then use an ‘If’ statement to compare the two values and print a message if there are insufficient funds. If the transaction amount is less than the account balance, we subtract the transaction amount from the account balance and print the remaining balance to the Immediate window.

Example 5: Currency Conversion Function

Function ConvertToDollars(euroVal As Currency) As Currency
    ConvertToDollars = euroVal * 1.2 'assumes exchange rate of 1 euro = 1.2 dollars
End Function

Dim euroValue As Currency
Dim dollarValue As Currency

euroValue = 100
dollarValue = ConvertToDollars(euroValue)
Debug.Print "Equivalent Dollar Value: $" & dollarValue

In this example, we create a function called ‘ConvertToDollars’ that takes a ‘Currency’ value and converts it to dollars with an assumed exchange rate of 1 euro = 1.2 dollars. We then declare two variables: ‘euroValue’ and ‘dollarValue’, both of type ‘Currency’ and assign a value of 100 to ‘euroValue’. We use the function to convert the euro value to dollars and then print the result to the Immediate window.

Summary

In conclusion, the ‘Currency’ data type in VBA is useful for handling monetary values with precision and avoiding any rounding or approximation errors. It has a wide range of uses, from simple calculations to currency conversions, and can be easily integrated into VBA codes. Understanding the syntax, storage, and range of the ‘Currency’ data type is essential for efficient handling and manipulation of monetary data in VBA. We hope this blog post has provided a comprehensive overview of the ‘Currency’ data type in VBA and its usage through various examples.

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