CODEWITHSUNDEEP
vbaexceldependencies
nicolas
nicolas

Reputation: 9805

recursive tree evaluation of excel formula

I have a top cell which contains a formula referring to other cells. Those cells contain formulas referring to other cells.

I would like to evaluate the top cell while replacing in the whole evaluation tree every occurrence of a cell, say A4, by another cell, say A5.

I know you can navigate the tree of dependants, but can you evaluate the formula after performing a replacement ?

Upvotes: 2

Views: 1758

Answers (2)

Charles Williams
Charles Williams

Reputation: 23505

Why not just set Calculation to Manual, make the changes to the cells and then call Application.Calculate.
Seems a lot simpler and safer to use Excel's built-in dependency tracking rather than try to re-invent it.

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149287

Yes You can use the Application.Evaluate to evaluate a formula. See this example

Sub Sample()
    Dim frmla As String

    frmla = Range("E1").Formula

    frmla = Replace(frmla, "A2", "A1")

    MsgBox Application.Evaluate(frmla)
End Sub

Upvotes: 0

Related Questions