How to get cell value from formula in Excel using VBA?

Question

I have a formula in a range of cells on a worksheet that calculates numerical values. How to get numeric values ​​in VBA from a range passed to a function?

Say the first 10 rows of column A in a sheet contain rand (), and I pass this 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 am calling a function from a cell ...

=X(a1:a10)

How to get the value of a cell, for example. 0.62933645?

Excel 2003, VB6

Answer 1

The following code works for me when working with 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 to what I pass (this is the range of cells with Rand () in them). I'm not sure what else is going on here.

! X, ? , , X ( ) , . :

X = a

Answer 2

, . ( : "for" " " ). = 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

, , , . :

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

, , , .

Answer 3

, .

To speed up macros, pre-formatting is often performed.

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

in this state, you must force the calculation before the value is 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