Mixing
Avoid numbers being always rounded while using format
It's quite a basic problem but I can't seems to use the right format so that the numbers in my listbox aren't automatically rounded
I wanted to have a column showing prices but it's always rounded to the unit before the coma. I'm currently using this code
ListBox.List(lngIndex, 3) = (Format(ListBox.List(lngIndex, 3), "0.00€;0.00€"))
And instead of 85,72 I get 85.
excel vba excel-vba format
asked Jan 2 at 11:11
ThrynThryn
17710
|
show 4 more comments
It's quite a basic problem but I can't seems to use the right format so that the numbers in my listbox aren't automatically rounded
I wanted to have a column showing prices but it's always rounded to the unit before the coma. I'm currently using this code
ListBox.List(lngIndex, 3) = (Format(ListBox.List(lngIndex, 3), "0.00€;0.00€"))
And instead of 85,72 I get 85.
excel vba excel-vba format
asked Jan 2 at 11:11
ThrynThryn
17710
Is it possible that the column width is too small?
– Storax
Jan 2 at 11:53
Nope, it can show up to 4 numbers after the coma while I never use more than 2
– Thryn
Jan 2 at 11:55
How do you know? I can decrease the column width that it only shows two numbers and the remaining digits are just cut off.
– Storax
Jan 2 at 11:58
I simply tried it, and I know every entry so i'm certain about what's inside
– Thryn
Jan 2 at 12:00
1
Just another remark: What do you see in the immediate window when you add the linedebug.print ListBox.List(lngIndex, 3)just after the line you posted.
– Storax
Jan 2 at 12:52
|
show 4 more comments
It's quite a basic problem but I can't seems to use the right format so that the numbers in my listbox aren't automatically rounded
I wanted to have a column showing prices but it's always rounded to the unit before the coma. I'm currently using this code
ListBox.List(lngIndex, 3) = (Format(ListBox.List(lngIndex, 3), "0.00€;0.00€"))
And instead of 85,72 I get 85.
excel vba excel-vba format
asked Jan 2 at 11:11
ThrynThryn
17710
It's quite a basic problem but I can't seems to use the right format so that the numbers in my listbox aren't automatically rounded
I wanted to have a column showing prices but it's always rounded to the unit before the coma. I'm currently using this code
ListBox.List(lngIndex, 3) = (Format(ListBox.List(lngIndex, 3), "0.00€;0.00€"))
And instead of 85,72 I get 85.
excel vba excel-vba format
excel vba excel-vba format
asked Jan 2 at 11:11
ThrynThryn
17710
asked Jan 2 at 11:11
ThrynThryn
17710
asked Jan 2 at 11:11
ThrynThryn
17710
asked Jan 2 at 11:11
ThrynThryn
17710
asked Jan 2 at 11:11
ThrynThryn
17710
17710
Is it possible that the column width is too small?
– Storax
Jan 2 at 11:53
Nope, it can show up to 4 numbers after the coma while I never use more than 2
– Thryn
Jan 2 at 11:55
How do you know? I can decrease the column width that it only shows two numbers and the remaining digits are just cut off.
– Storax
Jan 2 at 11:58
I simply tried it, and I know every entry so i'm certain about what's inside
– Thryn
Jan 2 at 12:00
1
Just another remark: What do you see in the immediate window when you add the linedebug.print ListBox.List(lngIndex, 3)just after the line you posted.
– Storax
Jan 2 at 12:52
|
show 4 more comments
Is it possible that the column width is too small?
– Storax
Jan 2 at 11:53
Nope, it can show up to 4 numbers after the coma while I never use more than 2
– Thryn
Jan 2 at 11:55
How do you know? I can decrease the column width that it only shows two numbers and the remaining digits are just cut off.
– Storax
Jan 2 at 11:58
I simply tried it, and I know every entry so i'm certain about what's inside
– Thryn
Jan 2 at 12:00
1
Just another remark: What do you see in the immediate window when you add the linedebug.print ListBox.List(lngIndex, 3)just after the line you posted.
– Storax
Jan 2 at 12:52
Is it possible that the column width is too small?
– Storax
Jan 2 at 11:53
Is it possible that the column width is too small?
– Storax
Jan 2 at 11:53
Nope, it can show up to 4 numbers after the coma while I never use more than 2
– Thryn
Jan 2 at 11:55
Nope, it can show up to 4 numbers after the coma while I never use more than 2
– Thryn
Jan 2 at 11:55
How do you know? I can decrease the column width that it only shows two numbers and the remaining digits are just cut off.
– Storax
Jan 2 at 11:58
How do you know? I can decrease the column width that it only shows two numbers and the remaining digits are just cut off.
– Storax
Jan 2 at 11:58
I simply tried it, and I know every entry so i'm certain about what's inside
– Thryn
Jan 2 at 12:00
I simply tried it, and I know every entry so i'm certain about what's inside
– Thryn
Jan 2 at 12:00
1
1
Just another remark: What do you see in the immediate window when you add the line
debug.print ListBox.List(lngIndex, 3) just after the line you posted.– Storax
Jan 2 at 12:52
Just another remark: What do you see in the immediate window when you add the line
debug.print ListBox.List(lngIndex, 3) just after the line you posted.– Storax
Jan 2 at 12:52
|
show 4 more comments
2 Answers
2
active
oldest
votes
I think the problem is that the source of your Format() function is a string: ListBox.List(lngIndex, 3), while Format expects a Number or Date. There must be some implicit conversion that loses the decimals.
If you type print Format(0.856, "0.00€;0.00€") in the debug window it works fine.
answered Jan 2 at 11:21
Patrick HonorezPatrick Honorez
19.1k565120
If I enter Format("85,72", "0.00€;0.00€") in the debug window it also works quite well even if it's entered as a string
– Thryn
Jan 2 at 11:38
ok. Then try toPrint ListBox.List(lngIndex, 3)...
– Patrick Honorez
Jan 2 at 13:49
on my pc,print Format("85,72", "0.00€;0.00€")returns 8572.00€, which is not what is expected."85.72"(with a decimal dot) is working.
– Patrick Honorez
Jan 2 at 13:54
It may have something to do with my localization, cause I use Excel in French.
– Thryn
Jan 2 at 13:56
add a comment |
It was a miss configuration of an SQL database where the list took her source. A column wasn't configured as Decimal. Quite embarassing
answered Jan 2 at 13:59
ThrynThryn
17710
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%2f54005265%2favoid-numbers-being-always-rounded-while-using-format%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 think the problem is that the source of your Format() function is a string: ListBox.List(lngIndex, 3), while Format expects a Number or Date. There must be some implicit conversion that loses the decimals.
If you type print Format(0.856, "0.00€;0.00€") in the debug window it works fine.
answered Jan 2 at 11:21
Patrick HonorezPatrick Honorez
19.1k565120
If I enter Format("85,72", "0.00€;0.00€") in the debug window it also works quite well even if it's entered as a string
– Thryn
Jan 2 at 11:38
ok. Then try toPrint ListBox.List(lngIndex, 3)...
– Patrick Honorez
Jan 2 at 13:49
on my pc,print Format("85,72", "0.00€;0.00€")returns 8572.00€, which is not what is expected."85.72"(with a decimal dot) is working.
– Patrick Honorez
Jan 2 at 13:54
It may have something to do with my localization, cause I use Excel in French.
– Thryn
Jan 2 at 13:56
add a comment |
I think the problem is that the source of your Format() function is a string: ListBox.List(lngIndex, 3), while Format expects a Number or Date. There must be some implicit conversion that loses the decimals.
If you type print Format(0.856, "0.00€;0.00€") in the debug window it works fine.
answered Jan 2 at 11:21
Patrick HonorezPatrick Honorez
19.1k565120
If I enter Format("85,72", "0.00€;0.00€") in the debug window it also works quite well even if it's entered as a string
– Thryn
Jan 2 at 11:38
ok. Then try toPrint ListBox.List(lngIndex, 3)...
– Patrick Honorez
Jan 2 at 13:49
on my pc,print Format("85,72", "0.00€;0.00€")returns 8572.00€, which is not what is expected."85.72"(with a decimal dot) is working.
– Patrick Honorez
Jan 2 at 13:54
It may have something to do with my localization, cause I use Excel in French.
– Thryn
Jan 2 at 13:56
add a comment |
I think the problem is that the source of your Format() function is a string: ListBox.List(lngIndex, 3), while Format expects a Number or Date. There must be some implicit conversion that loses the decimals.
If you type print Format(0.856, "0.00€;0.00€") in the debug window it works fine.
answered Jan 2 at 11:21
Patrick HonorezPatrick Honorez
19.1k565120
I think the problem is that the source of your Format() function is a string: ListBox.List(lngIndex, 3), while Format expects a Number or Date. There must be some implicit conversion that loses the decimals.
If you type print Format(0.856, "0.00€;0.00€") in the debug window it works fine.
answered Jan 2 at 11:21
Patrick HonorezPatrick Honorez
19.1k565120
answered Jan 2 at 11:21
Patrick HonorezPatrick Honorez
19.1k565120
answered Jan 2 at 11:21
Patrick HonorezPatrick Honorez
19.1k565120
answered Jan 2 at 11:21
Patrick HonorezPatrick Honorez
19.1k565120
19.1k565120
If I enter Format("85,72", "0.00€;0.00€") in the debug window it also works quite well even if it's entered as a string
– Thryn
Jan 2 at 11:38
ok. Then try toPrint ListBox.List(lngIndex, 3)...
– Patrick Honorez
Jan 2 at 13:49
on my pc,print Format("85,72", "0.00€;0.00€")returns 8572.00€, which is not what is expected."85.72"(with a decimal dot) is working.
– Patrick Honorez
Jan 2 at 13:54
It may have something to do with my localization, cause I use Excel in French.
– Thryn
Jan 2 at 13:56
add a comment |
If I enter Format("85,72", "0.00€;0.00€") in the debug window it also works quite well even if it's entered as a string
– Thryn
Jan 2 at 11:38
ok. Then try toPrint ListBox.List(lngIndex, 3)...
– Patrick Honorez
Jan 2 at 13:49
on my pc,print Format("85,72", "0.00€;0.00€")returns 8572.00€, which is not what is expected."85.72"(with a decimal dot) is working.
– Patrick Honorez
Jan 2 at 13:54
It may have something to do with my localization, cause I use Excel in French.
– Thryn
Jan 2 at 13:56
If I enter Format("85,72", "0.00€;0.00€") in the debug window it also works quite well even if it's entered as a string
– Thryn
Jan 2 at 11:38
If I enter Format("85,72", "0.00€;0.00€") in the debug window it also works quite well even if it's entered as a string
– Thryn
Jan 2 at 11:38
ok. Then try to
Print ListBox.List(lngIndex, 3)...– Patrick Honorez
Jan 2 at 13:49
ok. Then try to
Print ListBox.List(lngIndex, 3)...– Patrick Honorez
Jan 2 at 13:49
on my pc,
print Format("85,72", "0.00€;0.00€") returns 8572.00€, which is not what is expected. "85.72" (with a decimal dot) is working.– Patrick Honorez
Jan 2 at 13:54
on my pc,
print Format("85,72", "0.00€;0.00€") returns 8572.00€, which is not what is expected. "85.72" (with a decimal dot) is working.– Patrick Honorez
Jan 2 at 13:54
It may have something to do with my localization, cause I use Excel in French.
– Thryn
Jan 2 at 13:56
It may have something to do with my localization, cause I use Excel in French.
– Thryn
Jan 2 at 13:56
add a comment |
It was a miss configuration of an SQL database where the list took her source. A column wasn't configured as Decimal. Quite embarassing
answered Jan 2 at 13:59
ThrynThryn
17710
add a comment |
It was a miss configuration of an SQL database where the list took her source. A column wasn't configured as Decimal. Quite embarassing
answered Jan 2 at 13:59
ThrynThryn
17710
add a comment |
It was a miss configuration of an SQL database where the list took her source. A column wasn't configured as Decimal. Quite embarassing
answered Jan 2 at 13:59
ThrynThryn
17710
It was a miss configuration of an SQL database where the list took her source. A column wasn't configured as Decimal. Quite embarassing
answered Jan 2 at 13:59
ThrynThryn
17710
answered Jan 2 at 13:59
ThrynThryn
17710
answered Jan 2 at 13:59
ThrynThryn
17710
answered Jan 2 at 13:59
ThrynThryn
17710
17710
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%2f54005265%2favoid-numbers-being-always-rounded-while-using-format%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
Is it possible that the column width is too small?
– Storax
Jan 2 at 11:53
Nope, it can show up to 4 numbers after the coma while I never use more than 2
– Thryn
Jan 2 at 11:55
How do you know? I can decrease the column width that it only shows two numbers and the remaining digits are just cut off.
– Storax
Jan 2 at 11:58
I simply tried it, and I know every entry so i'm certain about what's inside
– Thryn
Jan 2 at 12:00
1
Just another remark: What do you see in the immediate window when you add the line
debug.print ListBox.List(lngIndex, 3)just after the line you posted.– Storax
Jan 2 at 12:52