Run EXCEL formula in VBA function

Question

I have a custom function in VBA which I call from cell and I have a formula like this

"=SUM(SUMIFS(CURR_COUNT;MDL;""MODEL"";CURR_MDLCD;{CODES};RG;""REGIONID""))/SUM(SUMIFS(CURR_COUNT;MDL;""MODEL"";RG;""REGIONID"";CURR_MDLCD;""<>     ""))"

My custom function take some cells values and insert them to formula which is just string. After that I need to calculate formula and return result to cell from which I call the function? Can I calculate this formula in the function and return result to a cell?

Answer 1

Assuming your formula string would work if it was actually put into a cell, then it should work if you use Application.Evaluate - but you don't include the equals sign.

e.g. To calculate

=SUM(A1:B1)

in VBA you would use

Application.Evaluate("SUM(A1:B1)")