How do I get the cell value from a formula in Excel using VBA?

Question

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

Answer 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