CODEWITHSUNDEEP
excelvbaformatting
Lucas Raphael Pianegonda
Lucas Raphael Pianegonda

Reputation: 1181

Proper NumberFormat in VBA-excel

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

Here is what I get with "#'##0"

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.

Upvotes: 1

Views: 596

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

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

Then 

.NumberFormat = "#,##0"

will produce

enter image description here

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.

Upvotes: 4

Grzesiek Danowski
Grzesiek Danowski

Reputation: 467

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/

Upvotes: 3

Related Questions