VBA Format cells(values) with correct accounting symbol

[ElPabloSS]

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]

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.

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]

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]

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.

 

[ElPabloSS]

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