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)

 

[Leith Ross]

Hello Jennifer,

You need to use the opposite of ChrW which is AscW in your test.

    If AscW(OU) = gMaxCode Then AMHi = AMHi + 1

 

[JenniferMurphy]

Hello Jennifer,

You need to use the opposite of ChrW which is AscW in your test.

    If AscW(OU) = gMaxCode Then AMHi = AMHi + 1

Perfect, thank you. I knew it had to be something like that. ?

 

[JenniferMurphy]

Now I am totally confused. I am storing a Unicode character in a cell with one UDF. But when a different UDF reads that same character back and compares it with the exact same character (global constant) that was used to store it, the compare fails. Here's my code

'Global constant
Public Const gMinCode As Long = &H2193    'Down arrow character

'UDF #1: Store the down arrow in front of a number
OverUnder = ChrW(gMinCode) & Abs(Round(pReading - pMin, 0))

'UDF #2: Read the character back and compare with the same global constant
OU = Left(pPMRange(i).Text, 1)        'Get just 1st character
If OU = ChrW(gMinCode) Then MsgBox "equal"

The MgBox never gets executed.

What am I doing wrong now? ?

 

[RoryA]

Those code parts work in isolation for me, so we'd probably need to see more of the UDF.

 

[JenniferMurphy]

Those code parts work in isolation for me, so we'd probably need to see more of the UDF.

OK, here goes.

'Global constants
Public Const gMaxCode As Long = &H2191     'Up arrow character (High)
Public Const gGoodCode As Long = &H2192   'Right arrow character (Good)
Public Const gMinCode As Long = &H2193      'Down arrow character (Low)

This is essentially the complete OverUnder code. The calling sheet has a table of readings. Next to each column of readings is a column of OverUnder codes that indicate whether each reading is over, under, or within a target range. This code examines the adjacent reading and returns a code character (up/down/right arrow) plus a value.

'UDF #1: Store the down arrow in front of a number
Public Function OverUnder(pReading As Variant, pMin As Long, pMax As Long) As String
'If reading > Max, add up arrow flag (too high)
If pReading > pMax Then
   OverUnder = ChrW(gMaxCode) & Round(pReading - pGood, 0)
'If < Min, add down arrow flag (too low)
ElseIf pReading < pMin Then
   OverUnder = ChrW(gMinCode) & Abs(Round(pReading - pMin, 0))
'It's in the recommended range, format Min to Good as % & add right arrow (good)
Else
   OverUnder = (pReading - pMin) / (pGood - pMin)
   OverUnder = ChrW(gGoodCode) & Format(OverUnder, "0%")
End If
End Function

The PC Tally UDF is fairly complicated. I think I have extracted all of the relevant pieces. It is passed several ranges containing, among other things, the Over/Under values stored by the OverUnder UDF. Its job is to add up all of the codes in the different categories and return the tallies.

'UDF #2: Read the character back and compare with the same global constant
Public Function PCTally(pAMRange As Range, pAMSkip As Range, _
                        pPMRange As Range, pPMSkip As Range, _
                        pAMMin As Integer, pAMGood As Integer, pAMOK As Integer, _
                        pPMMin As Integer, pPMGood As Integer, pPMOK As Integer) As String

Dim OU As String                       'The code (first) character from the OverUnder function
Dim NumReadings As Integer   'Number of readings
NumReadings = pPMRange.Rows.Count   'Number of readings
For i = 2 To NumReadings - 1                    'Tally the readings (AM & PM)
   OU = Left(pPMRange(i).Text, 1)                  'Get just 1st character
   If OU = ChrW(gMinCode) Then MsgBox "equal"
Next i

End Function

 

[RoryA]

Have you stepped through the code, or used debug statements to verify the cell contents/addresses? It will be hard to test without your data setup.

 

[JenniferMurphy]

Yes, I have. I'll try to post the results.

It sounds like you are saying that what I am trying to do should work. That is, if I use a global string constant to store a Unicode character in a cell and then read it back using a different UDF, the read character should compare equal to the global constant. Correct?

Thanks

 

[RoryA]

Yes it should work.

 

[JenniferMurphy]

Have you stepped through the code, or used debug statements to verify the cell contents/addresses? It will be hard to test without your data setup.

Here are the results from the Immediate window for the first pass through the For loop in the PCTally UDF.

?numreadings
20
    OU = Left(pPMRange(i).Text, 1)        'Get just 1st character
?ou
?    'Unprintable character?
?ascw(ou)
8595
?gmincode
8595
?gmincode = ascw(ou)
True