Macro to format Currency in Armenian DRAM

[kevanr]

Hi

I have a multi-currency model that allows the user to format using a selection from a number of different currencies e.g. USD, GBP, EUR etc with their relevant currency symbols.

An example of the code within the macro is as follows:

' For USD:
Selection.NumberFormat = _
"_-[$$-en-US]* #,##0_ ;[Red]_-[$$-en-US]* -#,##0 ;_-[$$-en-US]* ""-""??_ ;_-@_ "

' For EUR:
Selection.NumberFormat = _
"_-[$€-x-euro2] * #,##0_ ;[Red]_-[$€-x-euro2] * -#,##0 ;_-[$€-x-euro2] * ""-""_ ;_-@_ "

These all run perfectly.

I am attempting to provide a format for the Armenian DRAM. In Excel the default format is [$֏-hy-AM] however when I use it the dram currency symbol "֏" comes up as a "?" in the format. In other words the code gets changed to [$?-hy-AM]

Can anybody help me solve this problem.

Many thanks

 

[Macropod]

Works fine for me when I simply paste the dram currency symbol (֏) from your post into Excel's number formatting pane.

 

[kevanr]

Works fine for me when I simply paste the dram currency symbol (֏) from your post into Excel's number formatting pane.

Thanks Paul, I agree that works when you manually post it.
I'm referring to when I run a macro as my model is used across the world and I do not want users to have to do anything manually, they must simply run the macro and VBA must sort it.
However I'm getting the problem of my VBA code in my VBA editor changing the code to include the "?" and not the "֏" as described in my post above.

 

[Macropod]

For VBA, you can insert the character via ChrW, using its Unicode Hex value:

Selection.NumberFormat = _
  "_-[$" & ChrW(&H58F) & "-hy-AM] * #,##0_ ;[Red]_-[$" & ChrW(&H58F) & "-hy-AM] * -#,##0 ;_-[$" & ChrW(&H58F) & "-hy-AM] * ""-""_ ;_-@_ "

 

[kevanr]

For VBA, you can insert the character via ChrW, using its Unicode Hex value:

Selection.NumberFormat = _
  "_-[$" & ChrW(&H58F) & "-hy-AM] * #,##0_ ;[Red]_-[$" & ChrW(&H58F) & "-hy-AM] * -#,##0 ;_-[$" & ChrW(&H58F) & "-hy-AM] * ""-""_ ;_-@_ "

Thanks Paul - that worked perfectly, much appreciated !