Recent Addins ADO Application Arrays Charts Commandbars Count Dates Dialogs Events File access Files & Folders Functions Games Help Import & Export Lookup Off topic Other Outlook PivotTables Power Query Printing Problem solving Read me Searching Sorting Sound Strings Sum Time VBA programming VBE Workbooks Worksheets

Retrieving formula results

 2009-02-18    Other    0    204

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")