Currency conversion in Excel VBA

Objective:

Create a function that converts an amount from one currency to another (e.g., from Euro to USD).

1. Add a VBA Module

Open Excel and press Alt + F11 to open the VBA editor.

Go to Insert > Module to create a new module.

Copy and paste the code below into this module.

VBA Code for Currency Conversion:

Option Explicit
' Declare global variables for exchange rates
Dim rateEuroUSD As Double
Dim rateEuroGBP As Double
Dim rateEuroJPY As Double
Sub ConvertCurrency()
    ' Initialize the exchange rates (example values)
    rateEuroUSD = 1.1   ' Example: 1 EUR = 1.1 USD
    rateEuroGBP = 0.85  ' Example: 1 EUR = 0.85 GBP
    rateEuroJPY = 150   ' Example: 1 EUR = 150 JPY   
    ' Local variables for currencies and amounts
    Dim amount As Double
    Dim sourceCurrency As String
    Dim targetCurrency As String
    Dim result As Double
    ' Prompt user to enter the source currency, target currency, and amount
    sourceCurrency = InputBox("Enter the source currency (EUR, USD, GBP, JPY):")
    targetCurrency = InputBox("Enter the target currency (EUR, USD, GBP, JPY):")
    amount = InputBox("Enter the amount to convert:")
    ' Perform conversion based on selected currencies
    If sourceCurrency = "EUR" Then
        If targetCurrency = "USD" Then
            result = amount * rateEuroUSD
            MsgBox amount & " EUR = " & result & " USD"
        ElseIf targetCurrency = "GBP" Then
            result = amount * rateEuroGBP
            MsgBox amount & " EUR = " & result & " GBP"
        ElseIf targetCurrency = "JPY" Then
            result = amount * rateEuroJPY
            MsgBox amount & " EUR = " & result & " JPY"
        Else
            MsgBox "Target currency not recognized"
        End If
    ElseIf sourceCurrency = "USD" Then
        If targetCurrency = "EUR" Then
            result = amount / rateEuroUSD
            MsgBox amount & " USD = " & result & " EUR"
        ElseIf targetCurrency = "GBP" Then
            result = (amount / rateEuroUSD) * rateEuroGBP
            MsgBox amount & " USD = " & result & " GBP"
        ElseIf targetCurrency = "JPY" Then
            result = (amount / rateEuroUSD) * rateEuroJPY
            MsgBox amount & " USD = " & result & " JPY"
        Else
            MsgBox "Target currency not recognized"
        End If
    Else
        MsgBox "Source currency not recognized"
    End If
End Sub

Explanation of the Code:

Global Variables:

• rateEuroUSD, rateEuroGBP, rateEuroJPY: These are the exchange rates you define for each currency against the Euro. For example, 1 EUR = 1.1 USD, 1 EUR = 0.85 GBP, and 1 EUR = 150 JPY.

ConvertCurrency Function:

• User Inputs: The three InputBox prompts ask the user to enter:
  • The source currency (e.g., EUR, USD).
  • The target currency (e.g., EUR, USD).
  • The amount to convert.
• Conversion Conditions:
  • The code checks the source currency (EUR, USD, etc.) and the target currency selected.
  • Then, it applies the appropriate exchange rate to perform the conversion by multiplying the amount of the source currency by the exchange rate.
  • A MsgBox displays the result of the conversion.

Running the Code:

1. You can run this code by pressing F5 in the VBA editor or by linking it to a button on your Excel sheet.
2. The program will ask the user for the currencies and the amount to convert, then display the result in a message box.

Example of Usage:

If you enter the following values:

• Source Currency: EUR
• Target Currency: USD
• Amount: 100

The message box displayed will be:

100 EUR = 110 USD

This is based on the conversion rate 1 EUR = 1.1 USD.

Extending with Dynamic Exchange Rates:

You can extend this further by fetching real-time exchange rates via an API like Fixer.io or OpenExchangeRates. For this, you’ll need to make HTTP requests in VBA to get the live rates and modify the code accordingly.