Placing triangle symbols WITHIN cell for significance testing

H

Hari Prasadh

Hi,

I want to insert symbols (Arial font) 25BC, 25B2, 25C4 and 25BA from Unicode
(hex) -- Their descriptions are Black down-pointing triangle/up-pointing
triangle/left-pointing and right pointing triangle.

I tried code(1) to code(255) but none of them yielded the triangles I
require. I want to do this using a programmatic approach.

Is it possible and if yes, please guide me.

Thanks a lot,
Hari
India

PS: - I would be running some formulas for comparing cells across 2 columns
(Z-test) and based on the result (True or false) would like to place one of
these symbols in the third column of the same row. then using R Bovey's
chart labeler show the significances in my charts.
 
D

Dana DeLouis

Hi. Do Insert | Symbol | symbols tab.
Select "Arial" from the Font box (Upper left)
Select "Unicode" in From box (Lower Right)
Enter your Hex code in the "character code" box.

HTH :>)
 
D

Dana DeLouis

I want to do this using a programmatic approach.

Oops. I missed the "programming" part. A recorded macro didn't help. I'm
not sure just yet.
 
D

Dr. Stephan Kassanke

Hari Prasadh said:
Hi,

I want to insert symbols (Arial font) 25BC, 25B2, 25C4 and 25BA from
Unicode (hex) -- Their descriptions are Black down-pointing
triangle/up-pointing triangle/left-pointing and right pointing triangle.

I tried code(1) to code(255) but none of them yielded the triangles I
require. I want to do this using a programmatic approach.

Is it possible and if yes, please guide me.

Thanks a lot,
Hari
India

PS: - I would be running some formulas for comparing cells across 2
columns (Z-test) and based on the result (True or false) would like to
place one of these symbols in the third column of the same row. then using
R Bovey's chart labeler show the significances in my charts.
Click to expand...

You are trying to insert unicode symbols which are not included in ASCII
code, thus chr() will fail. Try the ChrW function which will yield unicode
strings. Convert your hex values to decimals and try

range("a1")= chrw(9660) will result in the triangle downwards in cell A1.

The "regular" worksheet function is, at least in my Excel 2003 version, not
unicode enabled, and will only accept values 1 to 255. IMHO your way to go:
a loop in VBA checking your conditions for each cell and inserting the
appropriate symbol via the chrw function.

cheers
Stephan
 
H

Hari Prasadh

Hi Stephen,

Thanx a lot for the help.

The following chrw codes in VBA worked great for me.

? 9650
? 9658
? 9660
? 9668


I was just wondering. The help file for "Insert a symbol" in excel XP says
within --Unicode options- "If you know the character code for a Unicode
character, you do not need to open the Symbol dialog box. Type the Unicode
hexadecimal character code in the document, and then press ALT+X. "

In the above why are they referring to a document as in "Type unicode....in
the document"? Shouldnt they say excel sheet?

I went to an excel sheet and typed 25BC and pressed Alt +X , but nothing
happened? Am I supposed to type this somewhere else. (I also tried with an
equal sign -- = 25BC -- but to no avail)
The "regular" worksheet function is, at least in my Excel 2003 version,
not unicode enabled, and will only accept values 1 to 255. IMHO your way
to go:
Click to expand...
In my excel EVEN 1 to 255 gives a name error. I thought that may be charw is
not available as a function within workbook environment and may be available
in VBA only. But you say that 1 to 255 is working fine in worksheet, so i
guess I am wrong.

Regards,
Hari
India
 
H

Hari Prasadh

Hi Dana,

Thanks for the time you took to respond.

I also tried a recorded macro but got some irrelevant stuff (posted in a
somewhat related thread "Shooting a Bullet character in Excel cell.")

Thanks a lot,
Hari
India
 
D

Dr. Stephan Kassanke

Hi Hari,

a lot of questions ;-) I try to answer them below.

cheers,
Stephan

Hari Prasadh said:
Hi Stephen,

Thanx a lot for the help.
Click to expand...

you are welcome.
The following chrw codes in VBA worked great for me.

? 9650
? 9658
? 9660
? 9668


I was just wondering. The help file for "Insert a symbol" in excel XP says
within --Unicode options- "If you know the character code for a Unicode
character, you do not need to open the Symbol dialog box. Type the Unicode
hexadecimal character code in the document, and then press ALT+X. "
Click to expand...

