Retrieving formula results

 2009-02-18    Other    0    139

If you need to get the result from a formula in a cell, you would normally do something like this:

dblValue = Range("A1").Value
If you need to calculate parts of a formula or math expression then you can do something like this:
' assume that cell A1 contains this formula: 
' =(A2+A3)/(A4+A5)
strFormula = Range("A1").Formula
varItems = Split(strFormula, "/")
dblValueFirst = Application.Evaluate(varItems(0))
dblValueSecond = Application.Evaluate(varItems(1))
The evaluating expression can contain both cell references, functions and regular math expressions, all of the examples below will return a value:
dblValue = Application.Evaluate("(A4+A5)")
dblValue = Application.Evaluate("=AVERAGE(A1:A9)")
dblValue = Application.Evaluate("AVERAGE(A1:A9)")
dblValue = Application.Evaluate("=2+3*4")
dblValue = Application.Evaluate("2+3*4")