0

I am trying to make a UDF that does integration on function specified in some cell.

for that i have made this beautiful function that almost works:

Function EvaluateFormula(ByVal Variable As Double, ByVal FormulaCell As Range) As Double
    Dim Formula As String
    Dim result As Double
    
    ' Get the formula from the cell
    Formula = UCase(FormulaCell.Formula)
    Formula = Replace(Formula, "VARIABLEX", Variable)
    
    ' Evaluate the modified formula and return the result
    result = Application.Evaluate(Formula)

    EvaluateFormula = result
End Function

Function works fine with all kinds of UDF that i can pass thru the formulacell but if i try to pass function where Evaluateformula() is used or any another function that result contains Application.Evaluate it fails to get result? i guess its impossible to run application.evaluate within application evaluate? What could I do?

Rihards Roziņš
  • 1
  • 1
  • It works for me for a simple number formula in cell. Can you give specific formula or function you used which triggers the error? – Dhay Mar 17 '23 at 09:58
  • If formula in the cell A3 is "=EVALUATEFORMULA(A1;A2)+1" a1 is equal to 1 and a2 is "=variablex+1" ... A3 will calculate perfectly but now if i try to do the same and make formula in A4 cell "=EVALUATEFORMULA(A1;A3+1" ... and in A3 cell i change A1 to variablex it doesnt work.. i can stack any another UDFs as long as it doesn't have Application.Evaluate() i think somehow only one Application.Evaluate() can be active at any given time or something like that.. – Rihards Roziņš Mar 18 '23 at 11:31

0 Answers0