unicode characters in VBA editor

[dannnv]

I am using old version of Excel, 97. This may be solved in later version but would like to know if it can be solved in Excel 97. I am making an xls to xml convertor. The first thing I need to do is replace characters with the unicode equivalent so that they can be properly displayed in xml. I am using this code repeatedly in my VBA macro for each character I expect to be a problem

Cells.Replace What:="¢", Replacement:="¢", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

The issue is that I cannot paste certain characters into the What:=" ", because the editor does not recognize them. An example is the square root sign. If I try and paste it from character map is is not a radical sign in the editor.

Any way around this??

Thanks Dan

 

[Leith Ross]

Hello Danny,

You can solve this problem by downloading Microsoft Layer for Unicode Support at the link below...

http://www.microsoft.com/downloads/...d7-ed06-4f0d-80a4-2a7eeaee17e2&displaylang=en

Sincerely,
Leith Ross

 

[dannnv]

Supported Operating Systems: Windows 95; Windows 98; Windows 98 Second Edition; Windows ME

Are you sure? I am running XP. The MS site does not say it supports XP. I have newer operating system , its the Excel version that is old.

 

[pgc01]

Hi Dan

You can also use the character Unicode value.

For ex., the code of the square root character is hexadeximal 221A.

Write something with the letter "V" in A1 (ex: "V169") and try:

Range("A1").Replace What:="V", Replacement:=ChrW(&H221A), LookAt:=xlPart, MatchCase:=False

 

[dannnv]

THanks pgc01,

Although I needed to use your advice in reverse. I need to put the hexidecimal code to search for the character I cannot paste into the editor. When I pasted a square root symbol into an excel cell, it actually found it and replaced it with the unicode string.

Cells.Replace What:=ChrW(&H221A), Replacement:="√", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I just to to get the hex codes for the characters I am looking for.

Thanks again

D

 

[jindon]

try

For i = 8482 To 9591
    debug.print charw(i)
Next

 

[pgc01]

I'm sorry but I don't understand.

I hope one of these options helps.

If you mean that you'd like to find a character and you don't know its code you'll have to search
- in xl 2007 you can do it in Insert->Symbol
- in previous versions you can use the OS Character Map (in the Accessories) or use the Paste symbol in word and copy to excel
- an alternative is to google for it

If you mean you have imported or pasted a text with symbols into a cell and you'd like to know its Unicode code, use the AscW() function.
For ex., to know the Unicode code of the second character in A1 in decimal

MsgBox AscW(Mid(Range("A1"), 2, 1))

or in hex

MsgBox Application.WorksheetFunction.Dec2Hex(AscW(Mid(Range("A1"), 2, 1)))

Hope this helps