Defining Unicode characters as VBA global constants

[JenniferMurphy]

I need to make use of two Unicode special characters, the up arrow (&H2191) and the down arrow (&H2193).

Originally, I was using them in text string expressions like ChrW(CLng("&H2191")). But now I need to replicate that in multiple UDF. I'd prefer not to duplicate that code multiple times. If I ever change the characters, I will have to find all of the places and change each one.

So I tried to define global constants, but it isn't working. I first tried

Public Const gMaxCode as String = ChrW(CLng("&H2191"))

But it complained about it not being a "constant expression". It seemed like a constant expression to me. I don't know what part is not constant. I then tried

Public Const gMaxCode as String = ChrW(9593)

But, of course, that got the same error.

Is there a way I can get the up and down arrow characters into a global constant?

Thanks

 

[Fluff]

How about

Public Const gMaxCode As String = "&H2191"

and then convert it in the UDF

 

[JenniferMurphy]

How about

Public Const gMaxCode As String = "&H2191"

and then convert it in the UDF

Perfect. I should have thought of that. Thank you.

 

[Fluff]

You could also use "8593" for the up arrow & "8595" for the down arrow & then you can use ChrW(gMaxCode)
Rather than converting the Hex number to long first

 

[JenniferMurphy]

You could also use "8593" for the up arrow & "8595" for the down arrow & then you can use ChrW(gMaxCode)
Rather than converting the Hex number to long first

I thought of that, but I prefer to have the hex number in the constant as that's what is in the Unicode table. I already get things mixed up enough.

 

[Fluff]

Fair enough & thanks for the feedback

 

[Jaafar Tribak]

Perfect. I should have thought of that. Thank you.

ChrW expects a Long not a String.
so you can just declare the const as Long :

Public Const gMaxCode As Long = &H2191

Therefore, you don't have to take the extra step of converting it later on in code :

Dim sUpArrow As String

sUpArrow = ChrW(gMaxCode)

 

[JenniferMurphy]

ChrW expects a Long not a String.

Did anyone say it expects a string?

so you can just declare the const as Long :

Public Const gMaxCode As Long = &H2191

Therefore, you don't have to take the extra step of converting it later on in code :

Dim sUpArrow As String

sUpArrow = ChrW(gMaxCode)

That does save a step. My code is now:

Public Const gMaxCode As Long = &H2191
   . . .
OverUnder = ChrW(gMaxCode) & Round(pReading - pGood, 0)

Thanks