为什么在 Excel 2003 的 VBA 中使用 Range.Formula 而不是 Range.Value？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13687800/
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
Why use Range.Formula in VBA for Excel 2003 instead of Range.Value?
提问by Mr_Moneybags
I was wondering why in VBA code for Excel 2003 do we even need to use Range.Formulato writea formula to a cell instead of just using Range.Cell? They both write Strings to the cell that become forumlas, and the formula works (from what I've tested).
我想知道为什么在 Excel 2003 的 VBA 代码中，我们甚至需要使用Range.Formula将公式写入单元格而不是仅使用Range.Cell？他们都将字符串写入成为论坛的单元格，并且公式有效（根据我的测试）。
ActiveCell.Value="=If(True,""yes"",""no"")"
and
和
ActiveCell.Formula="=If(True,""yes"",""no"")"
do the same thing for me (when I select a cell and execute each of the above code segments separately in separate cells). They both show the "yes" value in the cell and the formula is stored when I click to view each cell.
为我做同样的事情（当我选择一个单元格并在单独的单元格中分别执行上述每个代码段时）。它们都在单元格中显示“是”值，并且当我单击以查看每个单元格时会存储公式。
I looked on Microsoft's Dev Center for info:
我查看了 Microsoft 的开发中心以获取信息：
Range.FormulaRange.Formula Property for Excel 2013 (Excel 2003 did not have a page for this Property)
Range.FormulaExcel 2013 的 Range.Formula 属性（Excel 2003 没有此属性的页面）
Range.ValueProperty for Excel 2003 (Expand the "Value property as it applies to the Range object." heading
Range.ValueExcel 2003 的属性（展开“适用于 Range 对象的 Value 属性。”标题
I also googled "Why use Range.Formula instead of Range.Value VBA Excel" and couldn't find anything that related to my question.
我还搜索了“为什么使用 Range.Formula 而不是 Range.Value VBA Excel”并且找不到与我的问题相关的任何内容。
Some peoplesaid, use Range.Value.
有人说，用Range.Value。
Some others say use Range.Formulaon stack overflow (Sorry I lost the reference to the exact question...)
其他人说Range.Formula在堆栈溢出时使用（对不起，我丢失了对确切问题的引用......）
采纳答案by bonCodigo
In terms of values of data:
在数据价值方面：
Imagine you have integers, doubles for certain calculations and if you use .formula you gonna get screwed. Because .FORMULAalways return a String. (Any value set to .formula without is a plain value not a formula) Unless you have exception handling and the extras ready in your code. Whereas .VALUEreturns your data's data type as you expect them to be.
想象一下，你有整数，某些计算的双倍，如果你使用 .formula 你会被搞砸。因为.FORMULA总是返回一个字符串。（任何没有设置为 .formula 的值都是一个普通值而不是一个公式）除非您在代码中准备了异常处理和附加功能。而.VALUE根据您的预期返回数据的数据类型。
In terms of retrieving formula: Value is one way road in case to set formula but not to retrieve.
在检索公式方面：值是一种方法，以防设置公式而不是检索。
So you see these methods are introduced for a reason and help you to work with right properties you require. :-)
因此，您会看到这些方法的引入是有原因的，并帮助您使用所需的正确属性。:-)
回答by blackworx
This doesn't apply so much when writing actual formulas to cells, but I find using .Formulainstead of .Valuewhen filling a range with values from a variant array containing multiple data types (especially non-US dates) gives much more reliable results.
在将实际公式写入单元格时，这并不适用，但我发现使用.Formula而不是使用.Value包含多种数据类型（尤其是非美国日期）的变体数组中的值填充范围会给出更可靠的结果。
I can't remember the exact problem that led me to discover this as a workaround, as it was many moons ago; nor have I tested to find out what the actual differences are. I simply switched once I discovered the benefit and have been doing it this way ever since.
我不记得让我发现它作为解决方法的确切问题，因为它是在很多个月以前；我也没有测试以找出实际差异是什么。一旦我发现了好处，我就换了，从那以后就一直这样做。
At a guess I would say that writing using the .Formulaproperty prevents Excel from attempting to automatically convert number formats for any cells in the target range which do not already have a number format set (and possibly even for those that do).
猜测我会说，使用该.Formula属性进行编写会阻止 Excel 尝试自动转换目标范围中尚未设置数字格式的任何单元格的数字格式（甚至可能为设置了数字格式的单元格）。
