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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
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:
 
Upvote 0
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:
Upvote 0
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 :(

 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top