如何在 Excel VBA 中应用自定义格式，例如“文本：”0.0%
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16302795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I apply a custom format such as "Text:" 0.0% in Excel VBA
提问by psychonomics
I would like Excel VBA to write either a number or a percentage, depending upon a user control.
我希望 Excel VBA 根据用户控件编写数字或百分比。
In Excel, I can hit Ctrl 1 to apply a custom format such as "Text:" 0.0% or "Text:" 0.0.
在 Excel 中，我可以按 Ctrl 1 来应用自定义格式，例如“文本：”0.0% 或“文本：”0.0。
I have managed to write the text and then use either Format() or Round(), depending upon the control value. However, Format has two decimal places, and I would like a single decimal place.
我设法编写了文本，然后根据控件值使用 Format() 或 Round()。但是，格式有两个小数位，我想要一个小数位。
Here is some illustrative code. As stated above, I would like to have control over the number of decimal places for the percentages (e.g. 0, 1, 2).
这是一些说明性代码。如上所述，我想控制百分比的小数位数（例如 0、1、2）。
Select Case sControl
Case "Percentage"
oRange = "Text: " & Format(dvalue + 0.000001, "Percent")
Case "Numeric"
oRange = "Text: " & Round(dvalue + 0.000001, 1)
End Select
Any pointers on how to do this would be great. I am adding 0.000001 to dvalue in both cases, as I understand that this helps to maintain traditional rounding accuracy.
关于如何做到这一点的任何指示都会很棒。在这两种情况下，我都将 0.000001 添加到 dvalue，据我所知，这有助于保持传统的舍入精度。
Cheers Tim
干杯蒂姆
回答by pn7a
I think this is what you are looking for:
我认为这就是你要找的：
FormatPercent(1 + 0.000001, 1)
It formats as a percentage and the second argument (the 1 in this case) is the number of decimal points you want.
它的格式为百分比，第二个参数（在本例中为 1）是您想要的小数点数。
回答by K_B
How this is done is by changing the Range.Styleand/or the Range.NumberFormatproperty for a given Range.
这是如何通过更改给定的Range.Style和/或Range.NumberFormat属性来完成的Range。
For example: Selection.NumberFormat = "0.00000%"
例如： Selection.NumberFormat = "0.00000%"
In general how to get to know this in Excel-VBA is simply by recording a macro where you do exactly what you want to happen in your macro. Stop recording, go to the VBA window and look at the new macro in one of the Module1, Module2, ... modules.
一般来说，如何在 Excel-VBA 中了解这一点很简单，只需录制一个宏，您就可以在其中执行您想要在宏中执行的操作。停止录制，转到 VBA 窗口并查看 Module1、Module2、... 模块之一中的新宏。
You don't actually need to round the numbers to get the formatting that you want, just as you wouldn't need to when you apply number formatting manually in your sheets.
您实际上不需要对数字进行四舍五入以获得所需的格式，就像在工作表中手动应用数字格式时不需要那样。
