問題描述
如何使用 VBA 從 Excel 中的公式中獲取單元格值？ (How do I get the cell value from a formula in Excel using VBA?)
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?
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...
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?
Excel 2003, VB6
‑‑‑‑‑
參考解法
方法 1:
The following code works for me when running from 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.
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 = a
方法 2:
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:
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.
方法 3:
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
(by Simon、Richard Morgan、Oorang、Frobbit)