如何使用 VBA 从 Excel 中的公式中获取单元格值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/901440/
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
How do I get the cell value from a formula in Excel using VBA?
提问by Simon
I have a formula in a range of cells in a worksheet which evaluate to numerical values. How do I get the numerical values in VBA from a range passed into a function?
我在工作表中的一系列单元格中有一个公式，可以计算出数值。如何从传递给函数的范围中获取 VBA 中的数值？
Let's say the first 10 rows of column A in a worksheet contain rand() and I am passing that as an argument to my function...
假设工作表中 A 列的前 10 行包含 rand() 并且我将其作为参数传递给我的函数...
public Function X(data as Range) as double
for c in data.Cells
c.Value 'This is always Empty
c.Value2 'This is always Empty
c.Formula 'This contains RAND()
next
end Function
I call the function from a cell...
我从一个单元格调用函数...
=X(a1:a10)
How do I get at the cell value, e.g. 0.62933645?
我如何获得单元格值，例如 0.62933645？
Excel 2003, VB6
Excel 2003、VB6
回答by Richard Morgan
The following code works for me when running from VBA (Excel 2003):
从 VBA (Excel 2003) 运行时，以下代码对我有用：
Public Function X(data As Range) As Double
For Each c In data.Cells
a = c.Value 'This works
b = c.Value2 'This works too (same value)
f = c.Formula 'This contains =RAND()
Next
End Function
a and b are the same and equal what I'm passing in (which is a range of cells with Rand() in them). I'm not sure what else is going on here.
a 和 b 是相同的并且等于我传入的（这是一个包含 Rand() 的单元格范围）。我不确定这里还发生了什么。
Aha! You need to set X, no? I'm not sure what exactly you expect this function to do, but you need to set X (the name of the function) to the value you want returned. Add this line:
啊哈！你需要设置X，不是吗？我不确定您到底希望这个函数做什么，但是您需要将 X（函数的名称）设置为您想要返回的值。添加这一行：
X = a
回答by Oorang
I can't replicate a problem using the layout you posted. I noticed a few syntax errors in your posted code (ie: "for" should be "for each"). But when I put =RAND() in A1:A10 and =X(A1:A10) I got a return just fine with this:
我无法使用您发布的布局复制问题。我注意到您发布的代码中有一些语法错误（即：“for”应该是“for each”）。但是当我把 =RAND() 放在 A1:A10 和 =X(A1:A10) 中时，我得到了一个很好的回报：
Public Function X(data As Range) As Double
Dim c As Excel.Range
Dim sum As Double
For Each c In data.Cells
sum = sum + c.Value
Next
X = sum
End Function
However, just to a expand a little more on a few of the other questions you brushed up against. You can evaluate a formula for a result like so:
但是，只是为了进一步扩展您遇到的其他一些问题。您可以为结果评估公式，如下所示：
Public Function X(data As Range) As Double
Dim c As Excel.Range
Dim sum As Double
For Each c In data.Cells
sum = sum + Excel.Evaluate(c.Formula)
Next
X = sum
End Function
But generally speaking you won't want to, as this is basically calculating the same value twice.
但一般来说，您不会想要，因为这基本上是两次计算相同的值。
回答by Frobbit
Make sure you do a calculate before requesting the value.
确保在请求值之前进行计算。
To Speed up macros something like the following is often preformed..
为了加速宏，通常会执行以下操作。
'Set Reasonable default
Application.CutCopyMode = False
Application.ScreenUpdating = False
Application.Interactive = False
Application.Calculation = xlCalculationManual
in this state you must force calculation before the value will be available.
在这种状态下，您必须在值可用之前强制计算。
Public Function X(data As Range) As Double
'You may need the following as well
'Application.Calculate
Dim c As Range
For Each c In data.Cells
c.Calculate
c.Value 'This is now has a value
c.Value2 'This is now has a value
c.Formula 'This contains RAND()
Next
End Function
