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

Question

我在工作表的一系列单元格中有一个公式，可以计算出数值。如何从传递给函数的范围中获取VBA中的数值？

假设工作表中的第一列的前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

我从单元格中调用该函数...

=X(a1:a10)

我该如何获取单元格的值，例如0.62933645？

Excel 2003，VB6

- Simon

---

我写了一些快速的VBA代码，可以获取单元格Rand()的值，没有问题。也许可以发布更多的代码来查看其他情况？ - Richard Morgan

Richard，没有更多的代码了，就是这样...我想做的就是获取单元格的值，它们都是“空”的。 - Simon

请解释在什么情况下使用该函数。特别是，单元格是否在同一工作表上，并且在使用它时是否有其他活动，例如对话框或某些Excel功能（某些范围方法在某些情况下在Excel中不起作用）。 - GSerg

For循环的开头行不正确 - 应该是：对于每个c在data.Cells中。假设这是一个打字错误，因为程序缺少Each将无法编译。 - barrowc

那不应该是“for each”吗？ - Oorang

Answer 1

以下代码在我从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和b是相同且等于我传入的内容（这是一系列带有Rand()的单元格）。我不确定还发生了什么。

啊哈！你需要设置X，对吧？我不确定你希望这个函数做什么，但你需要将X（函数名称）设置为想要返回的值。添加这行代码：

X = a

- Richard Morgan

---

如果单元格包含以下内容，我可以使其正常工作： 一个值 对一个值的引用 由两个值组成的公式但是，如果直接引用包含rand()函数的单元格，它永远不起作用。 - Simon

也许你可以尝试一下如何调用X函数？我能想到的每种方式都对我有效。 - Richard Morgan

问题中所编写的X函数没有对c.Value、c.Value2或c.Formula进行任何操作。假设我们需要在循环中对c.Value执行一些操作，例如在循环内进行a = a + c.Value，然后如上所述X = a。 - barrowc

Answer 2

我无法使用您发布的布局复制问题。 我注意到您发布的代码中有一些语法错误（例如：“for”应为“for each”）。 但是，当我在A1：A10中放置=RAND()和=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

然而，稍微对你提到的其他问题做一点详细解释。您可以这样计算公式的结果：

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

但一般来说，您不会想这样做，因为这基本上是计算相同的值两次。

- Oorang

---

重新评估公式（尽管在大多数情况下是多余的）对我非常有用。我想缓存另一个工作簿中的值，但它的单元格将返回“空”，因为它们稍后将在Excel中计算。 - aliqandil

Answer 3

在请求值之前，请确保进行计算。

为了加快宏的速度，通常会执行以下操作...

'Set Reasonable default
Application.CutCopyMode = False
Application.ScreenUpdating = False
Application.Interactive = False
Application.Calculation = xlCalculationManual

在这种状态下，您必须强制计算才能获取值。

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

- Frobbit

---

Application.Calculate不是必要的，因为即使计算模式设置为xlManual，Excel也会计算公式的新值。如果每次都必须调用Application.Calculate，那么您就打败了将其设置为手动并加快速度的整个目的，对吧？ - Adarsha

并不是因为你只计算了一个单元格，而不是整个工作表。此外，最大的CPU节省者是关闭屏幕更新。 - Frobbit