防止 Excel 从 VBA 重新格式化科学记数法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25001155/
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
Prevent Excel from reformatting scientific notation from VBA
提问by jaysoncopes
Before you claim that this is a possible duplicate, hear me out.
在您声称这可能是重复之前，请听我说。
I'm trying to write some code for my company to be able to parse data onto a server, and in order to do so, I need to use scientific notation.
我正在尝试为我的公司编写一些代码，以便能够将数据解析到服务器上，为此，我需要使用科学记数法。
I've already figured out how to convert a regular number to scientific notation using Format(), but my problem is that I need to have Excel display 0.0000e+00, not 0.00e+00
我已经想出了如何使用 将常规数字转换为科学记数法Format()，但我的问题是我需要让 Excel 显示 0.0000e+00，而不是 0.00e+00
My code is extremely basic:
我的代码非常基本：
Sub ConvertScientific()
Dim org, conv
If IsNumeric(Range("a2").Value) = True Then
org = Range("a2").Value
Else
MsgBox ("This is not a valid number!")
End If
conv = Format(org, "0.0000E+00")
Range("b2").Value = conv
End Sub
Most of that is stuff that doesn't even need touching, but the problem I'm running into is that, even though I've defined the format to be "0.0000e+00", it still ends up resulting in 0.00e+00format.
其中大部分内容甚至不需要触摸，但我遇到的问题是，即使我已将格式定义为"0.0000e+00"，它仍然最终导致0.00e+00格式。
This is extremely essential to my program, mostly because it's meant to run automatically without having a defined spreadsheet where the decimal value is set to 4 on some cells. I know that it is possible to have my desired format, but the only way I've been able to do it is by doing it manually.
这对我的程序非常重要，主要是因为它旨在自动运行而无需定义电子表格，其中某些单元格的十进制值设置为 4。我知道有可能拥有我想要的格式，但我能够做到的唯一方法是手动进行。
Many thanks to anyone with advice.
非常感谢任何有建议的人。
EDIT:
编辑：
I discovered that a possible way of reformatting is using the Range().NumberFormatproperty, but it seems silly to have two lines of code, where one should be sufficient. If anyone could explain why my code isn't working as I understand it should, please feel free to let me know for a 1+ and/or a valid answer.
我发现重新格式化的一种可能方法是使用该Range().NumberFormat属性，但有两行代码似乎很愚蠢，其中一行就足够了。如果有人可以解释为什么我的代码没有按照我的理解工作，请随时让我知道 1+ 和/或有效答案。
回答by Ron Rosenfeld
In order to have Excel DISPLAY it as you describe, before entering conv into B2, you can format it either as scientific with four decimals, or as text; or you can precede conv with a single quote
为了让 Excel 按照您的描述显示它，在将 conv 输入 B2 之前，您可以将其格式化为带有四位小数的科学格式或文本格式；或者你可以在 conv 前面加上一个单引号
Option 1
选项1
With [B2]
.numberformat = "@"
.value = conv
end with
Option 2
选项 2
with [B2]
.NumberFormat = "0.0000E+00"
.Value = conv
end with
Option 3
选项 3
[B2].Value = "'" & conv
EDIT: Excel sees values that are numeric as numbers; it stores them in accord with the IEEE standard that it is using, and displays them pretty much as it sees fit. That is what is happening with your entry. In order to CONTROL how Excel displays a value, you have to either enter the value as text, or format the cell as you desire.
编辑：Excel 将数值视为数字；它按照它所使用的 IEEE 标准存储它们，并按照它认为合适的方式显示它们。这就是您进入时发生的情况。为了控制 Excel 显示值的方式，您必须将值作为文本输入，或者根据需要设置单元格的格式。
