Testing for a unicode character in VBA

[JenniferMurphy]

I got some great help recently in learning how to return extended Unicode characters such as the up and down arrows from a UDF. Now I need to learn how to test for those same codes when read from the worksheet by a different UDF.

Here's my code. The 4 statements after the comments are trying to compare the character from the cell to the global Unicode constant, gMaxCode.

Public Const gMaxCode As Long = &H2191  'Up arrow

Public Function PCTally(pAMRange As Range) as String
Dim OU As String    'The code character in the cell
NumReadings = pAMRange.Rows.Count

For i = 2 To NumReadings - 1
   OU = Left(pAMRange(i).Text, 1)        'Get code character

'None of these tests works
   If OU = gMaxCode Then AMHi = AMHi + 1
   If ChrW(OU) = gMaxCode Then AMHi = AMHi + 1
   If OU = ChrW(gMaxCode) Then AMHi = AMHi + 1
   If OU = CLng(gMaxCode) Then AMHi = AMHi + 1

Next i

The character is stored in the cell using this code from a different UDF:

  OverUnder = ChrW(gMaxCode) & Round(pReading - pGood, 0)

 

[JenniferMurphy]

Please ignore the previous message. It got posted by mistake. I don't see any way to delete it. I'll post something better shortly. Sorry about that.

 

[JenniferMurphy]

I found the problem. First I was confusing the character with its Unicode index, a number. Then I was converting them in the wrong direction. Then I was confusing high readings with low readings. Basically, I didn't know which was was up! ?

This all would have been so much easier if VBA would simply display extended Unicode characters both in the Immediate window and the Watch window. ?

Thanks for the help! ?

 

[RoryA]

Yeah, but the VBE (and VBA) is really old and MS has no interest in it, so...

 

[yky]

It shows up in my watch window.

 

[JenniferMurphy]

It shows up in my watch window.

View attachment 1919

Interesting. What about the Immediate window?

 

[yky]

Works also.

 

[yky]

There is one setting in English version of Windows 10 that many people have no need to bother. In Settings/Time & Language/Language/Preferred Languages, you can add other (non-Latin) languages. This setting is for non-unicode compliance programs, such as VBE, to display unicode characters. I have Chinese added. This is why unicode can be displayed in VBE.