Excel VBA 中的 .NumberFormat 选项是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20648149/
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
What are .NumberFormat Options In Excel VBA?
提问by user1760110
Can you please let me know what are the .NumberFormatformat options in Excel VBA? As you are fully aware Excel 2010 supports the following types:
您能否让我知道.NumberFormatExcel VBA中的格式选项是什么？如您所知，Excel 2010 支持以下类型：
I know that we can set for example Text type as:
我知道我们可以将例如文本类型设置为：
.NumberFormat ="@"
or for number:
或数字：
.NumberFormat = "0.00000"
Can you please let me know what are other options for types in VBA?
您能否让我知道 VBA 中类型的其他选项是什么？
回答by doovers
Note this was done on Excel for Mac 2011 but should be same for Windows
请注意，这是在 Excel for Mac 2011 上完成的，但对于 Windows 应该是相同的
Macro:
宏：
Sub numberformats()
Dim rng As Range
Set rng = Range("A24:A35")
For Each c In rng
Debug.Print c.NumberFormat
Next c
End Sub
Result:
结果：
General General
Number 0
Currency $#,##0.00;[Red]$#,##0.00
Accounting _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
Date m/d/yy
Time [$-F400]h:mm:ss am/pm
Percentage 0.00%
Fraction # ?/?
Scientific 0.00E+00
Text @
Special ;;
Custom #,##0_);[Red](#,##0)
(I just picked a random entry for custom)
（我只是随机选择了一个自定义条目）
回答by Kevin Lee Garner
Thanks to this question (and answers), I discovered an easy way to get at the exact NumberFormat string for virtually any format that Excel has to offer.
感谢这个问题（和答案），我发现了一种简单的方法来获取 Excel 必须提供的几乎任何格式的确切 NumberFormat 字符串。
How to Obtain the NumberFormat String for Any Excel Number Format
如何获取任何 Excel 数字格式的 NumberFormat 字符串
Step 1: In the user interface, set a cell to the NumberFormat you want to use.
第 1 步：在用户界面中，将单元格设置为您要使用的 NumberFormat。
In my example, I selected the Chinese (PRC) Currency from the options contained in the "Account Numbers Format" combo box.
在我的示例中，我从“帐号格式”组合框中包含的选项中选择了 CN (PRC) 货币。
Step 2: Expand the Number Format dropdown and select "More Number Formats...".
第 2 步：展开数字格式下拉菜单并选择“更多数字格式...”。
Step 3: In the Number tab, in Category, click "Custom".
第 3 步：在数字选项卡的类别中，单击“自定义”。
The "Sample" section shows the Chinese (PRC) currency formatting that I applied.
“示例”部分显示了我应用的 CN (PRC) 货币格式。
The "Type" input box contains the NumberFormat string that you can use programmatically.
“类型”输入框包含可以以编程方式使用的 NumberFormat 字符串。
So, in this example, the NumberFormat of my Chinese (PRC) Currency cell is as follows:
因此，在此示例中，我的 Chinese (PRC) Currency 单元格的 NumberFormat 如下所示：
_ [$￥-804]* #,##0.00_ ;_ [$￥-804]* -#,##0.00_ ;_ [$￥-804]* "-"??_ ;_ @_
If you do these steps for each NumberFormat that you desire, then the world is yours.
如果您为您想要的每个 NumberFormat 执行这些步骤，那么世界就是您的。
I hope this helps.
我希望这有帮助。
回答by Developer
doversgives us his great answer and based on it you can try use it like
dovers给了我们他很好的答案，基于它你可以尝试使用它
public static class CellDataFormat
{
public static string General { get { return "General"; } }
public static string Number { get { return "0"; } }
// Your custom format
public static string NumberDotTwoDigits { get { return "0.00"; } }
public static string Currency { get { return "$#,##0.00;[Red]$#,##0.00"; } }
public static string Accounting { get { return "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)"; } }
public static string Date { get { return "m/d/yy"; } }
public static string Time { get { return "[$-F400] h:mm:ss am/pm"; } }
public static string Percentage { get { return "0.00%"; } }
public static string Fraction { get { return "# ?/?"; } }
public static string Scientific { get { return "0.00E+00"; } }
public static string Text { get { return "@"; } }
public static string Special { get { return ";;"; } }
public static string Custom { get { return "#,##0_);[Red](#,##0)"; } }
}
回答by DougM
In Excel, you can set a Range.NumberFormatto any string as you would find in the "Custom" format selection. Essentially, you have two choices:
在 Excel 中，您可以将 a 设置Range.NumberFormat为在“自定义”格式选择中找到的任何字符串。基本上，您有两个选择：
- Generalfor no particular format.
- A custom formatted string, like "$#,##0", to specify exactly what format you're using.
- 一般没有特定格式。
- 自定义格式化字符串，如“$#,##0”，用于准确指定您使用的格式。
回答by ice1e0
The .NET Library EPPlusimplements a conversation from the string definition to the built in number. See class ExcelNumberFormat:
.NET 库EPPlus实现了从字符串定义到内置数字的对话。请参阅类ExcelNumberFormat：
internal static int GetFromBuildIdFromFormat(string format)
{
switch (format)
{
case "General":
return 0;
case "0":
return 1;
case "0.00":
return 2;
case "#,##0":
return 3;
case "#,##0.00":
return 4;
case "0%":
return 9;
case "0.00%":
return 10;
case "0.00E+00":
return 11;
case "# ?/?":
return 12;
case "# ??/??":
return 13;
case "mm-dd-yy":
return 14;
case "d-mmm-yy":
return 15;
case "d-mmm":
return 16;
case "mmm-yy":
return 17;
case "h:mm AM/PM":
return 18;
case "h:mm:ss AM/PM":
return 19;
case "h:mm":
return 20;
case "h:mm:ss":
return 21;
case "m/d/yy h:mm":
return 22;
case "#,##0 ;(#,##0)":
return 37;
case "#,##0 ;[Red](#,##0)":
return 38;
case "#,##0.00;(#,##0.00)":
return 39;
case "#,##0.00;[Red](#,#)":
return 40;
case "mm:ss":
return 45;
case "[h]:mm:ss":
return 46;
case "mmss.0":
return 47;
case "##0.0":
return 48;
case "@":
return 49;
default:
return int.MinValue;
}
}
When you use one of these formats, Excel will automatically identify them as a standard format.
当您使用其中一种格式时，Excel 会自动将它们识别为标准格式。
