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
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.
excel vba excel-vba formatting
asked Nov 14 '18 at 12:29
Lucas Raphael PianegondaLucas Raphael Pianegonda
565217
add a comment |
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.
excel vba excel-vba formatting
asked Nov 14 '18 at 12:29
Lucas Raphael PianegondaLucas Raphael Pianegonda
565217
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
Nov 14 '18 at 12:34
@GrzesiekDanowski Perfect! Works! Put it as an answer. The macro recorder is useful but not without flaws unfortunately.
– Lucas Raphael Pianegonda
Nov 14 '18 at 12:38
add a comment |
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.
excel vba excel-vba formatting
asked Nov 14 '18 at 12:29
Lucas Raphael PianegondaLucas Raphael Pianegonda
565217
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.
excel vba excel-vba formatting
excel vba excel-vba formatting
asked Nov 14 '18 at 12:29
Lucas Raphael PianegondaLucas Raphael Pianegonda
565217
asked Nov 14 '18 at 12:29
Lucas Raphael PianegondaLucas Raphael Pianegonda
565217
asked Nov 14 '18 at 12:29
Lucas Raphael PianegondaLucas Raphael Pianegonda
565217
asked Nov 14 '18 at 12:29
Lucas Raphael PianegondaLucas Raphael Pianegonda
565217
asked Nov 14 '18 at 12:29
Lucas Raphael PianegondaLucas Raphael Pianegonda
565217
565217
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
Nov 14 '18 at 12:34
@GrzesiekDanowski Perfect! Works! Put it as an answer. The macro recorder is useful but not without flaws unfortunately.
– Lucas Raphael Pianegonda
Nov 14 '18 at 12:38
add a comment |
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
Nov 14 '18 at 12:34
@GrzesiekDanowski Perfect! Works! Put it as an answer. The macro recorder is useful but not without flaws unfortunately.
– Lucas Raphael Pianegonda
Nov 14 '18 at 12:38
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
Nov 14 '18 at 12:34
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
Nov 14 '18 at 12:34
@GrzesiekDanowski Perfect! Works! Put it as an answer. The macro recorder is useful but not without flaws unfortunately.
– Lucas Raphael Pianegonda
Nov 14 '18 at 12:38
@GrzesiekDanowski Perfect! Works! Put it as an answer. The macro recorder is useful but not without flaws unfortunately.
– Lucas Raphael Pianegonda
Nov 14 '18 at 12:38
add a comment |
2 Answers
2
active
oldest
votes
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/
answered Nov 14 '18 at 12:42
Grzesiek DanowskiGrzesiek Danowski
1926
add a comment |
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.
answered Nov 14 '18 at 12:45
PᴇʜPᴇʜ
22.1k42750
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53300290%2fproper-numberformat-in-vba-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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/
answered Nov 14 '18 at 12:42
Grzesiek DanowskiGrzesiek Danowski
1926
add a comment |
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/
answered Nov 14 '18 at 12:42
Grzesiek DanowskiGrzesiek Danowski
1926
add a comment |
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/
answered Nov 14 '18 at 12:42
Grzesiek DanowskiGrzesiek Danowski
1926
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/
answered Nov 14 '18 at 12:42
Grzesiek DanowskiGrzesiek Danowski
1926
answered Nov 14 '18 at 12:42
Grzesiek DanowskiGrzesiek Danowski
1926
answered Nov 14 '18 at 12:42
Grzesiek DanowskiGrzesiek Danowski
1926
answered Nov 14 '18 at 12:42
Grzesiek DanowskiGrzesiek Danowski
1926
1926
add a comment |
add a comment |
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.
answered Nov 14 '18 at 12:45
PᴇʜPᴇʜ
22.1k42750
add a comment |
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.
answered Nov 14 '18 at 12:45
PᴇʜPᴇʜ
22.1k42750
add a comment |
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.
answered Nov 14 '18 at 12:45
PᴇʜPᴇʜ
22.1k42750
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.
answered Nov 14 '18 at 12:45
PᴇʜPᴇʜ
22.1k42750
answered Nov 14 '18 at 12:45
PᴇʜPᴇʜ
22.1k42750
answered Nov 14 '18 at 12:45
PᴇʜPᴇʜ
22.1k42750
answered Nov 14 '18 at 12:45
PᴇʜPᴇʜ
22.1k42750
22.1k42750
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53300290%2fproper-numberformat-in-vba-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
Nov 14 '18 at 12:34
@GrzesiekDanowski Perfect! Works! Put it as an answer. The macro recorder is useful but not without flaws unfortunately.
– Lucas Raphael Pianegonda
Nov 14 '18 at 12:38