英文:
How can I format a number in VBA to display as million with 2 decimal places?
问题
我想要将数字显示为百万，小数点后保留2位数字，例如2.11百万。
我的代码可以将数字显示为百万，但当我尝试获取小数部分时，它会返回整个数字。
任何帮助都将非常感谢。
Format(CStr(Sheets("Latest").Range("P" & i).Value), "#0.00,,")
我已经尝试过多次更改格式，还尝试了使用VBA.Format或函数来强制文本，但都没有成功。
英文:
I would like to get number display as million with 2 numbers after the comma such as 2.11 m.
My code works to get million only, however when I ask to get deximal, it retrieve the entire number.
Any help will be great.
Format(CStr(Sheets("Latest").Range("P" & i).Value), "#0.00,,")
I already tried to to change the format several time, and also vba.format or function to force text but without success.
答案1
得分: 4
不确定是否仅通过格式设置可以完成此操作，但您始终可以像这样做：
Function millionsFormat(ByVal num As Double) As String
millionsFormat = Format(num / 1000000, "0.00") & " m"
End Function
进行测试：
Sub test()
Dim num As Double
num = 2100000
Debug.Print millionsFormat(num)
End Sub
输出：
2.10 m
英文:
Not sure if you can do this with just formatting but you can always do something like this:
Function millionsFormat(ByVal num As Double) As String
millionsFormat = Format(num / 1000000, "0.00") & " m"
End Function
to test it:
Sub test()
Dim num As Double
num = 2100000
Debug.Print millionsFormat(num)
End Sub
output:
2.10 m
答案2
得分: 1
"Display two-digit decimal places for an amount that is represented in whole millions"
"Not sure if you can do this with just formatting"
... Yes, you can via0,,.00 "m"*)
- *) Note that the regional country settings might invert the common English comma
,and point.separators especially in European countries.
or similar to @andrewb's answer via vba function assuming an input of e.g. 2100000 with a resulting output of 2.10 m:
Function millionsFormat(ByVal num As Double) As String
millionsFormat = Format(num, "0,,.00 ""m""") ' ~~> e.g. 2.10 m
End Function
- Note that in VBA string parts included by (literal) quotation marks need redoubled quotation marks within the string
""..."".
英文:
Display two-digit decimal places for an amount that is represented in whole millions
> "Not sure if you can do this with just formatting"
... Yes, you can via 0,,.00 "m" *)
- *) Note that the regional country settings might invert the common English comma
,and point.separators especially in European countries.
or similar to @andrewb 's answer via vba function assuming an input of e.g. 2100000 with a resulting output of 2.10 m:
Function millionsFormat(ByVal num As Double) As String
millionsFormat = Format(num, "0,,.00 ""m""") ' ~~> e.g. 2.10 m
End Function
- Note that in VBA string parts included by (literal) quotation marks need redoubled quotation marks within the string
""..."".
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论