3

There is a project requirement where we need to check a cell for its currency type.

I came across a similar question in this forum (How to check if cell is formatted as Currency?). It explains how to do that for the $ symbol. But I couldn't replace the $ symbol with other currency symbols (for example Indian Rupee, Euro etc.) in Visual Basic Editor. To have other symbols in VBE I have tried the following:

  • Inserted the symbol in Excel using Insert->Symbol option.
  • Copy pasted the symbol in VBE.

But it is pasted as ? in VBE.

Please help me to have currency symbols in VBE. If the above is not possible please suggest a way to identify the currency type of a cell.

user202729
  • 3,358
  • 3
  • 25
  • 36
Kannan
  • 31
  • 1
  • 3
  • Related: the method in the answer below cannot be used in const. See [excel - Unicode string literals in VBA - Stack Overflow](https://stackoverflow.com/questions/23678033/unicode-string-literals-in-vba) ___________________________________________________________________________________________ Some other things (MsgBox, the Immediate window -- Debug.Print) doesn't support Unicode -- see https://stackoverflow.com/questions/55210315/ and https://stackoverflow.com/questions/23810324/ – user202729 Sep 28 '21 at 15:18
  • [This is a tool that implements the answer below. Paste the string in the "input" box then press ctrl+enter.](https://tio.run/##bZBPSsQwGMXXk1OEIDbBmUCnMIth6kaQOYCiIFLSzldbbZOQpjBF3HgKd57BP@foWbxBTaoyMOMmkJfvve/3ojtbKBkNg4hLqVtLGUJlrZWxuLRgrFJV8ycIY0SHNpDjskkMaAMNSCvSCuh2iUtp2ew0dYYlmhiwrZE4mq/i7SoO5wuEMuVSpDM08c0tQpcX5@EiydQGtCq9OKbz8aRkTdjBBM@NqtPOQkMFB@kfaNDafBYuKgiYA8@VOUBLpvjOqFY7vl0jPkppR/d3TPEDdPF@BnONysPWiZMnu1pcaA1yQwMS4BNMCL93kbQWmmaF@aVgjLuISmTgx6Y4ICRgbtpdGJpA1YDP9D2ywhOPJi/9s4ecFebqiB6vH5fXT4xw56qFdcv8V2jj6lDiQX44dn73PAz9W/@Ow3mESf/Rf@Kv15fnbw) – user202729 Oct 12 '21 at 06:23
  • Note that you need `ChrW` the two surrogates separately for non-BMP characters, see [excel - Get Unicode characters with charcode values greater hex `FFFF` - Stack Overflow](https://stackoverflow.com/q/56008191/5267751) – user202729 Oct 12 '21 at 06:26

2 Answers2

5

VBA editor is not Unicode.

In order to have Unicode characters in it, you need to use ChrW$, e.g.

Dim Rupee As String
Rupee = ChrW$(&h20A8&)

Also see Unicode string literals in VBA.

Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Can you explain why this does not appear to be a universal answer to inputting Unicode characters? On my machine this works fine with the Rs symbol, but then I tried 23D1 "metrical breve" from the "Miscellaneous technical" block, and Word (NB Word 2007) produces a stupid "can't represent this" box instead. – mike rodent May 30 '21 at 08:29
  • @mikerodent Do you have a font that has this character? – GSerg May 30 '21 at 09:53
  • Ah, got it now... yes, a font such as "MS Mincho" is capable of coming up with the right character. So it appears to be a question of what fonts are and are not capable of... – mike rodent May 30 '21 at 10:49
-1

I have an improved version for the answer... use the following code for making the selection in to Indian rupee Symbol with Lakhs crores format, immaterial of your regional settings in Millions format. 

Dim rs As String
rs = ChrW$(&H20B9&)
Selection.NumberFormat = "[>=10000000][$" & rs & "-ta-IN] ##\,##\,##\,##0.00;[>=100000][$" & rs & "-ta-IN] ##\,##\,##0.00;[$" & rs & "-ta-IN] ##,##0.00"

Hope this helps.

Satheesh K
  • 108
  • 9
  • While this code per se may be useful for someone, it has nothing to do with the question. – GSerg Feb 07 '22 at 11:15