Retrieving formula results
2009-02-18 Other 0 166
If you need to get the result from a formula in a cell, you would normally do something like this:
dblValue = Range("A1").ValueIf 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")