Using U+2191 character in Excel VBA

[amthorpe2000]

I have a list of headers that include ↑ (U+2191) in them, I want to write a formula in Excel VBA to do something when a cell contains this as the first character. However I can't seem to include it in the formula it just shows ?

e.g.

.FormulaR1C1 = "=IF(LEFT(RC[-8],1)=""?"","""",RC[-19])"

what I need is something that will give

.FormulaR1C1 = "=IF(LEFT(RC[-8],1)=""↑"","""",RC[-19])"

any suggestions.

 

[Alex Blakenburg]

Try this:
(from @Domenic > https://www.mrexcel.com/board/threads/returning-unicode-characters-from-a-udf.1117279/post-5384726)

.FormulaR1C1 = "=IF(LEFT(RC[-8],1)=" & """" & ChrW(CLng("&H2191")) & """" & ","""",RC[-19])"

 

[amthorpe2000]

Alex Blakenburg thank you. works perfectly.​

​

 

[Alex Blakenburg]

Thanks for letting me know. Glad we could help.

 

[Domenic]

It looks like I added an unnecessary step in the solution I offered in that referenced link.

The ChrW function simply requires a Long, which can be specified as a literal in the form of a decimal value, hexadecimal value, or octal value. So the following refer to the same character...

ChrW(8593)  'decimal value

ChrW(&H2191) 'hexadecimal value

ChrW(&O20621) 'octal value

Therefore, I think you can simply do the following...

.FormulaR1C1 = "=IF(LEFT(RC[-8],1)=""" & ChrW(&H2191) & ""","""",RC[-19])"

Hope this helps!

 

[Alex Blakenburg]

Thanks Domenic, I will update my notes