How To Deconstruct Formulas Into VBA

[doctorp]

I've seen a few tools around that deconstruct formulas into vba forms text boxes. Then you can evaluate each of the terms in the formula (usually with a hyperlink).

Example is here:
http://www.bestpracticemodelling.com/software/bpmtraverse

Does anyone know how to go about writing such code. I can build the form myself and all the finer points, but how exactly does one separate all the variables from the operators and brackets etc.

Any help would be welcomed.

Thanks:banghead:

[SamT]

Wow! Major project.

First build lists of

Functions and the number of Arguments (Separators) each takes
Containers "[{("
Separators ",:'"!"
Operators "=+-\ /*&^!"
Keywords and constants
Range Conjoiners ": ,"
EtcPractice analyzing formulas, taking very careful notes about what you are doing mentally. Note: This example still needs to break down "IF(LEFT' and "-2" even further For your purposes.

http://www.excelkey.com/forum/download/file.php?id=1723

BTW, the example returns "" if error and "FALSE" if no error.

You will also have to account for undocumented programming tricks like not using both Return Separators in the IFERROR Function. The next to last line in the example, (,"")