Returning unicode characters from a UDF

[JenniferMurphy]

How can I return unicode special characters from a UDF? I am writing one that examines a range of cells and returns a value with the prefix indicating where that cell fits in a range. I wold like that prefix to be the up arrow (Unicode 2191) or the down arrow (unicode 2192).

I tried returning ChrW(2192) & Abs(Round(pReading - pMin, 0)).

The value in the watch window is "?9", but the value in the cell is "9[]", where "[]" represents a square.

How can I get it to return a string that will display as "^9", but with the up arrow in place of the "^".

If I replace the ChrW(2192) with "-" ("-" & Abs(Round(pReading - pMin, 0)), it correctly displays "-9".

Thanks

 

[Domenic]

ChrW requires a decimal value. Therefore, first convert your hex value into a decimal value, and then use ChrW to return the unicode character...

Hex constant -----------------------------------> &H2191

Convert to decimal value 8593 ----------------> CLng("&H2191")

Return unicode character ----------------------> ChrW(CLng("&H2191"))

So you would have the following...

ChrW(CLng("&H2191")) & Abs(Round(pReading - pMin, 0))

Hope this helps!

 

[JenniferMurphy]

ChrW requires a decimal value. Therefore, first convert your hex value into a decimal value, and then use ChrW to return the unicode character...

Hex constant -----------------------------------> &H2191

Convert to decimal value 8593 ----------------> CLng("&H2191")

Return unicode character ----------------------> ChrW(CLng("&H2191"))

So you would have the following...

ChrW(CLng("&H2191")) & Abs(Round(pReading - pMin, 0))

Hope this helps!

Wow! That works like a champ. Thanks.

I wonder why my version caused the square character to follow the digit ("9[]")?

 

[Domenic]

Wow! That works like a champ. Thanks.

You're very welcome!

I wonder why my version caused the square character to follow the digit ("9[]")?

With regards to ChrW(2192), you're supplying ChrW the decimal value 2192. This value is equal to the hex value &H0890 or the unicode code point U+0890. This code point, however, is unassigned, as per the following unicode character reference...

Unicode/Character reference/0000-0FFF - Wikibooks, open books for an open world

en.wikibooks.org

Therefore, since it's unassigned, ChrW returns a question mark. In my watch window window, it's displays as "?9", while in my cell it displays as "9?".

 

[JenniferMurphy]

Therefore, since it's unassigned, ChrW returns a question mark. In my watch window window, it's displays as "?9", while in my cell it displays as "9?".

OK, but why is the "?" after the "9" instead of before it? Why is it "9?" instead of "?9"? Just curious.

 

[Domenic]

That's a good question. Unfortunately, I don't know the answer.

 

[JenniferMurphy]

That's a good question. Unfortunately, I don't know the answer.

OK, no problem. Thanks