Globally modify non-adjacent worksheet formulas matching a pattern

I have a worksheet with 2 named ranges:
MasterFormula
ModifiedFormula


My goal is to input a formula into the MasterFormula cell, enter a formula in the ModifiedFormula cell and then use a macro to modify all cells in the worksheet whose formula matches the pattern. As a simple example, let's say I'd like to add error suppression to a sum formula:
MasterFormula: =SUM(A2:B2)
ModifiedFormula: =IFERROR(SUM(A2:B2),0)

Goal: nest all SUM formulas in the worksheet that match this pattern:
SUM(REF:ADJACENT REF)

Skip:
SUM(#+#,REF)
SUM(REF,REF,#+REF) etc.
AVG(#+#,REF)

Find and replace won't do the trick as it will slavishly replace all references with the identical new references.

At this point, I'm looking for guidance and will build it myself if someone can help me figure out how to parse the various parts of a formula through VB. (ie function start, total number of supplied arguments, recognize which parts are ranges, etc.)

Incidentally, nesting error suppression in a given contiguous range is no big deal - I'd just highlight the whole range, modify the first formula and press control enter, or copy paste. The problem is my spreadsheets are quite complex with many different formulas in any given row, so they're not always contiguous. It would also be nice to be able to modify any part of the formula and propagate that everywhere.

bump... Any takers on this?

It would be a very non-trivial issue to parse a cell formula for the general case.

For your simple example, you could see if the formula starts with =SUM(, and then get the precedents, verify there are only two, and verify that they are adjacent. If so, wrap the formula with your IFERROR function.

That would not nearly be rigorous.

I agree that might not be too bad. Most of the formulas I deal with are more complex, such as:

Master:
=IF(OR($H18="",OFFSET(L$1,$A18-1,0)=""),"",IFERROR(CUBEVALUE($B$1,$B$2,$B$3,OFFSET(L$1,$A18-1,0),$B18,$D18,$E18,$F18,$G18,$H18,$I18,L$4),0))

Modified (remove error suppression):
=IF(OR($H18="",OFFSET(L$1,$A18-1,0)=""),"",CUBEVALUE($B$1,$B$2,$B$3,OFFSET(L$1,$A18-1,0),$B18,$D18,$E18,$F18,$G18,$H18,$I18,L$4))

Modified (remove beginning if):
=IFERROR(CUBEVALUE($B$1,$B$2,$B$3,OFFSET(L$1,$A18-1,0),$B18,$D18,$E18,$F18,$G18,$H18,$I18,L$4),0)

Sorry to have wasted your time by not coming to the crux of the problem.

Any idea how to accomplish this kind of thing?

Yes, but not at all inclined to undertake it. It would be time consuming, and I don't see the value in it.

Thanks for continuing to monitor a slightly older thread... I just need a bit of guidance, and I'll build it. Mostly looking for conceptual understanding. If it were you, would you examine the cell as a string and then parse the bits? Would it be best to use backend Excel calculation engine through the API since it would implicitly recognize formulas? I just need some idea of how to get started with this - I'm a long time Excel user, so if I get a good feel for how to pick this apart, I'm sure I can come up with the rest.

Many of the spreadsheets I work with are a thousand columns by tens of thousands of rows, with many different flavors of formulas scattered throughout. Blocks are not always continuous, and it makes troubleshooting and maintaining them a very time consuming and dangerous affair.

Would it be best to use backend Excel calculation engine through the API since it would implicitly recognize formulas?

I don't know what that means.

I have a minimalist parser to support showing nesting in formulas. It starts by indentifying quoted strings (which are thereafter ignored), and then indentifies functions (any sequence of a word characters followed immediately by an opening paren), then matches parens. It works well, but it's not code I am proud of at all.

The next step would be to separate everything else into tokens, and identify each as either a number, an array constant, a range reference (including implied intersections, the only arguments I can think of that are separated by a space), a named constant, a named range, a named formula, ...

The kind of thing that makes me wish I'd taken a class in symbolic systems.

Thanks for the suggestions. The API refers to the application programming interface and exposes to the user objects, methods etc. that are otherwise unavailable. I'm not very familiar with it. Sounds like this will be quite a project! As for matching parens, my guess is to identify the last occurrence of ( and then work from the inside out. This is gonna be fun .

Tally ho !