I know there is a way of inserting characters if you know the numeral
ASCII/ANSI code. Press ALT and type the numeral code on the numeral block
(right part of your keyboard), e.g. ALT+065 yields "A". I was unaware that
this is possible with Unicode as well. i have a notebook with no numeral
block here, so I cannot test this. But i do not think it makes sense to
first type the code and press Alt+X in a second step, i think X symbolizes
the code of the character. Just try pressing Alt+unicode numeral code (hex
and decimal) on the number block, maybe it works.
In the above why are they referring to a document as in "Type
unicode....in the document"? Shouldnt they say excel sheet?
Click to expand...

The help file is probably written in a generic manner, "documents" covers
worksheets as well.
I went to an excel sheet and typed 25BC and pressed Alt +X , but nothing
happened? Am I supposed to type this somewhere else. (I also tried with an
equal sign -- = 25BC -- but to no avail)
Click to expand...

see above

In my excel EVEN 1 to 255 gives a name error. I thought that may be charw
is not available as a function within workbook environment and may be
available in VBA only. But you say that 1 to 255 is working fine in
worksheet, so i guess I am wrong.
Click to expand...

I have a german version here. The VBA function Chr is called Zeichen() as a
worksheet function. Probably the name is char. try inserting a function via
Insert/function and then select the Text functions to restrict the number of
available text functions. ach function has a short description in the
dialog, so you will find the appropriate function. My guess is char, e.g.
=Char(65) yields A.
 
D

Dr. Stephan Kassanke

Hi Hari,

I was wrong on the input method:

http://www.linguistics.ucsb.edu/faculty/cumming/WordForLinguists/Unicode.htm

states:
Inserting Unicode characters is a bit more complex. The following is copied
from the Unicode FAQ:


--------------------------------------------------------------------------------

Q. How can I input any Unicode character if I know its hexadecimal code?

A. Some platforms have methods of hexadecimal entry; others have only
decimal entry.

On Windows, there is a decimal input method: hold down the alt key while
typing decimal digits on the numeric keypad. The ALT+decimal method requires
the code from the encoding of the command prompt. To enter Unicode decimal
values, you have to prefix the number with a 0 (zero). E.g. ALT+0163 is the
pound sign ("£"), in decimal.

There is a hex-to-Unicode entry method that works with WordPad 2000,
Office 2000 edit boxes, RichEdit controls in general, and in Microsoft Word
2002. To use it, type a character's hexadecimal code (in ASCII), making
corrections if needed, and then type Alt+x after it. The hexadecimal code is
replaced by the corresponding Unicode character. The Alt+x can be a toggle
(as in the Microsoft Office XP). That is, type it once to convert the hex
code to a character and type it again to convert the character back to a hex
code. If the hex code is preceded by one or more hexadecimal digits, you
will need to "select" the code so that the preceding hexadecimal characters
aren't included in the code. The code can range up to the value 0x10FFFF
(which is the highest character in the 17 planes of Unicode).
 
H

Hari Prasadh

Hi Stephen,

Thnx a TON for your detailed notes on all my doubts.

Very much appreciated.

(For my programming use chrw would come very very handy).

Thanks a lot,
Hari
India
 
D

Dr. Stephan Kassanke

Hari Prasadh said:
Hi Stephen,

Thnx a TON for your detailed notes on all my doubts.

Very much appreciated.

(For my programming use chrw would come very very handy).

Thanks a lot,
Hari
India
Click to expand...
you are always welcome,
Stephan
 
T

Tushar Mehta

To toggle between unicode characters and hex using ALT+x, use
Sub setAltX()
Application.OnKey "%x", "toggleUnicode"
End Sub
Sub resetAltX()
Application.OnKey "%x"
End Sub
Sub toggleUnicode()
If Len(ActiveCell.Value) > 1 Then
ActiveCell.Value = ChrW("&H" & ActiveCell.Value)
Else
ActiveCell.Value = Hex(AscW(ActiveCell.Value))
End If
End Sub

Run the first to enable the toggle capability. Then, select any cell
with a hex code in it and use ALT+x. Finally, to disable the toggle,
run the 2nd subroutine. Do keep in mind that XL will not run a macro
while in edit mode. That might reduce the value of the above
capability.

To toggle between unicode and decimal, use
Sub toggleUnicodeDec()
If Application.WorksheetFunction.IsNumber(ActiveCell.Value) Then
ActiveCell.Value = ChrW(ActiveCell.Value)
Else
ActiveCell.Value = AscW(ActiveCell.Value)
End If
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Top