Proper NumberFormat in VBA-excel

Question

I know at first this question sound easy.

I want to have a high comma after the third digit. For example: 10'000 or 1'000 and 100 and 10. I am able to do this manually in excel using "Format">"Number"> 0 "decimals". That gives me the right formatting. But now comes the twist! If I record the a macro of that action the code is something like this:

MyChart.Axes(xlValue).TickLabels.NumberFormat = "#'##0"

But this yields something else when a applied, namely : 100'000 10'000 1'000 '100 '10

Any suggestions on what format code to use ? I could always go through all ticklables check the value and format it indivdually but that's a pain. If I do not have to I'd rather not do it.

I suppose that you should use "#,##0" where comma marks thousand seperator that in your operating system settings can be defined as apostrophe. I don't know how format if you want different than in system settings. — Grzesiek Danowski
– Grzesiek Danowski, Commented Nov 14, 2018 at 12:34
@GrzesiekDanowski Perfect! Works! Put it as an answer. The macro recorder is useful but not without flaws unfortunately. — Lucas Raphael Pianegonda
– Lucas Raphael Pianegonda, Commented Nov 14, 2018 at 12:38

Pᴇʜ · Accepted Answer · 2018-11-14 12:45:36Z

If you want to change the thousands separator in Excel from default , to ' use

Application.ThousandsSeparator = "'" Application.UseSystemSeparators = False

Then

.NumberFormat = "#,##0"

will produce

Note that this will change the thousands separator in your complete Excel (not only that workbook).

Alternatively you can change it system wide in your system settings.

Grzesiek Danowski · Accepted Answer · 2018-11-14 12:42:03Z

I suppose that you should use "#,##0" where comma marks thousand seperator that in your operating system settings can be defined as apostrophe. I don't know how format if you want different than in system settings. Reference: https://www.ablebits.com/office-addins-blog/2016/07/07/custom-excel-number-format/

Collectives™ on Stack Overflow

Proper NumberFormat in VBA-excel

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related