Excel VBA - 添加自定义数字格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27950093/
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
Excel VBA - add a custom number format
提问by horace_vr
I have a file, generated outside Excel with many percentages. All these percentages have a single decimal place. When imported into excel, Excel adds a second decimal place to the percentages - this seems to be some default format for percentages in Excel. It just adds a "0".
我有一个文件，在 Excel 之外生成，有很多百分比。所有这些百分比都有一个小数位。当导入到 Excel 中时，Excel 会为百分比添加第二个小数位 - 这似乎是 Excel 中百分比的一些默认格式。它只是添加了一个“0”。
I want to format all double-decimal places percentages to a single decimal point. If I do this manually, by using CTRL+H, Find format/replace format, it goes well. But I cannot do this via VBA/macro, even if I record the macro while doing the replacement manually. The code generated by recording the macro is this:
我想将所有双小数位百分比格式化为一个小数点。如果我手动执行此操作，通过使用 CTRL+H，查找格式/替换格式，它会顺利进行。但是我不能通过 VBA/宏来做到这一点，即使我在手动替换时记录了宏。录制宏生成的代码是这样的：
Application.FindFormat.NumberFormat = "0.00%"
Application.ReplaceFormat.NumberFormat = "0.0%"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
The failure happens at:
失败发生在：
Application.ReplaceFormat.NumberFormat = "0.0%"
After a lot of trial and error, I found that the new format needs to be in the Custom format list. But single-decimal place percentages is not there by default. If I manually format a single cell to the desired format, the macro works. Of course, I do not want to do that with every file.
经过大量的反复试验，我发现新格式需要在自定义格式列表中。但默认情况下不存在单小数位百分比。如果我手动将单个单元格格式化为所需的格式，宏就可以工作。当然，我不想对每个文件都这样做。
Any input would be much appreciated.
任何输入将不胜感激。
Thanks in advance!
提前致谢！
回答by
If you apply the custom number format to a single cell, it will become 'listed' and subsequently available for future use with a bulk replacement operation. I might recommend using something like,
如果您将自定义数字格式应用于单个单元格，它将成为“已列出”，随后可通过批量替换操作供将来使用。我可能会建议使用类似的东西，
.range("A1").NumberFormat = "0.0%;@"
That particular format mask can be applied to any cell with a percentage or text. If you had a column header label with a text value and applied that CNF, the ;@portion forces the text to be displayed literally (without change). In this manner you are open to more possibilities whereyou put the first CNF. After it has been used once, it will be available for bulk replacement operation(s) on the remainder of the percentage values.
该特定格式掩码可以应用于任何带有百分比或文本的单元格。如果您有一个带有文本值的列标题标签并应用了该 CNF，则该;@部分会强制文本按字面显示（无需更改）。通过这种方式，您可以在放置第一个 CNF 的地方获得更多可能性。使用一次后，它将可用于对剩余百分比值进行批量替换操作。
回答by Gary's Student
Consider the simple:
考虑简单的：
Sub dural()
Dim r As Range
For Each r In ActiveSheet.UsedRange
If r.NumberFormat = "0.00%" Then
r.NumberFormat = "0.0%"
End If
Next r
End Sub
