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:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
You could try:

Code:
.NumberFormat = "0.00% " & ChrW(8594) & ";0.00% " & ChrW(8592)
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0
I used insert then symbol. Found the symbol and inserted it into a cell eg A1. Then used =UNICODE(A1)
 
Upvote 0
Cool. Learned something new today. All my geeky friends will probably say they new that already. pfft, right!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top