Custom Number Format, with Special Character, Macro

szymagic

Board Regular
Joined
Jul 8, 2016
Messages
61
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?

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

End With

Any help would be greatly appreciated!
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,727
Office Version
365
Platform
Windows
You could try:

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,803
Two possibilities:

Save the format on the sheet somewhere, then use:

Code:
.NumberFormat = Range("Z99").Value

End With
or
Code:
.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.

Code:
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

Board Regular
Joined
Jul 8, 2016
Messages
61
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/office/vba/Language/Reference/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

Code:
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

Well-known Member
Joined
May 31, 2015
Messages
1,600
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.
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,727
Office Version
365
Platform
Windows
I used insert then symbol. Found the symbol and inserted it into a cell eg A1. Then used =UNICODE(A1)
 

szymagic

Board Regular
Joined
Jul 8, 2016
Messages
61
Thanks Steve the fish and Eric W,


That worked beautifully! I really appreciate it!
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,600
Cool. Learned something new today. All my geeky friends will probably say they new that already. pfft, right!
 

Forum statistics

Threads
1,078,338
Messages
5,339,638
Members
399,317
Latest member
mLife

Some videos you may like

This Week's Hot Topics

Top