如何使用VBA从Excel中的公式获取单元格值？

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

3 个解决方案

#1

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

#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:

我无法使用您发布的布局复制问题。我注意到你发布的代码中有一些语法错误(即:“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.

但一般来说你不会想要,因为这基本上是计算两次相同的值。

#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

#1