Custom Number Format, with Special Character, Macro

[szymagic]

Hello,

Thank you for your assistance! I have a macro that applies a custom number format to selected cells. The number format works great within the workbook as:

0.00%" →";0.00%" ←"

I'd like to apply this using a macro. The VBA window won't display the "→" arrow symbols. They are displayed as "?"

I'm assuming I have to use CHAR or ChrW? What is the character code for the left and right arrows, and how do I incorporate them correctly into the .NumberFormat code?

With Selection[INDENT]
.NumberFormat = 0.00%" →";0.00%" ←"
[/INDENT]

End With

Any help would be greatly appreciated!

 

[Jeffrey Mahoney]

I believe that character falls into the extended character set for most fonts. Commands in VBA like Chr() and CHRW() only work with a range of zero to 255.

I'm not sure that's possible. It it is I'd like to know how.


https://docs.microsoft.com/en-us/of...ence/User-Interface-Help/character-set-128255

 

[steve the fish]

You could try:

.NumberFormat = "0.00% " & ChrW(8594) & ";0.00% " & ChrW(8592)

 

[Eric W]

Two possibilities:

Save the format on the sheet somewhere, then use:

.NumberFormat = Range("Z99").Value

End With

or

.NumberFormat = Range("Z99").NumberFormat

End With

If you really want to define it entirely in VBA, you can use the UNICODE character set, which has tens of thousands of characters.

Selection.NumberFormat = "0.00%"" " & WorksheetFunction.Unichar(8594) & _
                         """;0.00%"" " & WorksheetFunction.Unichar(8592) & """"

Do a web search for "UNICODE character set" and you'll find several pages defining them, so you'll know what numbers to use.

 

[szymagic]

I believe that character falls into the extended character set for most fonts. Commands in VBA like Chr() and CHRW() only work with a range of zero to 255.

I'm not sure that's possible. It it is I'd like to know how.


https://docs.microsoft.com/en-us/of...ence/User-Interface-Help/character-set-128255

Well that's a shame.

However, I think I found a work around. I am passing the string through the workbook.

I pasted this: 0.00%" →";0.00%" ←" into a range on a hidden sheet

and then called it with vba

Dim x As String


x = Sheets("HiddenSheet").Range("H50").Value

With Selection

    .NumberFormat = x

End With

If anyone knows of a cleaner solution please don't keep it to yourself!

 

[Jeffrey Mahoney]

Steve the Fish or Eric W,

How did you know the character numbers were 8594 and 8592?

Most of the Unicode tables are showing hexadecimal format or something.

 

[Eric W]

Go to:

https://www.vertex42.com/ExcelTips/unicode-symbols.html

There are other sites as well that show decimal numbers, as well as many other symbols.

 

[steve the fish]

I used insert then symbol. Found the symbol and inserted it into a cell eg A1. Then used =UNICODE(A1)

 

[szymagic]

Thanks Steve the fish and Eric W,


That worked beautifully! I really appreciate it!

 

[Jeffrey Mahoney]

Cool. Learned something new today. All my geeky friends will probably say they new that already. pfft, right!