CODEWITHSUNDEEP
vba
Kris
Kris

Reputation: 35

Non-volatile Evaluate

I have a worksheet containing all the equations. In other sheet I would like to calculate function value for different variables. Let's say in Sheet2 cell A1 I have the formula =125*B1^2+20*B1+222. In Sheet1 cell A10 I have =eval(SUBSTITUTE(FORMULATEXT(Sheet2!A1),"*B1","*"&CELL("address",C10))), so basically I change value of variable with different in cell C10.

My eval function is:

Function Eval(Ref As String)
    Application.Volatile
    Eval = Application.ThisCell.Parent.Evaluate(Ref)
End Function

The problem is that it takes forever to calculate and I would like to achieve same results without Volatile option, but have no idea how.

Upvotes: 1

Views: 166

Answers (1)

Chronocidal
Chronocidal

Reputation: 8081

Instead of creating a custom Eval function, use the secret, built in EVALUATE function left over from Excel 4.0

Create named range, called something like "EvaluatedFormula", and set the "Refers to:" to =EVALUATE(SUBSTITUTE(FORMULATEXT(Sheet2!$A$1),"B1","C" & ROW()))

Then, in cell A10, you just type =EvaluatedFormula, and BOOM! It's done. This can calculate over 9,000 rows in a fraction of a second - much faster than your Eval UDF

Upvotes: 2

Related Questions