Currency Symbols - Excel 2000

[ChrisGreaves]

I have been asked to implement "currency symbols" (no need for exchange rates) in a set of workbooks and have stumbled across a few curios. (Think USA/Canada/UK/India)
My first thought was to see how to implement my Currency styles with run-time variable symbols.
I recorded a couple of macros to modify my user-style Currency0:

Code: Select all

    ActiveWorkbook.Styles("Currency0").NumberFormat = "[$£-809]#,##0"

and

Code: Select all

    ActiveWorkbook.Styles("Currency0").NumberFormat = "[$Rp-421]#,##0"

For UK Pounds Sterling and for Indonesian Rupees respectively.

Please,
(1) Can anyone explain to me the mysterious offset values of -809 and -421?
(2) Can anyone explain to me why I don't see Indian Rupees in the (Format, Style, Modify, Format, Number) Symbol drop-down list? (I am in Excel 2000).

FWIW using

Code: Select all

    ActiveWorkbook.Styles("Currency0").NumberFormat = "[$GPC-421]#,##0"

renders a comprehensible GreavesPussyCat currency string, as does

Code: Select all

    ActiveWorkbook.Styles("Currency0").NumberFormat = "[$GPC-809]#,##0"

FWIW

Code: Select all

    ActiveWorkbook.Styles("Currency0").NumberFormat = "[$§]#,##0"

renders what I want, too

(a) the offset-thingy seems irrelevant (I tried removing the "-809" and the "-421")
(b) the $ symbol seems essential - some sort of escape character
(c) for practical purposes, any string/symbol can be implemented after the $ symbol.

[HansV]

(1) The numbers are the hexadecimal "locale id" codes (LCID) used by Windows. See for example Locale IDs Assigned by Microsoft.

(2) India has many languages, so there are several LCIDs for India: Gujarati, Hindi, Kannada, Konkana, Marathi, Punjabi, Sanskrit, Tamil and Telugu.

By the way, India uses a number format that Excel can't render:

1
10
100
1,000
10,000
1,00,000
10,00,000
1,00,00,000
10,00,00,000

[ChrisGreaves]

By the way, India uses a number format that Excel can't render:10,00,00,000

Right.
Thanks Hans. That makes three spanners in the works since 9am this morning (grin!)

"That is we use hundreds, thousands, lakhs and crores rather than millions and billions"
(And here I am wading through my 12 volumes of Rudyard Kipling).

Since my first post I have realized that by allowing run-time symbol replacement I could very well shoot myself in the foot.
I saw three basic formats:
· [$<symbol>]
· (<symbol>{ #0})
· <symbol>{ #0}
And thought - it's all very well to replace the $ with, say a Rp, but then I have to know to look for a Rp and replace that with a GPC, and so on. That is, I'd have to know the history of symbols.
Aaaaaargh.
After mulling over this for three seconds I elected to re-define all my "Currency*" styles with the [...]convention, so that string analysis is much simpler.
I'll have to worry about the separate issue of the number string.
Some URLs:
http://www.vbforums.com/showthread.php?t=454802
http://www.dq.winsila.com/tips-tricks/a ... words.html
http://www.ozgrid.com/forum/showthread.php?t=10226

[HansV]

And also see the deftly named thread Add-in (Excel 2003) in the Windows Secrets Lounge.

[ChrisGreaves]

I have been asked to implement "currency symbols"

Here is (attached) a little demo.
Drag the TEST procedure outside the function, enable it, then run it one-call at a time.
Pay me any way you like.
(later)
"You're welcome!"

[HansV]

I still have some Italian lire lying around, I think.

[ChrisGreaves]

1.JPG

[HansV]

Grazie!

[ChrisGreaves]

And also see the deftly named thread

Thanks for this, too. INR.ZIP is a nifty little thing, Deft, one might say ....