special character in vba

[Steve]

morning all.
I want to insert the 1/3 character in VBA for a screen tip in my RibbonX
gallery for excel 2007. (RonDeBRuin has posted a nice article in the MSDN
library on it now. Extremely helpful for anyone who wants to make their own
gallery.)
According to Chip Pearson's webpage, I can find the code required for this
character by using =code("â…“")

While the character showing here is a 1/2, I actually selected 1/3. Unicode
character U+2153 from the character map.

The answer that I get when using Chip's recommended way is 63.
however, when I then input chr(63) I only get a '?' instead of the 1/3.

So, my question is-- in the vba code window, how is it that I can insert a
character that's not from the 255 "standard" choices we have available to us?

Thank you.

 

[Gary''s Student]

Since 2153 Hex is 8531 Decimal, putting the character in a cell is as easy as:

Sub dural()
ActiveCell.Value = ChrW(8531)
ActiveCell.Font.Name = "Arial Unicode MS"
End Sub

I don't know about putting it elsewhere.

 

[Steve]

Doh!
I remember now--- THANK YOU.
I so seldom use the chrW that I forgot all about it.
Have a great weekend.

 

[Steve]

Now, the next thing that I'm curious about is--
As you've got a simple macro to input a specific character in a specific
font, is there any way to create a special character list with the ChrW--
similar to the =char(row()) use that Chip references?
It sure would be nice to have some recollectability on what the codes are
for the fancier characters.

Again-- thank you for jogging a vague memory.....

 

[Rick Rothstein]

Well, I guess you could construct a simple For..Next loop that iterates from
the first ASCII value to the last ASCII value and print the ChrW for each of
them BUT is that a practical thing to do? I ask because there are literally
thousands upon thousands of "special characters" in a Unicode font.

 

[Steve]

Thanks Rick.
I realized that a few minutes ago, and have just finished running it out to
10000.
For anyone interested in having a list, my code is as folows:
////////////////////////////////////////////////////////////////

Sub AsciiChrW()
Dim Character As String
Dim Number As Long
Dim i As Integer
For i = 1 To 10000 Step 1

Number = i


Character = ChrW(Number)

ActiveCell = "The ASCII character of " & Number & " is " & Character
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select

Next i
End Sub


////////////////////////////////////////////////////////////////

for what it's worth, I tried 50000 but it threw an overflow error. I'll
extend it later if the need arises.

 

[Steve]

well, apparently it stops at 10000.
I just tried extending it to 15000 and it stopped at 10K.
the code will give the user the characters that will be displayed when using
ChrW() in a string for output in a message box or....-- in my case I used
them in my supertip/stringtip for a gallery in my RibbonX.