Hi,

I am looking to code a macro to seperate a formula by operands and evaluate each portion individually. I am familar with programming but not with VBA (I have a good idea of the algorithm but not enough knowledge of VBA syntax to code). I'm hoping someone here can help

Basically what I want to do is to split a formula using the operand as delimiter and evaluating and resubstituting the results back to the original cell.

Example if a formula in C1 contained

=VLOOKUP(A1, RangeA, 2, FALSE) * VLOOKUP(B1, RangeB, 2, FALSE)

lets say the first portion of this VLOOKUP (on range A) returns the number 2 and the second portion returns the number 3. I was hoping a macro could turn this cell into

= 2 * 3

Obviously an escape character (') will be required so 2 * 3 is not evaluated to 6.

The macro would have to work with all sorts of formulas with or without brackets.

The alogrithm I had in mind is

LOOP worksheet for formula cells

in each formula cell cast the content of the formula as a string and loop characters until a operand (+, -, *, /) is reached (keeping track of the number of brackets).

If the number of "(" is equal to the number of ")" then the formula can be evaluated in a different cell and the result substituted back to the original formula.

If the number of "(" is not equal to the number of ")" remove the outer ")" until they are equal.

Repeat until the end of the formula is reached

Then add (') to the start of the string to prevent excel from performing a calculation.


I know this is a big ask but does anyone have any ideas on whether this is achievable and how I should code this?

Thanks in advanced