使用 VBA 将单元格格式化为任意货币而不考虑语言环境
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14443263/
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
Format a cell as arbitrary currency regardless of locale, using VBA
提问by harryg
This is really bugging me as it seems pretty illogical the way it's working.
这真的让我很烦恼，因为它的工作方式似乎很不合逻辑。
I have a macro to format a cell as a currency using a bit of code to obtain the currency symbol.
我有一个宏来使用一些代码将单元格格式化为货币来获取货币符号。
Here is the code involved:
这是涉及的代码：
Dim sym As String
sym = reportConstants(ISOcode)
'Just use the ISO code if there isn't a symbol available
If sym = "" Then
sym = ISOcode
End If
With range(.Offset(0, 3), .Offset(3, 3))
.NumberFormat = sym & "#,##0;(" & sym & "#,##0)"
Debug.Print sym & "#,##0;(" & sym & "#,##0)"
End With
reportConstantsis a dictionary object with currency symbols defined as strings. E.g. reportConstants("USD") = "$". This is defined earlier in the macro.
reportConstants是一个字典对象，货币符号定义为字符串。例如reportConstants("USD") = "$"。这在前面的宏中定义。
When the macro runs it gets the ISO code and should then format the cell with the corresponding currency symbol.
当宏运行时，它会获取 ISO 代码，然后应使用相应的货币符号格式化单元格。
When I run it in one instance the ISO code is "USD" - so symis defined as "$"- but it still formats the cell with a pound sign (￡). When I debug.printthe format cell string it shows $#,##0;($#,##0)so, as long as I got my syntax correct, it should use a dollar sign in the cell. But it uses a ￡ sign instead. (I am running a UK version of excel so it may be defaulting to ￡-sign, but why?)
当我在一个实例中运行它时，ISO 代码是“USD”——所以sym定义为"$"——但它仍然用井号 (￡) 格式化单元格。当我debug.print格式化单元格字符串时$#,##0;($#,##0)，只要我的语法正确，它就应该在单元格中使用美元符号。但它使用 ￡ 符号代替。（我正在运行英国版的 excel，所以它可能默认为 ￡ 符号，但为什么呢？）
Any help greatly appreciated.
非常感谢任何帮助。
回答by MattCrum
I just recorded a macro to set the format to $xx.xx and it created this: [$$-409]#,##0.00. Looks like the -409 localises the currency to a particular country; it works without it - try changing yours to .NumberFormat = "[$" & sym & "]#,##0.00"
我刚刚录制了一个宏以将格式设置为 $xx.xx 并创建了这个：[$$-409]#,##0.00. 看起来 -409 将货币本地化为特定国家；没有它也能工作 - 尝试将您的更改为.NumberFormat = "[$" & sym & "]#,##0.00"
回答by bonCodigo
Btw guess I read your question somewhat after posting ;) Excel is well influenced by the regional settings of your computer for currency, language, dates... Using numberformat can force it to keep the sign you require. if it is a matter of rounding up you can try to: On Excel 2010, go to File - Options - Advanced and scroll down to "When calculating this workbook" and click on the "set precision as displayed" and OK out.?
顺便说一句，我在发布后阅读了您的问题；) Excel 受计算机区域设置的影响很大，如货币、语言、日期...使用 numberformat 可以强制它保留您需要的符号。如果是四舍五入的问题，您可以尝试：在 Excel 2010 上，转到文件 - 选项 - 高级并向下滚动到“计算此工作簿时”，然后单击“设置显示的精度”并确定。？
Try this: given your values are numerics/ integers/decimals....
试试这个：假设你的值是数字/整数/小数....
Range("a2").Style = "Currency"
Range("a2").Style = "Currency"
Or you can use format:
或者您可以使用格式：
Format(value, "Currency")
Format(value, "Currency")
Format(Range(a2).value, "Currency")
Format(Range(a2).value, "Currency")
References:
参考：
http://www.mrexcel.com/forum/excel-questions/439331-displaying-currency-based-regional-settings.html
http://www.mrexcel.com/forum/excel-questions/439331-displaying-currency-based-regional-settings.html
http://www.addictivetips.com/microsoft-office/excel-2010-currency-values/
http://www.addivetips.com/microsoft-office/excel-2010-currency-values/
(PS: I am on mobile, you may try these two links)
（PS：我在手机上，你可以试试这两个链接）
