How do I get the cell value from a formula in Excel using VBA?
- by Simon
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
