Use unicode hex symbols in vba code format sub routine

smd747

Board Regular
Joined
Apr 24, 2011
Messages
214
This was a great tip from datapigtechnologies.com
Improving the Default Percent Formatting

Improving the Default Percent Formatting » Bacon Bits:
Which works great, but becomes a pain when you use it often.
I have searched and attempted to create a simple section format macro.
Code:
Sub FormatTest()
    Selection.NumberFormat = " [BLUE]?+0%;[RED]?(0%)"
End Sub
But it keeps replacing the symbols with ?
It should have been
I have searched and attempted to create a simple section format macro.
Code:
Sub FormatTest()
    Selection.NumberFormat = " [BLUE]▲+0%;[RED]▼(0%)"
End Sub
Is it not possible to use symbols in vba
I also tried
IN THE VBA CODE
UNICODE HEX
▲=CHAR(25B2)
▼= CHAR(25BC)
This is what the macro recorder does
Code:
Sub Macro1()
' Macro1 Macro
    Range("J2").Select
    ActiveCell.FormulaR1C1 = " [BLUE]?+0%;[RED]?(0%)"
    Range("M4:M8").Select
    Selection.NumberFormat = "[Blue] ?+0%;[Red]?(0%)"
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("M4").Select
End Sub

Is this possible ?? What are alternative ways?? Thanks

 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Have you tried using VBA's ChrW to include the symbols in the number format string?
Code:
Sub FormatTest()
    Selection.NumberFormat = " [BLUE]" & ChrW(&H25B2) & "+0%;[RED]" & ChrW(&H25BC) & "(0%)"
End Sub
 
Upvote 0
Thanks Norie that worked, I tried Chr not ChrW and my syntax was not formatted right. Still learning VBA Thanks for the education, Thank you
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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