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:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
You could try:

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,234
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

ADVERTISEMENT

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

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,768
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
8,110
Office Version
  1. 365
Platform
  1. 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,768
Cool. Learned something new today. All my geeky friends will probably say they new that already. pfft, right!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,367
Messages
5,528,271
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top