VBA Format cells(values) with correct accounting symbol

ElPabloSS

New Member
Joined
Sep 12, 2018
Messages
10
Hi,

I'm struggling with formatting values using VBA by using the standard accounting symbols.

My plan is to take the information from a report that provides me with values and currency information in two separate columns. The currency is a three-letter code like USD, EUR, AUD etc. and I would use that to translate it to the proper accounting symbol.

My idea would be to set the accounting format using VBA (as if I would do it manually over "format cells - accounting - symbol") but I have only found complex and individual formatting code that I would need to apply and pre-define. As I don't know which currencies the report includes, an automated mapping from the three-letter code to the accounting symbol would be perfect but I fear that's not available.
Is there any property available to set the accounting symbol for the different currencies?

Hope you understand my problem and can help :)

Thanks
 

areed1192

New Member
Joined
Dec 9, 2018
Messages
25
Unfortunately, there is no built-in mapping that exist in VBA, in other words, I can't pass through a currency code and it will output the correct format. However, we could write a simple case statement that would probably do the job for you. However, this would require some upfront time as you would have to do it for all of the different currency codes.

My recommendation is to just have a list of all your currency types, turn on the macro recorder and change them one by one. Once you're done go into the code and take the pieces you need.

Now, I gave you a framework you can leverage. Basically, all this code does is looks at column "B" for the currency code and then based on what it finds, takes the value in column "A", applies a formatting to it and puts the new value in column C.

Code:
Sub CurrencySymbol()
    
    Dim CurrName As String
    
    Range("B1").Select


Do While ActiveCell.Value <> Empty


    CurrName = ActiveCell.Value
    
    Select Case CurrName
    
        'USD Case
        Case Is = "USD"
        
             With ActiveCell.Offset(0, 1)
                .Value = ActiveCell.Offset(0, -1).Value
                .NumberFormat = "_([$$-en-US]* #,##0.00_);_([$$-en-US]* (#,##0.00);_([$$-en-US]* ""-""??_);_(@_)"
             End With
        
        'EUR Case
        Case Is = "EUR"
        
             With ActiveCell.Offset(0, 1)
                .Value = ActiveCell.Offset(0, -1).Value
                .NumberFormat = "_([$€-x-euro2]* #,##0.00_);_([$€-x-euro2]* (#,##0.00);_([$€-x-euro2]* ""-""??_);_(@_)"
             End With
        
        'CYN Case
        Case Is = "CYN"
        
            With ActiveCell.Offset(0, 1)
                .Value = ActiveCell.Offset(0, -1).Value
                .NumberFormat = "_([$¥-zh-CN]* #,##0.00_);_([$¥-zh-CN]* (#,##0.00);_([$¥-zh-CN]* ""-""??_);_(@_)"
             End With
    
    End Select


    ActiveCell.Offset(1, 0).Select


Loop


End Sub
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

If I can recall correctly ... you will have to be careful with the Windows Regional settings ... which will happily interfere with your macro ...:wink:
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
I do not do international programming, so the following is only a guess as I have no way to test it. VB provides a FormatCurrency function which will automatically format a number using the currency symbol defined in the system control panel for the computer running the code. Using my American locale system, if I execute this line of code...

CurrencySymbol = FormatCurrency(0, 0, vbFalse)

a lone $ sign (my regional currency symbol) is assigned to the variable. Perhaps doing that in other locales will assign their lone currency symbol for that locale to the variable.
 
Last edited:

ElPabloSS

New Member
Joined
Sep 12, 2018
Messages
10
Hi,
thanks all for the information. @areed: thanks for the code! I will try it and let you know @James: you're right with the regional setting and my workbook should be used in several European countries so this could lead to problems.
@Rick: yes, The FormatCurrency function is unfortunately only providing the local currency symbol. If this function could be used to determine the actual currency you would like to use it would be great... but I fear that's not the case :(

 

Forum statistics

Threads
1,082,587
Messages
5,366,486
Members
400,894
Latest member
frog9000

Some videos you may like

This Week's Hot Topics

Top